Various

Compare two Excel tables with CSV in MS Access

It may happen to end with two different tables, in which you just want to know what are the exact differences, especially when you manage a lot of data.

I propose you a simple way to quickly get differences between two tables – the only difficult part is to get them in the right format !

The first step is to get two Excelicon workbook, for example an old one (Fig 1) and a new one (Fig 2).

 First Excel table to be compared
Fig 1 : First Excel table to be compared
Second Excel table to be compared
Fig 2 : Second Excel table to be compared

Check that both tables have the same columns in the same order with the same naming, and that the first one contains a unique identifier – meaning there is no two times the same value in this column within the same file, and that same lines of both files have the same value in this column.

Also, you may want to order your tables, as the program will not do it – you can use standard Excel order function to do so.

Then, you should convert your Excelicon tables in csvicon. Open then, and choose “File” => “Save As…” => “CSV (Comma delimited)(*.csv)”, for the first file (Fig 3), and then again for the second file (Fig 4).

Saving first file as CSV
Fig 3 : Saving first file as CSV
Saving second file as CSV
Fig 4 : Saving second file as CSV

You can now download the Accessicon database I propose you[1] and save it in the same folder as your two csv files.

Open it and select your language in the Macros menu (Fig 5), and the program will ask you to provide the name of the first file (Fig 6) to compare to the second one (Fig 7).

Compare2CSVfiles-v1.2.mdb language selection screen
Fig 5 : Compare2CSVfiles-v1.2.mdb language selection screen
Providing first file name in Compare2CSVfiles-v1.2.mdb
Fig 6 : Providing first file name in Compare2CSVfiles-v1.2.mdb
Providing second file name in Compare2CSVfiles-v1.2.mdb
Fig 7 : Providing second file name in Compare2CSVfiles-v1.2.mdb

Pay attention to give the full name of the csv file, and to have them located in the same folder, or you will get an error (Fig 11).

Compare2CSVfiles-v1.2.mdb error message
Fig 11 : Compare2CSVfiles-v1.2.mdb error message

As a result, you will get up to 3 files, depending on type of errors found :

Compare2CSVfiles-v1.2.mdb added lines result table
Fig 8 : Compare2CSVfiles-v1.2.mdb added lines result table
Compare2CSVfiles-v1.2.mdb deleted lines result table
Fig 9 : Compare2CSVfiles-v1.2.mdb deleted lines result table
Compare2CSVfiles-v1.2.mdb modified cells result table
Fig 10 : Compare2CSVfiles-v1.2.mdb modified cells result table

Example files were built using Random Word Generator (Plus)[2].