How do I create a readable diff of two spreadsheets using git diff

Question

We have a lot of spreadsheets  xls  in our source code repository   These are usually edited with gnumeric or openoffice org  and are mostly used to populate databases for unit testing with dbUnit   There are no easy ways of doing diffs on xls files that I know of  and this makes merging extremely tedious and error prone   I ve tried to converting the spreadsheets to xml and doing a regular diff  but it really feels like it should be a last resort     I d like to perform the diffing  and merging  with git as I do with text files  How would I do this  e g  when issuing git diff

User · Answer

Diff Doc may be what you re looking for         Compare documents of MS Word  DOC  DOCX etc   Excel  PDF  Rich Text  RTF   Text  HTML  XML  PowerPoint  or Wordperfect and retain formatting   Choose any portion of any document  file  and compare it against any portion of the same or different document  file

User · Answer

I know several responses have suggested exporting the file to csv or some other text format  and then comparing them  I haven t seen it mentioned specifically  but Beyond Compare 3 has a number of additional file formats that it supports  See Additional File Formats  Using one of the Microsoft Excel File Formats you can easily compare two Excel files without going through the export to another format option

User · Answer

Do you use TortoiseSVN for doing your commits and updates in subversion  It has a diff tool  however comparing Excel files is still not really user friendly  In my environment  Win XP  Office 2007   it opens up two excel files for side by side comparison   Right click document   Tortoise SVN   Show Log   select revision   right click for  Compare with working copy

User · Answer

I got the problem like you so I decide to write small tool to help me out  Please check ExcelDiff Tools  It comes with several key points    Support xls  xlsx  xlsm  With formula cell  It will compare both formula and value  I try to make UI look like standard diff text viewer with   modified  deleted  added  unchanged status  Please take a look with image below for example

User · Answer

I have found xdocdiff WinMerge Plugin  It is a plugin for WinMerge  both OpenSource and Freeware  you doesn t need to write a VBA nor save an excel to csv or xml   It works just for the celd s contains   This plugin supports also     rtf Rich Text  docx  docm Microsoft WORD 2007 OOXML    xlsx  xlsm Microsoft Excel 2007 OOXML    pptx  pptm Microsoft PowerPoint 2007 OOXML    doc Microsoft WORD ver5 0 95 97 2000 XP 2003   xls Microsoft Excel ver5 0 95 97 2000 XP 2003   ppt Microsoft PowerPoint 97 2000 XP 2003   sxw  sxc  sxi  sxd  OpenOffice org   odt  ods  odp  odg  Open Document    wj2 wj3 wk3 wk4 123  Lotus 123    wri  Windows3 1 Write    pdf  Adobe PDF   mht  Web Archive   eml  Exported files from OutlookExpress    Regard  Andres

User · Answer

If you have TortoiseSVN then you can CTRL click the two files to select them in Windows Explorer and then right-click  TortoiseSVN- Diff    This works particularly well if you are looking for a small change in a large data set

User · Answer

There is a library daff  short for data diff  which helps in comparing tables  producing a summary of their diffs  and using such a summary as a patch file   It is written in Haxe  so it can be compiled in major languages   I have made an Excel Diff Tool in Javascript with help of this library  It works well with numbers  amp  small strings but the output is not ideal for long strings  e g  a long sentence with with minor character change

User · Answer

I found an openoffice macro here that will invoke openoffice s compare documents function on two files   Unfortunately  openoffice s spreadsheet compare seems a little flaky  I just had the  Reject All  button insert a superfluous column in my document

User · Answer

Hmmm  From the Excel menu choose Window -  Compare side by side

User · Answer

Convert to cvs then upload to a version control system then diff with an advanced version control diff tool   When I used perforce it had a great diff tool  but I forget the name of it

User · Answer

If you re using Java  you could try simple-excel   It ll diff spreadsheets using Hamcrest matchers and output something like this   java lang AssertionError  Expected  entire workbook to be equal      but  cell at  C14  contained  lt  bananas  gt  expected  lt nothing gt             cell at  C15  contained  lt  1 850 000 EUR  gt  expected  lt  1 850 000 00 EUR  gt             cell at  D16  contained  lt nothing gt  expected  lt  Tue Sep 04 06 30 00  gt      at org hamcrest MatcherAssert assertThat MatcherAssert java 20    I should qualify that we wrote that tool  like the ticked answer rolled their own

User · Answer

We faced the exact same issue in our co  Our tests output excel workbooks  Binary diff was not an option   So we rolled out our own simple command line tool  Check out the ExcelCompare project  Infact this allows us to automate our tests quite nicely   Patches   Feature requests quite welcome

User · Answer

