allenflame423
asked on
How to import CSV file into an Access Database and skip duplicates.
I'm trying import a csv file into Access database but I'd like it to skip duplicates. I was able to get the regular import to work and get the fields to line up, but then I was left with duplicates. Any suggestions?
After importing, create a find duplicates query using the wizard (query in database wizard, new, Find duplicates Query Wizard. Concert it to a delete query and run it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The existing table has lots of data in it. How can I be certain that the delete query will delete the new records only.
aikimark's suggestion is a good idea which will resolve "How can I be certain that the delete query will delete the new records only."
Otherwise you can add a Where clause in the query so only new records are selected.
Otherwise you can add a Where clause in the query so only new records are selected.
ASKER
Ok, I've found out there is certain data in the new csv file that I'd like to update in the current table. How involved would this be to update certain fields? The database is basically our school computer inventory from when we walked around to every machine. It includes lots of data on the machine, including model, serial, location, asset number, room number, and supposed computer name. The csv file is an export from out of our Zenworks Inventory, and includes the Model of the machine, serial number, computer name, and current user. The only two fields I'd like to update are current user and model of machine.
ASKER
so using the serialnumber as a unique index would keep them from importing if they already exist?
>>so using the serialnumber as a unique index would keep them from importing
Yes
===================
>>updating fields
That is a different question. Keep this question simple -- eliminating duplicates during CSV import
Yes
===================
>>updating fields
That is a different question. Keep this question simple -- eliminating duplicates during CSV import
Setting serialnumber index to yes, no duplicators will prevent importing records with duplicate serialnumbers.
An error message will be produced and a table listing the duplicated words will be created.
aikimark should get the points. it is his suggestion.
aikimark should get the points. it is his suggestion.
@thenelson
I don't think the Import Errors table is created or populated for duplicate keys like it is with field-level (data type) errors. You will just see the message about the number of rows NOT inserted.
I don't think the Import Errors table is created or populated for duplicate keys like it is with field-level (data type) errors. You will just see the message about the number of rows NOT inserted.