+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?
+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
+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?
+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
+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
+6
I did try that query Ashwin..But its returning null
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.