I wrote a macro that imports a whole bunch of csv files based on a list. The macro then cuts down the imported files that have thousands of rows to about 60 rows based on certain criteria.
Recently, the csv files I need to import have over 1MM rows. Excel can only load 1,048,576 rows of data.
I'm seeing that it's possible to open csv files in Excel Power Query, which has can hold a few million rows.
Anyone know how to load csv files into Power Query based on a list? How to automate this via vba so I don't have to manually point to the list? And how to then run my remaining code to thin out the data on Power Query?
Any help on this would be appreciated.
You can't run VBA macros in Power Query but you should be able filter the CSV in PQ based on whatever criteria you have.
Theoretically, in Excel if you would do a vlookup on each row to see if it's in the list, and then remove anything that's not found, would that satisfy your requirement? if yes then it can be done in PQ with a merge and then filter the blanks (or do an inner join but that's much slower) .
Regarding the multiple files - you can create a function to clean up the data and then loop thru all the files and have the function run on each file, but there's no UI for that, and it's somewhat advanced.
This should help get you started with looping.
Also you can point PQ to a folder with all the files then when you have new files you can just replace the files in the folder.
Only caveat is that as far as I know PQ can't write to other excel files (In other words PQ won't manipulate the outside source file, rather it imports the data, then you can transform the data as needed, and then spit it out into the current excel file). You might be able to output them in separate tabs and then you can use VBA to separate if needed.
Let me know if I can be of further help.