Please Help! Excel Power Query Query data directly to txt file?

[Maggie Barr]

Greetings, and thank you in advance if you can assist.
I am running MS Office Pro 2019, so Access and Excel Power Query 2019 on a PC.
I have a series of databases in both Access and Excel. I tend to do some preliminary data handling and modifications in Access and take the output from those into Excel Power Query for final data work and manipulations. I resorted to this method as there was no way to do it all in one program or the other because of the file sizes and complexity of manipulations. I have also had to split my Access databases over time as the data sizes grow.

I recently was unable to do a simple union query in Access and it is telling me that either the database size is too big (actually only 444 KB) or there is not enough memory (16 GB). Well, I trimmed the database to just the two files I am trying to union. One is 3,504,398 records and the other 1,717. Doesn't seem that big, as they are linked tables only. No matter what, I couldn't get the UNION to work.

I opened a workbook in Excel, loaded the data via the txt files to the data model (connections only), did the union there fine, but I cannot figure out how to export that query as a txt file, which is what I need to bring the data into my next database. I cannot load it to a table because the record number is too high. I have looked online for how to do this, but I am not using Power BI, but Excel with Power Query. Can anyone please tell me there is a way to simply export data from a query to a txt file, and how to do it.
Much appreciation, as this is stumping me and holding me up big time.
Best Wishes,
Maggie Barr

 

[alansidman]

Load your query to the Data Model. Now execute Control-A, Control-C. Save as .csv

 

[Maggie Barr]

Load your query to the Data Model. Now execute Control-A, Control-C. Save as .csv

Thank you for reaching out to help, I do appreciate it.
I already had it loaded to the data model as I always do that as part of the the load to process. However, I opened the data model to the sheet for the query I want exported, hit control A (select all), then control C (for copy), but upon the file save as csv, it just created a csv workbook with nothing there to import into Access. Any thoughts on what I am doing wrong. I just need the output of the single query in the workbook saves as a txt file. Sorry I am not catching on to what you described. I am trying it again, but it doesn't appear to be working as described.
Thank you again for helping!
Sincerely,
Maggie Barr

 

[alansidman]

When I tested this procedure, I then opened the .csv file with Notepad and it was a text file, comma delimited. I did not attempt to import the file into Access. I will try that now and report my results.

 

[Maggie Barr]

When I tested this procedure, I then opened the .csv file with Notepad and it was a text file, comma delimited. I did not attempt to import the file into Access. I will try that now and report my results.

How I did this was to open the data model, make sure I was on the right sheet for the query, cntrl A, cntrl C, then on the file tab click save as and save as csv to the folder I need. Did you do this from a query or a table in the workbook?

 

[alansidman]

I just tried it and it imports the .csv file without issue. When I was in Access, I indicated that it should import from a text file and it seemed to load without issue to a table.

 

[alansidman]

I loaded the query directly to the data model. Not from a table.

 

[Maggie Barr]

I just tried it and it imports the .csv file without issue. When I was in Access, I indicated that it should import from a text file and it seemed to load without issue to a table.

I'll keep trying. It seemed to save, but then it opened with nothing. I had a glitch trying to close the excel workbook I was working with after the save, so maybe it didn't save. I can't open it with notepad to check because the file is too large.
I tried it over again, and my workbook is frozen now....just not sure what is happening. The data, me, or the computer...
I appreciate you trying to help though, and I will post back if I get it working.
Best wishes,
Maggie

 

[Maggie Barr]

I loaded the query directly to the data model. Not from a table.

My data was loaded to the data model directly from a txt file, and only linked. Still not sure what is going on. It is a shame that they don't have a right click export option on the query like they do in Access. Trying to stay within data limits of various programs, doing some things in one program because it is "Better" and other things in other programs. Having to split databases because they have a 2 gigabyte limit when it is the desktop version vs online....all so frustrating. My excel is still showing (Not responding after the most recent attempt to save. I haven't had time to try to learn Power BI, or the R program, or SQL....and the list goes on.
Thanks gain, and I'll keep you posted.

 

[Maggie Barr]

Well, I never got the export of the query to work, but it turned out that in Access, when you do a UNION query, it checks for duplicates, so with the record number, the memory on the computer was taxed, but if you do a UNION ALL, it bypasses that and ran well. I know I don't have any duplicates between the two files, so this was safe for me to perform.

Thanks for trying to help, and if I have time, I will definitely try to figure out how to get a query to export as a txt file, as Power Query is a great thing to have to work with large files, but being able to export the output of a query directly as a txt file is crucial for multiple reasons and purposes.
Best Wishes,
Maggie