0

I have to compare two .csv sheet's data. the two .csv sheets have composite primary key.then add the records which are not common in the two .csv sheets in an another .csv sheet. Then i have to export the 3rd sheet's data to an access database file.

i am using perl to fetch the records which are not common in the two .csv sheets and putting those records in another .csv file. I am not sure how to export the 3rd .csv file's data to an access database file. Please help.

Also let me know if there is any other way to do this except using perl.

All this to be done automatically though a perl script or a macro.

HansUp
  • 95,961
  • 11
  • 77
  • 135
abhijit
  • 1
  • 1

1 Answers1

1

This should be pretty simple.
1. Import the csv sheets to compare into an access database table (http://office.microsoft.com/en-us/access-help/import-or-link-to-data-in-a-text-file-HA001232227.aspx)
2. Make sure that the primary key fields are defined properly in the table structure (should be set as numeric
3. Do an inner join on the two tables based on the primary key
I haven't tested this but it should work, basically you want to select all of the rows not in table1 or table2 into table3. The inner nested query with the inner join will find the matching rows and then the outer union query pulls in the rows that don't match

Select fields
into table3
from table1
where table1.id not in (

Select table1.id 
from table1 
  inner join table2 
  on table1.id=table2.id
)
union table2
where table2.id not in (
Select table1.id 
from table1 
  inner join table2 
  on table1.id=table2.id
)
Summit
  • 1,223
  • 2
  • 12
  • 15
  • hey Summit thanks for the help.But I need to automate the process.There should be a macro or a perl script which can do the abve stuff. – abhijit Aug 20 '11 at 18:28
  • Create a macro that automates the input and the above query and schedule the macro: http://stackoverflow.com/questions/4001516/how-can-i-schedule-a-macro-to-run-automatically-in-access-2007 – Summit Aug 20 '11 at 22:44