Hello everyone, this excel or spreadsheets merger are somehow become trending on my blog. Question keep coming event hough most of the questions are answered already on comments. I even wrote another post regarding the question about copying worksheets to worksheet(s) by customizing the vba macros scripts. Those comments, email or questions makes me think that vba macros is not an easy work out to everyone.
So, in a hope to help you work out with more easy spreadsheets merging solution, today i’d like to share another tool that i’ve created recently thanks to Lazarus, FreePascal and FPSpreadsheet component. This tool named Oa spreadsheets merger which will be completely different from the old excel merger tool which require MS Excel 2007 in order to do the merging process. Oa spreadsheets merger will only run on command line (DOS) and doesn’t require MS Excel to do the merging job.
Oa Spreadsheets Merger (OSM) on Youtube
OSM is a Windows command line tool to merge or combine list of spreadsheets files into one or more spreadsheets files depend on number of combined rows. Depend on options selected, OSM can also merge worksheets or selected worksheets into worksheets or single worksheets. If maximum combined rows reached (depend on spreadsheets format), OSM will split combined spreadsheets into several files or several worksheets (single sheets mode only).
If you have all MS Excel, open spreadsheets or csv files gathered within a folder than this option will be the most simple way to merge. Simply put a directory path after -d option and then set an output file. Please note that output extention will determine the merged spreadsheets format automatically.
oamerger -d D:\PATH\TO\EXCEL_DIRECTORY\ D:\PATH\TO\MERGED.xls
As default, OSM will merge all worksheets to related worksheets number and name on the output spreadsheet file. If combined rows exceeded maximum number of rows, it will automatically split the output into several files until the merge process done.
Maximum number of rows for xlsx (Ms Excel 2007 and later) and open spreadsheets documents (ods) is 1.048.576 rows, for xls (MS Excel 97-2003) is 65.536 rows. There are actually no limit of rows for csv, but for the sake of RAM, i’ve set limit for csv equal to xls.
OSM can also merge from a list of spreadsheets files. The list can have files from different locations and can be also modified (add or delete) manually using text editor or using OSM command. List file can be any text files which contains file path, column start, column end, row start, row end, rows count and extentions of spreadsheets file.
To add files or folder into list, use -a or –append-file or -i or –append-dir options. To remove files or dir use -r or –remove-file or -x or –remove-dir options. Type oamerger -h for more list of options.
oamerger --append-file=D:\XLSX\NXLS\DB_2.xlsx -l D:\PATH\TO\mergelist.txt oamerger -i D:\XLSX\DIR -l D:\PATH\TO\mergelist.txt oamerger -r D:\XLSX\NXLS\DB_2.xlsx -l D:\PATH\TO\mergelist.txt oamerger -x D:\XLSX\DIR -l D:\PATH\TO\mergelist.txt
To start merge simply put list file as the source of spreadhseets files.
oamerger -l D:\PATH\TO\mergelist.txt D:\PATH\TO\output.xls
Please note that all files within list and folder must have the same number of worksheets and name. Otherwise it will return error. By default the same result as merge directory mode will apply to list file mode, where worksheets will merged into worksheets related to source worksheets number or names. OSM also will split output file into several files if maximum rows reached.
OSM can take advanced to worksheets copy with -s or –sheets-copy options. You can combine all worksheets into single worksheet, or combine only spesific selected worksheets. There are 4 different modes in this option:
1. Copy all worksheets and output file will have the same amount of worksheets (default). No need -s option default sheets copy mode.
2. Copy all to single worksheet (single sheet mode)
oamerger --list=D:\PATH\TO\mergelist.txt -s [all:single] D:\PATH\TO\output.xls
3. Copy selected worksheets into single worksheet (single sheet mode)
oamerger -l D:\PATH\TO\mergelist.txt --sheets-copy=[1,2,5:single] D:\PATH\TO\output.xls
1,2,5 = worksheets position number regarding names.
4. Copy only selected worksheets, output file will have the same names and ammount of worksheets
oamerger -l D:\PATH\TO\mergelist.txt -s [1,2,5] D:\PATH\TO\output.xls
On single sheet mode, you can set OSM to put exceeded rows into new worksheets instead of new files with -n options. But please be cautions because it will use a huge amount of RAM during process.
If you want to exclude the first row from merged files, because it contain column header, you can set OSM to start copying from row 2 to each files within list or directory. To do this set option -w or –row-start=2 or higher.
oamerger -w 2 -d D:\PATH\TO\XLS_FOLDER D:\PATH\TO\output.xls
Important, if you want to use custom row and column with spreadsheets list, use this option during list creation. Its mean for every folders or files you add to the list, it has to contain this option, except if you want the first file to contain first row.
oamerger --append-file=D:\PATH\TO\first-spreadsheets.xls --list=D:\PATH\TO\spreadsheets.lst oamerger -w 2 --append-die=D:\PATH\TO\XLS_FOLDER --list=D:\PATH\TO\spreadsheets.lst oamerger -w 2 -a D:\PATH\TO\another-spreadsheets.xls -l D:\PATH\TO\spreadsheets.lst oamerger --list=D:\PATH\TO\spreadsheets.lst D:\PATH\TO\output.xls
Another custom column and row options:
-c set column start (column represented by alphabetic character, for example Column B)
-e set the end of column
-t set the end of row
Unfortunately, at current version, OSM can only use copy value to merge spreadsheets documents. Which mean, formats settings (such as border, alignment, etc) and formulas will not be copied into the merged file, the output file will only contain merged values from source documents.
Select download from 3 cloud storage link bellow.
OSM doesn’t require install, simply unzip oamerger.zip into a folder and open command window into folder by SHIFT+RIGHT CLICK oamerger folder, and select Open Command Window Here. A command window will appear, you can then type oamerger -h or –help to show help or option list.
Although OSM is not a great tool and tend to to run slower, but it work as expected, and more important thing, it bring support to ODS and CSV format thanks to FPSpreadsheet component. Have any question? please shoot a comment or contact us through contact us form on About Us page.