Compare/show difference between multiple Excel/CSV files/workbooks/sheets

qubodup 2010-01-15

I have screwed up important multi-sheet Excel workbooks and need to compare revisions of them. How do I do it?

  1. I export the sheets
  2. I install the command-line tool csvdiff
  3. I install Open Command Prompt Shell Extension, so I can open a CLI/Command line interface/shell in a folder by right-clicking in it and selecting "Open Command Prompt"
  4. I start typing a lot:

>"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "OldFile.csv" -a "NewFile.csv"

I write following .bat (Batch) file. Simply a "compare.txt" file with the ending changed to ".bat" ("compare.bat"). I put it in the folder with all the CSV files. In my case, I have ten revisions and I want to see the difference between two successive/sequenced revisions/versions of each sheet/file.

"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (345).xlsSheet1.csv" -a "File (346).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (346).xlsSheet1.csv" -a "File (347).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (347).xlsSheet1.csv" -a "File (348).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (348).xlsSheet1.csv" -a "File (349).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (349).xlsSheet1.csv" -a "File (350).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (350).xlsSheet1.csv" -a "File (351).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (351).xlsSheet1.csv" -a "File (352).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (352).xlsSheet1.csv" -a "File (353).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (353).xlsSheet1.csv" -a "File (354).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (354).xlsSheet1.csv" -a "File (355).xlsSheet1.csv"

I know, I know. Anyways, I use my text editor (I use notepad++) to replace "Sheet1" with "Sheet2" etc. (or whatever the sheet names are) since my workbooks are multi-sheet ones.

The output might be overwhelming, so you should increase the CLI buffer size. Click on the CLI window's top left icon, select "Properties" and increase the Screen Buffer Size Height.


I wish I knew how to effectively use the window command prompt/write batch files. Please comment if you know how to enhance the code. Perhaps by looping through all files that only differ in a number but are equal besides that and compare each increment?

No comments:

Post a Comment