Can anyone out there help me figure out how to best merge 1500 matching spreadsheets for analysis? They have the same columns, in the same folder. I have access to Access, Excel, SharePoint, Word, Informatica, Eclipse. Any other recommendations have to be free (gratis), or have a free trial that does the merging.

The purpose of this is for me to clean the data and verify quality before importing into a CRM. Which software could do that?

  • which OS? Free, opensource, or paid? How do you want to analyse the data? – ᔕᖺᘎᕊ Nov 17 '14 at 21:21
  • Windows 8 or Mavericks. Down with cloud too. Free, or trail, 1x use ok with add-ons – MeighanRocksSF Nov 17 '14 at 22:12
  • I don't know either, not even VB, – MeighanRocksSF Nov 17 '14 at 22:39
  • excel max specs - are you meaning you have 1500 workbooks that you want to merge into one workbook, each being a worksheet? Or do you need to perform lookups or functions on each one relative to the others? – Raystafarian Nov 20 '14 at 17:24

Hi my answer is not provided based on personal experience but I hope this tool will help you Bulk File Merger,This Program allows the following features.

  • It allows to Merge several file types (xls/xlsx),(doc/docx), csv, and text (txt) files.
  • No technical/macro programming or formulas to mess with With a few clicks, the program will automatically merge your Excel files line by line, page by page.

Important fact mentioned in the website found in the FAQ:

Is there a maximum file size or max row count when merging Excel files?

If you are merging .xls files, the maximum row count allowed per file is 65,536. If your merged file needs to exceed that, you will need to use the .xlsx file format, where the maximum row count can be close to 1 million. Bulk File Merger does not restrict file sizes.

|improve this answer|||||

So I ended up trying the Excel PowerQuery add in and that was perfect. It allowed me to query 1500 xls sheets in a standard folder either in the Cloud or my desk, then remove all incomplete rows, update records with additional mappings from 1 sheet, 1 time, and then load to CRM or convert to a csv. I was given an added Bonus of being able to compare to a table of existing data to remove duplicates also. Price: free Mac win and cloud

|improve this answer|||||

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.