Question Importing CSV Getting 'Duplicate Records Violation' When there is no duplicates (1 Viewer)

[KevinM]

(Access 2010).
I'm using the following vb code to import from a csv file:

DoCmd.TransferText acImportDelim, strFileImportSpec, strTableNameImport, gb_strPathAndFileName

Everything imports fine yet I always get the standard 'Duplicate records violation MS Access error message':

'MS Access was unable to appand all the data...2 record(s) were deleted, and 0 records were lost due to key viloations....'

There are no constraints in the destination table and the records are always imported ok.

I don't get the error msg when I import vis the 'External Data' on the menu bar.

Trired it with several csv files and get the same eror msg.

It was working fine last week!

 

[Minty]

Have there been some "empty" lines added to the end of the spreadsheet you are importing? They can appear as if there is data in them to access so it thinks they would be duplicates assuming you have a key field.

 

[gemma-the-husky]

can you sort the import by the key fields, and then inspect it manually for duplicates.

either that, or add a unique record sequence number and then identify the missing numbers.

If access thinks there are duplicates, then there are.

out of interest, how many records are there in the data file, and how many in the imported table?

 

[KevinM]

There are only 2-4 records in each file.

The PK is a manual type (also tried auto).

I've opened the CSV file in notepad and ther are no extra rows or null values.

These are only test files so I can manipulate them if need be.

It was working fine last week!

 

[Tieval]

Access should create a table of errors, have you checked for this?

 

[gemma-the-husky]

well if you have 4 records it will be easy to see which one is missing!

are you sure you are importing to an empty file. If there is data in the file already, then the new data may well clash with existing data.

 

[The_Doc_Man]

You get the error when using VBA but not when using the menu function, and even with the error your data sets import correctly. Is that true? If so, I understand your confusion. At least in theory, the menu function does what the VBA function does, but the difference is a wizard that does a setup. (Or do you not use the wizard through the menu because you have them turned off?)

In any case, open the miscreant CSV file using WORD if you have it and click on the little "paragraph" symbol that shows you explicit line feeds and line breaks. Since you don't have to actually save the result, WORD won't do any damage; you can just close without saving. What you are looking for is excessive blank lines.

How did the CSV files get generated, by the way? EXCEL won't add extra lines unless it had extra lines and you just erased them (as opposed to deleting them, which is a different action to EXCEL.)

One last question: Do the CSV files have column header lines that might overlap, name-wise? Because column headers are recognized by the wizard but not necessarily by the VBA.

 

[KevinM]

You get the error when using VBA but not when using the menu function, and even with the error your data sets import correctly. Is that true? If so, I understand your confusion. At least in theory, the menu function does what the VBA function does, but the difference is a wizard that does a setup. (Or do you not use the wizard through the menu because you have them turned off?)

Yes, error when only using vba. I use the Wizard and don't get any 'import errors'.

In any case, open the miscreant CSV file using WORD if you have it and click on the little "paragraph" symbol that shows you explicit line feeds and line breaks. Since you don't have to actually save the result, WORD won't do any damage; you can just close without saving. What you are looking for is excessive blank lines.

It just showing the line break symbol at the end of each row (see attached snapshot).

How did the CSV files get generated, by the way? EXCEL won't add extra lines unless it had extra lines and you just erased them (as opposed to deleting them, which is a different action to EXCEL.)

I just manually created it so have complete control at dev stage until it goes live

One last question: Do the CSV files have column header lines that might overlap, name-wise? Because column headers are recognized by the wizard but not necessarily by the VBA.

I don't think so.

Not sure what has changed. I'll rebuild the file from scrtach and try it with onlt one row.

Thanks all for your help.