Use Altova DiffDog  Use diffdog s XML diff mode and Grid View to review the differences in an easy to read tabular format   Text diff ing is MUCH HARDER for spreadsheets of any complexity  With this tool  at least two methods are viable under various circumstances    Save As  xml  To detect the differences of a simple  one sheet spreadsheet  save the Excel spreadsheets to compare as XML Spreadsheet 2003 with a  xml extension  Save As  xlsx  To detect the differences of most spreadsheets in a modularized document model  save the Excel spreadsheets to compare as an Excel Workbook in  xlsx form   Open the files to diff with diffdog   It informs you that the file is a ZIP archive  and asks if you want to open it for directory comparison   Upon agreeing to directory comparison  it becomes a relatively simple matter of double-clicking logical parts of the document to diff them  with the XML diff mode    Most parts of the  xslx document are XML-formatted data   The Grid View is extremely useful   It is trivial to diff individual sheets to focus the analysis on areas that are known to have changed    Excel s propensity to tweak certain attribute names with every save is annoying  but diffdog s XML diff ing capabilities include the ability to filter certain kinds of differences   For example  Excel spreadsheets in XML form contain row and c elements that have s attributes  style  that rename with every save   Setting up a filter like c s makes it much easier to view only content changes    diffdog has a lot of diff ing capability   I ve listed the XML diff modes only simply because I haven t used another tool that I liked better when it comes to differencing Excel documents

User · Answer

Quick and easy with no external tools  works well as long as the two sheets you are comparing are similar    Create a third spreadsheet Type  if Sheet1 A1  lt  gt  Sheet2 A1   X       in the top left cell  or equivalent  click on the actual cells to automatically have the references inserted into the formula  Ctrl C  copy   Ctrl A  select all   Ctrl V  paste  to fill the sheet    If the sheets are similar  this spreadsheet will be empty except for a few cells with X in them  highlighting the differences  Unzoom to 40  to quickly see what is different

User · Answer

I ve done a lot of comparing of Excel workbooks in the past  My technique works very well for workbooks with many worksheets  but it only compares cell contents  not cell formatting  macros  etc  Also  there s some coding involved but it s well worth it if you have to compare a lot of large files repeatedly  Here s how it works   A  Write a simple dump program that steps through all worksheets and saves all data to tab-separated files  Create one file per worksheet  use the worksheet name as the filename  e g   MyWorksheet tsv    and create a new folder for these files each time you run the program  Name the folder after the excel filename and add a timestamp  e g   20080922-065412-MyExcelFile   I did this in Java using a library called JExcelAPI  It s really quite easy   B  Add a Windows shell extension to run your new Java program from step A when right-clicking on an Excel file  This makes it very easy to run this program  You need to Google how to do this  but it s as easy as writing a   reg file   C  Get BeyondCompare  It has a very cool feature to compare delimited data by showing it in a nice table  see screenshot   D  You re now ready to compare Excel files with ease  Right-click on Excel file 1 and run your dump program  It will create a folder with one file per worksheet  Right-click on Excel file 2 and run your dump program  It will create a second folder with one file per worksheet  Now use BeyondCompare  BC  to compare the folders  Each file represents a worksheet  so if there are differences in a worksheet BC will show this and you can drill down and do a file comparison  BC will show the comparison in a nice table layout  and you can hide rows and columns you re not interested in

User · Answer

I m the co-author of a free  open-source Git extension    https   github com ZoomerAnalytics git-xltrail  It makes Git work with any Excel workbook file format without any workarounds

User · Answer

Newer versions of MS Office come with Spreadsheet Compare  which performs a fairly nice diff in a GUI  It detects most kinds of changes

User · Answer

You can try this free online tool - www cloudyexcel com compare-excel   It gives a good visual output online  in terms of rows added deleted  changed etc     Plus you donot have to install anything

User · Answer

I found an openoffice macro here that will invoke openoffice s compare documents function on two files   Unfortunately  openoffice s spreadsheet compare seems a little flaky  I just had the  Reject All  button insert a superfluous column in my document

User · Answer

I don t know of any tools  but there are two roll-your-own solutions that come to mind  both require Excel    You could write some VBA code that steps through each Worksheet  Row  Column and Cell of the two Workbooks  reporting differences  If you use Excel 2007  you could save the Workbooks as Open-XML    xlsx  format  extract the XML and diff that  The Open-XML file is essentially just a  zip file of  xml files and manifests    You ll end up with a lot of  noise  in either case if your spreadsheets aren t structurally  close  to begin with

User · Answer

xdocdiff plugin for SVN

User · Answer

I would use the SYLK file format if performing diffs is important  It is a text-based format  which should make the comparisons easier and more compact than a binary format  It is compatible with Excel  Gnumeric  and OpenOffice org as well  so all three tools should be able to work well together  SYLK Wikipedia Article

[git] How do I create a readable diff of two spreadsheets using git diff?

Examples related to git

Examples related to version-control

Examples related to merge

Examples related to diff

Examples related to xls