Git diff and merge tool for Excel and CSV files

How to integrate xlCompare into GIT, SVN, SourceTree, Perforce as diff and merge tool for Excel files.

Install xlCompare on your computer and specify diff viewer options in the GIT, SVN, SourceTree, Perforce.

Compare three Excel files using xlCompare

3-way comparison tool for Excel files

Perforce (P4V)

Integrate xlCompare into Perforce desktop client

SVN

Integrate xlCompare into SVN

Step by step instructions how to use xlCompare with GIT

Excel files are binary files - old XLS format is a set of streams and records in the Biff format, modern XLSX is a ZIP file with a set of XML files inside. For the git this is binary files that can't be processed like text documents.

xlCompare fills this gap and allows to see the difference between two Excel files and merge contents of two of three Excel files in the version control systems like GIT, SVN, SourceTree, Perforce, ...

Below is a simple integration procedure that connects your git repository with xlCompare.

xlCompare has simple set of command line arguments that is standard for diff viewer tools and merge tools. So, you can integrate it into almost every version control application.

Step 1 - Install xlCompare on your computer

For this you need to visit Download page, download the setup and run it on your computer.

Step 2 - Edit .git/config file

Add following lines to the .git/config file in your repository:

[diff "xlcompare"]
    command = 'C:/Program Files/Spreadsheet Tools/xlCompare/exceldiff.cmd'
[merge "xlcompare"]
    name = xlCompare
    driver = 'C:/Program Files/Spreadsheet Tools/xlCompare/excelmerge.cmd' %A %O %B
[mergetool "xlcompare"]
    cmd = 'C:/Program Files/Spreadsheet Tools/xlCompare/excelmerge.cmd' \"$LOCAL\" \"$BASE\" \"$REMOTE\" \"$MERGED\"
    trustExitCode = false

IMPORTANT: Path to the xlCompare folder contains space characters, so we've wrapped it into single quote.

If you miss this quote chars - you will receive an error message in the Git console.

%A %O %B are command line arguments, used by GIT for the merge tools:

%A - LOCAL version of Excel file
%O - BASE (common) version of Excel file
%B - REMOTE version of Excel file

Why there are two lines for merge.

First one (merge) sets xlCompare as a driver for automatic merge operations, when you are updating files in your local repository.

Second line (mergetool) allows to use xlCompare with a command git mergetool --tool=xlcompare -y --no-prompt {file path}

trustExitCodeis set to true, so you will decide if merge operation was successful. However in the merge mode xlCompare returns number of conflicts as exit code. So, if needed, you can set this argument to true.

Step 3 - Connect Excel files in git with xlCompare

This is final step in our guide. Create .gitattributes file in the root of your repository. Possible this file already exists.

Add following line to the end of the file:

*.xlsx diff=xlcompare
*.xlsx merge=xlcompare

It points git to use xlCompare with Excel files, when diff viewer or merge tool is needed.

Start using xlCompare with git diff command!

That's all. You've connected your xlCompare with Excel files in the git repository.

Now git diff command shows difference for 2 Excel files by opening xlCompare with difference report.

How to integrate xlCompare into GUI tools like SourceTree, etc.?

If your version control application allows to set a GUI tool for diff and merge operations with Excel files, you can use these settings:

Diff tool

Use path to your xlCompare as tool. By default it is 'C:/Program Files/Spreadsheet Tools/xlCompare/exceldiff-source-tree.cmd'

Command line switches are following: $LOCAL $REMOTE

Merge tool

Use path to your xlCompare as tool. By default it is 'C:/Program Files/Spreadsheet Tools/xlCompare/excelmerge.cmd'

Command line switches are following: "$LOCAL" "$BASE" "$REMOTE" "$MERGED"

What about if version control app doesn't have $MERGED parameter?

Just remove it from command line and use following syntax: "$LOCAL" "$BASE" "$REMOTE"

IMPORTANT: we are using $LOCAL, $BASE and $REMOTE names in the command line switches above. Possible you need to replace them with appropriate names, used by your version control application.

NOTE: command files exceldiff-source-tree.cmd and excelmerge.cmd are included into xlCompare installation package. You have them on your computer at the following location: %PROGRAM FILES%\Spreadsheet Tools\xlCompare

Questions and Answers

In this topic we've described some common questions about git integration.

If you need an assistanse with using xlCompare as a difference viewer or merge tool for Excel files - contact us. We will be happy to help you.

Why do you use CMD files as a drivers for diff and merge tools?

You need to use this method, if you version control system requires console application as a diff or merge tool.

