Export data from excel Data model into text file

[earthworm]

I have a huge huge data set . One excel workbook with multiple sheets. Each sheet containing data more then 900k rows. I have appened them into single sheet through power query into data model. Now the problem is I cannot export data back into excel due to row limits as data length is now 5 million +
I want to export this data from data model into text file . Any possibility. I tried to search the internet and there is no solution.

 

[earthworm]

I am trying to import the data in access in last stage for data mining purpose.

There are special characters present in data . This can be in any cell.since data is huge it's diffcult to rectify them hence I need the data to remain intact in original after export. So that in future if I need to open the data in access or power bi I can easily perform analysis upon importing it from txt /.csv file.

Please help in resolving this exporting issue we will then deal with access on last part. Currently I am also unable to open the file in notepad. But able to import it again in power query in excel

 

[Alex Blakenburg]

Apart from consolidating the multiple sheets and putting it in the data model what is your initial power query code doing ?
If it is not doing anything too fancy it might be easier to load the initial data into MS Access instead. You access the data using Power Query from Access should you need to.

 

[earthworm]

Apart from consolidating the multiple sheets and putting it in the data model what is your initial power query code doing ?
If it is not doing anything too fancy it might be easier to load the initial data into MS Access instead. You access the data using Power Query from Access should you need to.

I think there is no option in access to ppend data in access from excel from multiple sheet.

 

[Alex Blakenburg]

There is not a specific option but you can append your import to an existing table and you can use VBA to automate that.
It would be preferably to look at the full workflow though. How are you getting the data into the sheets in the first place ?
If you are receiving multiple text file that you are importing into excel then you can import all the files from a folder into Access with a little VBA.
We do have to keep in mind that Access has a 2GB file size limit but I would have thought that $ 5m would not blow that unless your have an exceptionally large number of columns.