Alternate for Vlookup with sql queries

Hi @Ishwarya Kalidoss​ ,

If its excel, then you can't share the session with two files.

That only works for CSV, so if you want to perform operations onto multiple files, either you can convert them to csv and connect to the Folder Path and perform SQL operations, or you can Merge the Excel Workbooks together and perform SQL queries with both sheets.

If you want to connect to excel/csv like it were a database and perform JOINS, then here is an article I've written that could help you out.

Kind Regards,

Ashwin A.K

Thanks, @Ashwin A.K​ .. I've corrected it as you suggested, and I have 2 CSV files in the same path now. Here's what Im trying to achieve.

File 1 -

Ref#

5456

INV12

76589

File 2-

Ref#

5456

76589

Output-

INV12

Ref# column may have alphanumeric values. I tried with the below queries ( Ref# as common_id) but it returns null.

SELECT *

FROM  common

WHERE  NOT EXISTS

    (

    SELECT NULL

    FROM  table1 t1

    WHERE  t1.common_id = common.common_id

    )

SELECT *

FROM  common

WHERE  common_id NOT IN

    (

    SELECT common_id

    FROM  table1 t1

    )

Appreciate any suggestions to make it work?

Hi @Ishwarya Kalidoss​ ,

DB operations sometimes won't read alphanumerical values(not sure why), but you can give this a try:

Connection String:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$sFolderPath$; Extended Properties = "text";

Query:

SELECT t1.[Ref#]

FROM [firstCsvFile.csv] AS t1

WHERE t1.[Ref#] NOT IN

(SELECT t2.[Ref#]

FROM [secondCsvFile.csv] AS t2)

Kind Regards,

Ashwin A.K

the query works for numeric but not for alphanumeric as you said. Could you suggest any alternative solutions to achieve this? @Ashwin A.K​ 

I did try that query Ashwin..But its returning null