xlCompare itself is a GUI application, that is not always works when console tool is required.

In this case you can use our drivers or create a similar ones for your version control application.

What is inside CMD file

exceldiff.cmd

"%~dp0xlCompare.exe" %5 %2 -quit_on_close -titleMine:LOCAL -titleBase:REMOTE -rep:"%__CD__%"

"%~dp0xlCompare.exe" - path to xlCompare application that is in the current directory
%5 - LOCAL version of Excel file, git uses 7 arguments, 5-th one is a local file
%2 - REMOTE version of Excel file
quit_on_close - After you close the comparison xlCompare will exit. This will save you a second for clicking on the close button.
titleMine:LOCAL - Git uses temporary file names, so LOCA and REMOTE will clearly identify the file.
rep:"%__CD__%" - path to repository. This is highly important switch that should be used as last one. Without this parameter you will be unable to open files in subfolders.

excelmerge.cmd

"%~dp0xlCompare.exe" -merge -titleMine:LOCAL -titleBase:BASE -titleTheir:REMOTE "%1" "%2" "%3" -output:"%4" -rep:"%__CD__%"

"%~dp0xlCompare.exe" - path to xlCompare application that is in the current directory
git - says that it is used as external tool for GIT. It will exit after you close the comparison.
merge - perform merge operation on the passed files and open GUI window is there are conflicts.
titleMine:LOCAL - Git uses temporary file names, so LOCA and REMOTE will clearly identify the file.
"%1" "%2" "%3" - paths to local, base and remote versions of Excel files.
output:"%4" - if xlCompare is called with mergetool command, this switch tell xlCompare to save result file under $MERGED path. MERGED file is passed as 4-th argument in the mergetool syntax, described above.
rep:"%__CD__%" - path to repository. This is highly important switch that should be used as last one. Without this parameter you will be unable to open files in subfolders.

As you see contents of the CMD files are simple, you can modify them by adding CONFIG switch or create your own CMD wrappers for GIT.

Do I need a license to use xlCompare as git diff and merge tool?

Yes. xlCompare requires a license to be used as git or merge tool for Excel files.

Can I use xlCompare as git merge tool for Excel or CSV files?

Yes. Please, use the instructions shown above to integrate xlCompare into your GIT application.

How to automatically set Primary Keys and Heading Rows in my Excel file?

This is very important question. Let us explain why.

Primary Keys and Heading Rows is an important part of the Excel file comparison process. If your spreadsheet has table structure - contains list of records, key columns and heading rows as a part of the comparison algorithm for such type of files.

When you open your Excel files in xlCompare you can define key columns and rows using right click menu commands and this works perfectly.

But, in the command line mode, that can work without user interaction, this is impossible.

So, we've added a config swicth to the command line, that allows to run VBA macro for the every opened workbook.

In this macro you can use all the objects and methods that are available in the Excel workbooks in VBA.

xlCompare is based on the Spreadsheet Core engine that allows to run VBA macros as in Excel application.

How to use CONFIG switch in the command line

xlCompare.exe -config:"path your file on disk.vbs"

Config file itself is a text file with extension .VBS (Visual Basic Script).

In the examples below we are using Excel files where all worksheets have same structure. But you can define different combinations of keys for different worksheets. VBA is very flexible.

Set Primary Key column A and heading row 1

Contents of the config file is the following:

Public Sub Main(book as Object)

	Dim sheet as Object

	For Each sheet In book.Sheets
		sheet.PrimaryKeyColumn = "A"
		sheet.PrimaryKeyRow = 1
	Next

End Sub

Set Primary Key columns B,C,F and no heading rows

Contents of the config file is the following:

Public Sub Main(book as Object)

	Dim sheet as Object

	For Each sheet In book.Sheets
		sheet.PrimaryKeyColumn = Array("B","C","F")
	Next

End Sub

Set Primary Key column A,B,C and heading rows 4,5

Contents of the config file is the following:

Public Sub Main(book as Object)

	Dim sheet as Object

	For Each sheet In book.Sheets
		sheet.PrimaryKeyColumn = Array("A","B","C")
		sheet.PrimaryKeyRow = Array(4,5)
	Next

End Sub

I have more complex case, what to do?

Please, contact us using the form below, we will be happy help you with integration.

I'm getting VBA error messages when using this code?

Please, contact us and describe the issue. Our support will help you to solve the problem.

I have a question about xlCompare

Please, describe your question or inquiry in the form below. We will be happy to respond you!

Name:

E-mail:

Question: