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.

| |

Ron de Bruin Excel Automation has many pages with useful free Excel VBA Tips and Excel Add-ins.

I think RDBMerge, Excel Merge Add-in for Excel for Windows may be just what you need.

RDBMerge is a user friendly way to Merge Data from Multiple Excel Workbooks, csv and xml files into a Summary Workbook.

Install the RDBMerge utility

1) Download the correct version and extract it to a local directory.

2) Copy RDBMerge.xla(m) to a unprotected directory on your system. Tip: Use one folder for all your add-ins (easy to backup your add-ins this way)

3) Start Excel and open a workbook.

Excel 97-2003

Click Tools, click Add-Ins, use "Browse" to go to the add-in and then click OK. Verify RDBMerge is checked in the add-in list and then click OK.

Excel 2007-2013

2007: Click the Microsoft Office Button, click Excel Options, click the Add-Ins tab.

2010-2013: Click on File, click on Options, click the Add-ins tab. In the Manage drop-down, choose Excel Add-ins, and click Go. Use “Browse” to go to the add-in and then click on OK. Verify RDBMerge is checked in the add-in list and then click OK.

enter image description here

There are detailed step-by-step instructions and download links in the rest of the article.

You can also search his site for "merge spreadsheets" for other possiblities.

| |
  • the addin is asking for a password – wizlog Jul 12 '16 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.