VBA and Power Query

Hello Brawnystaff,

Do you want to create a workbook for each state /territory listed in column "AF" ?

Hello Brawnystaff,

I have been doing a lot of research on this problem. While the download for Power Query is free, my system does not meet the requirements needed. The main point of my research has been on handling such a large file size with 5 million rows. As you pointed out, this won't fit in a standard size workbook.

If this file were in database format before being converted into a CSV then using Power Query would be the best option. However, if the original file is a text file and you are using Power Query to export it as a CSV to Excel then converting the text file may not even be necessary. Can you post a sample of the original file?

Hello Brawnystaff,

I am not sure how to handle this file. When decompressed, the file is 5.11 GB. This file is larger than all the space needed for Windows and Office on my computer. There is no Windows editor I know of that can open a file of this size. Is there no way to download the file in smaller chunks from the site?

Hello Brawnystaff,

Can you tell me how Power Query manages to recognize this as an Excel file when it clearly exceeds the Excel limits? This just doesn't make sense.

Hello Brawnystaff,

I just tried out an idea. Actually it was my last resort. I successfully opened the file I saved to my hard drive after it was unzipped by using a Stream Object.

Now I can import each line at a time into a worksheet until it is full and then start filling another worksheet. The header row can be included on each successive worksheet and each sheet can then be filtered.

I think this will work.

Hello Brawnystaff,

I just finished running a test macro to read each line in the file to check if it would error out. It successfully read 4,551,495 lines without an error. Now I can start on the code to copy the lines on the worksheets, filter them and copy the states into their workbooks.

Hello Brawnystaff,

I just finished running a test macro to read each line in the file to check if it would error out. It successfully read 4,551,495 lines without an error. Now I can start on the code to copy the lines on the worksheets, filter them and copy the states into their workbooks.

Hello Brawnystaff,

Do you need the data converted from text to the type for the column it will be in? For example, a date, or currency amount.

Hello Brawnystaff,

I have run into a problem with Excel. Despite the huge number of rows and columns on a worksheet, Excel is very memory limited. I can download 23 lines of data before I get "Excel is out of resources".

This problem is independent of both my physical RAM (8GB) and hard disk (1TB) space. 23 rows at 330 columns is a pathetically small amount of worksheet space. I spent most of the day researching this problem and have not found any articles so far that explain this problem. All the solutions point to problems with excessive formatting, heavy formula use, Charts, etc. None of this applies in this situation.

To say this frustrating is an understatement. Still hoping there may be a solution. Some articles point to 32 bit Excel having a limit of 2GB of resource memory and that switching to the 64 bit version overcomes this limitation. However, I don't think this a very practical solution for the end user.