Question

Alternate for Vlookup with sql queries

  • 1 July 2022
  • 5 replies
  • 34 views

Badge +6

Hi Experts,

 

I have 2 files. File 1 with 10 records and File 2 with 15 records. I need to fetch the 5 new records that are newly added in file 2 comparing file 1.

 

I'm trying to achieve this with Subqueries. Something like select ID from file2 where ID not in (select ID from file 1). But I'm not sure if this is the right approach and also unsure of querying with 2 different files connected as DB..

 

Could anyone help to achieve this without a macro or looping files?

 

 

@Paul Hawkins​ 

 

 

 


5 replies

Userlevel 3
Badge +7

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

Badge +6

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?

Userlevel 3
Badge +7

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

Badge +6

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

Badge +6

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

Reply