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

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
184
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,159
Office Version
  1. 365
Platform
  1. Windows
Load your query to the Data Model. Now execute Control-A, Control-C. Save as .csv
 

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
184
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,159
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 28, 2014
Messages
184

ADVERTISEMENT

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

Well-known Member
Joined
Feb 26, 2007
Messages
6,159
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,159
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
184
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

Board Regular
Joined
Jan 28, 2014
Messages
184
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

Board Regular
Joined
Jan 28, 2014
Messages
184
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,597
Messages
5,625,731
Members
416,130
Latest member
galgozzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top