cancel
Showing results for 
Search instead for 
Did you mean: 

OLEDB connect with two different Excels and interact with the tables

Ndauti
Level 3

Hello All,

I am extracting a report on a daily basis and need to compare last report with actual report. Tables are identical. I might have 10k-15k rows each excel. I just need to see which customers (ColA) have chenged the amount (ColB). Performing this action via Loop it would take several minutes. I than thought OLEDB might be helpful. Do you know if it is possible to perform this action in OLEDB? I have the standard OLEDB Data business object and it seems like it can create connecion with only one Xlsx file. Is it possible to create two connections? I saw a VBO "Data - OLEDB(Multiple Connections)" do you ever used it? 

Thank you

TIm 

1 REPLY 1

EricNewton
Level 2

I am not familiar with the multiple connections VBO but you should be able to use the Data - OLEDB VBO with single connection to do a join between the 2 files. You can set up the connection to your new file then in the SQL query you can do a join on to the prior file specifying the connection details in the query.

Query should look something like this

"""

SELECT t1.[key], (t1.[value] - t2.[value]) AS [Diff Of Values] FROM [1st_Excel_sheet_name$A:B] as t1 INNER JOIN [Excel 12.0 Xml;HDR=Yes;Database=path_to_2nd_excel].[2nd_Excel_sheet_name$A:B] as t2 ON t1.[key] = t2.[key]

"""

Obviously just replace with the details of your actual excel files, (file paths, sheet names and column names). If you only need to look at data in the current file then I think a LEFT JOIN would work, example is for INNER JOIN. If you need an OUTER JOIN then you would need to do a LEFT and RIGHT join separately then combine and remove duplicates (if I am remembering correctly).