How to export data of .csv file to access database file

Question

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.

Hint: Exporting csv to access is the same as Importing csv from access. — TLP
Have you considered linking the two CSVs as tables in Access and simply running a make table query? — Fionnuala

Summit Summit · Accepted Answer · 2011-08-20T17:20:32

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
)

How to export data of .csv file to access database file

1 Answers