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

Compare two Excel tables with CSV in MS Access : Fig 1 : First Excel table to be compared
Fig 1 : First Excel table to be compared
Compare two Excel tables with CSV in MS Access : Fig 2 : 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).

Compare two Excel tables with CSV in MS Access : Fig 3 : Saving first file as CSV
Fig 3 : Saving first file as CSV
Compare two Excel tables with CSV in MS Access : Fig 4 : 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).

Compare two Excel tables with CSV in MS Access : Fig 5 : Compare2CSVfiles-v1.2.mdb language selection screen
Fig 5 : Compare2CSVfiles-v1.2.mdb language selection screen
Compare two Excel tables with CSV in MS Access : Fig 6 : Providing first file name in Compare2CSVfiles-v1.2.mdb
Fig 6 : Providing first file name in Compare2CSVfiles-v1.2.mdb
Compare two Excel tables with CSV in MS Access : Fig 7 : 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).

Compare two Excel tables with CSV in MS Access : 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 :

- a first file with new lines (Fig 8) found in second file but not in first file, with amount of new lines in file name,

- a second file with deleted lines (Fig 9) not found in second file but found in first file, with amount of deleted lines in file name,

- a third file with cell changes (Fig 10) from an old to a new value, with amount of changes in file name.

Compare two Excel tables with CSV in MS Access : Fig 8 : Compare2CSVfiles-v1.2.mdb added lines result table
Fig 8 : Compare2CSVfiles-v1.2.mdb added lines result table
Compare two Excel tables with CSV in MS Access : Fig 9 : Compare2CSVfiles-v1.2.mdb deleted lines result table
Fig 9 : Compare2CSVfiles-v1.2.mdb deleted lines result table
Compare two Excel tables with CSV in MS Access : Fig 10 : 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].

Similar articles

Comments (17)

Sherry Moody, 2018-08-19
Gracias, muy informativo
Caleb Evans, 2018-08-19
Me encanta leer tu contenido, sigue publicando
Brandon Watts, 2018-08-19
Grazie, molto istruttivo
Roderick Franklin, 2018-08-19
Eu não sabia sobre esses detalhes, por favor, faça mais
Bobbie Estrada, 2018-08-19
Obrigado que foi útil
Randy Russell, 2018-08-19
Yani böyle yapıyoruz, bilmek güzel
Rufus Anderson, 2018-08-19
เรื่องดีขอขอบคุณสำหรับการแบ่งปัน
Edgar Brown, 2018-08-19
Nu știam despre aceste detalii, vă rog să faceți mai mult
Irene Moore, 2018-08-19
I’ll try it right now, thanks for sharing
Jane Cooper, 2018-08-19
Uau, é realmente assim tão simples, vai tentar agora
Phillip Sanchez, 2018-08-19
Eu vou tentar agora, obrigado por compartilhar
Goosearma, 2018-08-19
素晴らしいサイトです
FighterGrand, 2018-08-19
वास्तव में जो मैं खोज रहा था, बिल्कुल सही
xangarandaina6, 2018-08-19
정확히 내가 뭘 찾고 있었는지, 완벽 했어.
ynternaat0, 2018-08-19
Hallo, ik zag je artikel en het hielp me om mijn probleem op te lossen, heel erg bedankt
modderl, 2018-08-19
素晴らしい情報、共有のおかげで
abastanzaP, 2018-08-19
וואו, זה באמת כל כך פשוט, ינסו את זה עכשיו

Leave a comment