Help with Import

[Thwarted]

I have data that is imported from a text file into my database. Is there a simple way to put up a warning flag if the same data is about to be imported?

Thanks in advance!

 

[sdk]

Hi,

Much depends on how you will determine duplicates, and if you need notification at each row etc. At a guess, you can either:

- open your file and explicitly check row by row - fairly messy, lots of code, but allows explicit row action (dump, renumber etc)

- attach/import your file, check for duplicate entries against existing equivalent (simple select qry) or only import new values to unique table (append qry) and detach/destroy text table - less messy but probably best. You then need to decide what to do with duplicates - dump, renumber etc.

Cheers

Steve

 

[Thwarted]

This will work in an append query? The entire record would have to match an existing record in the table.
Would Distinct Row work?

Thanks so much for your help.

 

[TomCologne]

Which qry will do what for you depends very much on the circumstances:

Appending (or not) to an existing table identifying each record by a primary key, i.e.SSN, with the txt.file having that same identifier = too good to be true! But you could still set your own criteria according to your needs.

Is the import a recurring event, and more importantly, are you dealing with consistently solid data in the .txt file, Delimiters, Format, Size, Spelling??
"Big Money, Cheat & Sons, Inc" or "Big Money Cheat & Sons Inc" can be a very different import.

I found importing the entire file into a temporary Access tableand then selecting, filtering formatting, editing....the method best working for me, Word holds too many surprises for my taste and I simply feel more comfortable within the Access environment.

One last remark about Appdqry: In Select view it may work fine, but appending fails due to some "Key" violation, even though all settings match perfectly. This message almost drove me crazy until I found a Microsoft article about changing the underlying table's settings "Required" to 'No' in some cases to make it work. A 97 bug!

You're not alone,

TomCologne

 

[Thwarted]

Thanks Tom
Ideally we would just like it if the user would NOT import the same text twice - but it continues to happen. They have an entire process to follow - they must download data from a handheld device - run a batch file that puts it into a text file in a temp folder where access looks for it - converts it and then the batch continues and moves it to a backup folder and then they run another program to delete the data from the handheld. If they would stop there as they should - it would be great - but if something happens - they forget one step at the end for instance - they start over - so they end up importing the exact same data into Access twice.
So I need the entire record to be identical, unfortunatly i cannot use primary key because i have made that autonumber as there are no other unique identifiers.
I will try the append query to see if i can make that work -thanks so much for your advice. Sorry for this long winded response