Command line spreadsheets merger – xlsx, xls, ods and csv

Last updated on March 12, 2015 @ 2 Comments

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

About Oa Spreadsheets Merger (OSM)

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).

Oa Spreadsheets Merger Command Options
There are 2 ways to merge spreadsheets with OSM that is directly merge a directory contains spreadsheets files or merge from a list of spreadsheet files.

Merging a folder or directory

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.

Merging from list of spreadsheets files

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.

OSM Spreadsheets List 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.

Worksheets Copy

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.

Modify columns and rows range

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

oamerger merging test

Copy Value

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.

TIPS

  • For faster result, use xls as output spreadsheets format (Excel 97-2003). If you want xlsx result, you can convert later with MS Excel. It doesn’t mean you can’t use xlsx as output format, it only take longer time than xls even only for 30.000 rows.
  • On single sheet mode, use -n option only if you have plenty of RAM. Depend on columns count, a single worksheets can take up to 300mb of RAM or even more.
  • Do not attempt to use -s or –sheets-copy to merge csv, since csv doesn’t have worksheets.
  • Rows start at “1” and columns start at “A”. Xlsx max rows = 1.048.576 and column = “XFD”. Ods max rows = 1.048.576 and column =”AMJ”. Xls and csv max rows = 65.536 and column = “IV”.
  • You can edit list file with notepad or any text editor, and edit it manually.
  • OSM automatically overwrite output file if exists, but make sure that the file not in use (opened), otherwise it will fail to rewrite.
  • Use -v option to see merge process, but verbose will slightly reduce performance.

Download

Select download from 3 cloud storage link bellow.

Installation

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.
Open Command Window Here
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.

M Riza

Article by M Riza

Is a blogger since 2007, founder and editor of Oa Ultimate. Working as a network administrator, computer technician, database and web maintainer | Twitter | G+

2 Comments → “Command line spreadsheets merger – xlsx, xls, ods and csv”

  1. Dan

    I like the work, any chance you’re planning to open source this say on github… I’d like to see if I can improve upon this with a gui… also thinking about porting to linux as well. Please get in touch if this is of interest. Thank you.

    • M Riza

      Hi, i was too shy to show off my messy codes to everyone. But you know, it only consists of few procedures and functions written with lazarus freepascal. I believe you can write a better one, github users usually produce great programs 😀 .
      Actually i do have a linux version, but it was too bugy to be shared to everyone. On my centos 7 machine, It used more than 95% of CPU during merge eventhough it run faster than windows version.


Leave a Reply

*

Loading Google+ Comments ...
Loading Facebook Comments ...