Import from .csv

[kdibricida]

I have a .csv file that I receive once per month. It is a current user list. I need to import the list of users into an access database table each month. Is there a way that I can have only the changes made to this file imported each month? Right now when I do an import it appends all of the data in the .csv to the table and I end up with duplicate entries.

 

[dhookom]

I would append the new records from the CSV into a temporary table. Then use queries to update or append records to the permanent table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kdibricida]

Thanks for the response.

Is there a way i can query for differences in the tables?

 

[dhookom]

Yes you can query for differences. This basically assumes you have some primary keys or uniqueness in your tables. We don't know anything about your tables so if you need some specific help, we need some specific information about tables and fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SeeThru]

Add both tables to a query, linked by primary key.

Add each 'pair' of fields to give a true/false result

i.e. Iif (Maintable!field1=NewTable!Field1,true,false)

Then you have a query that returnes true or false for each field if it has changed or not. you can use this to determine which records to update.

Create another query that adds new records only (link by primary key, all from new table, related only where available, set the primary key from Main Table to isNull, and append)

Create another that removes records that have been deleted (delete query, main table and New Table, linked by primary key, all from maintable, matched from new table, delete where no match to New Table.)