How to compare data between Excel and SQL Table to get unmatched records.

how to get unmatched records compare Excel and sql table, both sources have same columns need to validate the data on daily to Blue Prism. appreciate any help!!


Well-Known Member
Staff member
Hi vullim,

You will have to gauge the possibilities of what can or cannot be done based on your project.
Below are some of the options.

- Load the Excel to an SQL table and perform the comparison in SQL:
Using a connection string for Excel and an insert SQL query, load the data from the sheet to an existing or a new table on SQL. This can be something like the connection string and sql statement in the below link.
(The cnn.Open, nSQL and nJOIN lines in the above link)

Once these strings are formed as [data items] as per your excel file, use the Blue Prism's SQL VBO and pass these data items to it and run and check how the sheet data gets uploaded into the table.
Once the data is in the table, further you can have an SQL query something like the one in the below link that is marked in blue as answer.

This query needs to be on the SQL Server so that it can be called from Blue Prism via the SQL VBO.
On executing the query, the results (unmatched records) can be returned in a Blue Prism Collection via the SQL VBO.

Alternatively, you can even write a Custom code to upload an Excel file into an SQL table if you prefer that.

- Load the SQL table to an Excel and perform the comparison in Excel:
There is no straight forward way to do an entire row comparison between two sheets in Excel except making use of macros.
So if your project allows you to write a macro (available by doing a web search) then that macro can be called from Blue Prism via the Run Macro action.
The macro will return the unmatched records on a sheet which can then be taken into a Collection using the 'Get Worksheet as Collection' action of Blue Prism.

- Load the SQL table and the Excel into a Collection and perform the comparison on the Collection:
The rows of the SQL table can be returned to a Collection by simply firing a Select statement on that table using the SQL VBO.
The Excel can be taken into a collection using the 'Get Worksheet as collection'.

Once both are in a Collection it will require writing a custom code to perform the comparison and find the unmatched records between two Datatables. Collections as stored as Datatables (of .net) in Blue Prism so you can do a web search on how to get the unmatched records between two Datatables in .net (VB or C#) depending on the language of your choice.
Also you can take a look at any of the existing code in the Utility Collections VBO and see how a Collection can be interacted with and write your own code.

What you need to ensure while interchanging data between these sources (Excel, SQL, Collections) is about the datatypes. This is because I see dates in your screenshot and as an example a Date column in Excel shows up as Text column in a Collection when you do a 'Get WS as collection'. Also check the same when done through SQL. So you need to a research and analysis and see if datatypes cause any issues during the comparison of the values and decide upon the best possible approach.
Thank you for taking time to provide detailed information. I'll work on this and get back if I need some help to achieve I am new to BP it might take some time to figure this out..much appreciation for the help.