I have not yet found the exact answer to my specific problem, so let's give it a shot :
The Layout :
1 SharePoint Farm on which a report was run, returning 6 Excel Files with each containing 250 Worksheets with exactly 4001 rows in it (last worksheet in File 6 has a few less ... )
Every single worksheet has the very same and exact layout :
Row 1 : header (A-N) row 2 - 4001 : Data (A-N , 4k rows)
The issue I'm having is that I need to evaluate the Data in those 6 Files for a report , my thinking was to have the data imported into a mysql DB and then a simple website with a few queries to be set up to filter for a few specific rows (failure / sucess ) and then pull the resulting item / document / site collection etc.
The Problem however is that due to the sheer number of worksheets I do not really want to go ahead and C&P every single one , that would consume way too much time. The other issue is, that Excel / OO only saves CSV per active worksheet , again , no joy.
The Report itself can be obtained as a single txt (csv) file as well, delimited by semicolon but without text qualifiers.
As the report contains a list of all items, documents and object within the Farm , this is pretty much useless as well, as a rough count has revealed a good 900k items have somewhere a ; in the name , which without a text qualifier makes it impossible to import into a database.
So, the Q I'm really after :
(either Excel or openoffice) :
Would somebody be able to provide me with a function that copies the data from all worksheets into a "consolidated" one until the limit of 65,536 rows has been reached and then a new consolidated worksheet is being started.
As Excel / Openoffice allows to save the data with text qualifiers, that would make my life a lot simpler. I would end up importing into mysql only a few dozen files instead of thousands.
So, any ideas on how to attack this issue ? Perhaps MS Access, but havent found a way yet to import all worksheets into Access , which brings me back to an insane copy and paste day.
Of course, if any other suggestions come to mind, please share them.