Avoiding duplicates when importing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everybody! Does anyone have any great ideas for avoiding duplicates when
importing a CVS file into Access? When the record has been changed outside of
Access, it creates a new record in Access, rather than writing over the
existing record.
Any thoughts on the matter are VERY welcome!
Many thanks.
 
Import to a temp staging table.
Then write a query to only add new reocrds, not existing ones.
 
Hi Joe. Thanks for your input. Could I ask you to be a bit more explicit in
exactly what it is I should do? I am not sure I follow the concept of a temp
staging table, the query, etc.
Many thanks for your support.
 
OK.
1. Import the file using the wizard.
2. One choice is to New table or Existing table. Pick New.
3. Get to the last page of the wizard and choose the Advanced button.
4. Save the spec as a certain name (write it down - you will need it later.)
5. Finsih the import.
6. Review the data in the new table - is it correct ? Did everything import?
7. If no problems then continue - else go back and fix them until it works
right.
8. Now you are ready to repeat the process. Open the table and delete all
the data - but leave the table itself!
9. Re-import the file but this time choose existing table and pick the one
you just emptied. (this is your staging table.)
10. Did it work?
11. Now use code to do it. Check out TransferText (you will need the name of
your spec.)
12. Empty the staging table and use your code to automatically import the
same file (for the 3rd time at least!)
13. Did it work? Good!
14. Now write a query based on the stagin table to add data to your "real"
table.

This is what I meant.
HTH
 
oJoe, many thanks for taking the time to run through the steps! I follw your
logic, no pb. Would it be too much to ask that you give me a tip re the
"query"? Are there template queries of this kind (I have no clue about
drafting a qquery, truth be known!)?
Cheers.
 
Query
New
Find Unmatched Query Wizard
(This shows how to find all the non-duplicates.)

Use it as the basis for your Append query from staging table to real table.

Practice writing queries.
Start with the wizards, query grid and read Help.
Then learn SQL (the grid "hides" the SQL from you. You can read it by
switching to SQL view.)
 
Back
Top