Export Sheets to Text Delimited CSV file

Hello,

I have a macro that combines all worksheets to a single sheet and then exports the data as a text delimited (e.g. "Text","Text2","Text3") format. I am now to a point to where the amount of data I have to merge exceeds the max number of rows that Excel allows (1,048,576). I am trying to find how I can take each sheet and export directly into a single text delimited CSV file without doing the merging in excel. From what I've read, CSV files can handle more than the 1 million rows. Each sheet has a header, so I would only need the header copied into the CSV file one time and all other sheets just row 2 down (Columns A, B, C, D, E).

Anyone know of a way to do this without merging sheets in Excel before exporting?

Thanks for any help given.

Is that mean that you would like quotation marks with every sample of data, or only with text ?
And numbers without quotation marks ?

Hi Porucha,

Yes, quotations marks for every sample of data. So basically, in the export, the data would look something like:

"FeeSchedule","Code","Grouper","AllowedAmount","LocationCode"
"TEST1","80046","","15.53",""

One thing to add, there are other sheets within that should not be exported to the file (e.g. SETUP, FSNames), so only sheets not equal to sheets specified. Tried to attached a sample workbook, but for some reason it is not giving me the option.

Originally Posted by matrix2280

... Each sheet has a header, so I would only need the header copied into the CSV file one time and all other sheets just row 2 down (Columns A, B, C, D, E) ...

Are the headers of all sheets the same ?
It is more convenient to take data from a given sheet in its entirety, simpler code.

Yes the headers would be as follows for those being exported


"FeeSchedule","Code","Grouper","AllowedAmount","LocationCode”

maybe i understood you correctly, check:

Please Login or Register  to view this content.

between the tables with data and other entries in the sheet, a gap of one row and one column must be kept
exclusions for worksheets: "FSNames" and "SETUP"

WOW! That is exactly what I needed and worked perfectly (haven't tested it on the large data set yet, but should work for what I am doing).

Thank you so much porucha - your help is greatly appreciated.

Porucha,

The Macro works awesome, however, not sure if there is anything that could speed it up (if possible)?

Once I imported all of the data to give it a full test run, I have a total of 113 sheets to be exported, and each has around 15,000 rows of data (not exact number, but approximately based on the current file I am working with - could be more/less). I ended up stopping the macro after about an hour of running which left about 59 sheets remaining to export to the file which was now at 30MB.

If not really a way to speed it up, I'll just add in a form to have the user wait until finished.

Thanks for any additional help

Have you tried Power query? It can handle over millions of rows as it is saved on memory.

Originally Posted by matrix2280

... is anything that could speed it up (if possible)? ...

You can try to replace the code snippet (with 'For ... Next' loops) with this one:

Please Login or Register  to view this content.

However, I don't think it would help a lot. The code is so simple that there is not much to change (simply, a lot of data to be processed).
You could also check, what unnecessary services occupy the memory of a given computer and turn them off or check the idea of AB33 as above.

Porucha,

Thanks. Yeah it is a lot of data that I am working with so I wasn't sure.