Export mulitple worksheets to single large csv file

[Phooey]

I've been amending an Excel spreadsheet used for data conversion to be used in Stress Testing. Not my choice, I will admit, but the solution I've inherited.

As a last step, Excel opens up all csv files found within a directory and copies the contents to another sheet, appending to the bottom of the sheet. The script then randomises the data held in the sheet before saving as another csv file. My problem is that so far, I've managed to get this to work with a few csv files, but eventually I will need to use 2000+ csv files which will contain 250,000+ lines of data.

I so far have three proposed solutions:
a) count if the number of lines will exceed 65536 and create a new files if they do
b) use the vba FileWriter to write out the files
c) integrate Access in some way.

The problem with the solutions above is that I then need to randomise the data within the final csv file.

Any help is appreciated on the pros/cons of the above solutions and the randomisation of the final data in the csv file

Many thanks



Phooey

 

[Zathras]

Where are all of this data going to end up? Not much can be done with a 250,000 line csv file except to import it into some application or send it somewhere else. You idea to use Access is probably the way to go. (Assuming you don't have a "grown up" database to use like Oracle or SQL Server.)

As for randomization, it should be easy enough to use the RAND function and insert an extra column in your data before outputting to the csv file(s).

 

[Phooey]

The data will end up in a large csv file for use by LoadRunner.

I'm currently using the RAND function to randomise the data, but will not be able to do this when the number of input csv files grow beyond the scope of Excel.

Looks like Access may be the way to go.

Thanks

Phooey

 

[Zathras]

but will not be able to do this when the number of input csv files grow beyond the scope of Excel.

If you generate random numbers between 1 and 1,000,000 you shouldn't have too many collisions, and even when you do, they are of no consequence. So what if two (or more) records have the same random number? They are still randomly placed in the output file when compared with the 240,000 other records in the file.

 

[Phooey]

Thanks for the speedy replies...

I've managed to import the data into Access and randomise the data there using a query based on Rnd(), which gives the same results, just on a larger scale (i.e. more records).

Thanks very much for your help

Phooey