problem import spreadsheet to access (1 Viewer)

[eugzl]

Hi All.
I tried to import data from an Excel spreadsheet that has no duplicates into Access. When I used the Access wizard to do this, I ended up with a lot of duplicates in the table. When I copied and pasted the same spreadsheet into an Access table, there were no duplicates. What is the problem? How to properly avoid duplication when importing data?

Thanks

 

[theDBguy]

Can you list all the steps you took that resulted with duplicates?

 

[Pat Hartman]

Did you use the append option and append multiple times?

 

[eugzl]

Can you list all the steps you took that resulted with duplicates?

1. Convert .TXT file to .XLSX file
2. In menu bar select External Data --> extend New Data Source --> From File --> Excel
3. In Get External Data click Browse button to navigate Excel file
4. In Import Spreadsheet Wizard check fields name and their data type --> click Next
5. Choose my primary key --> click Next
6. Specify table Name --> click Finish

Nothing special. Thanks

 

[gemma-the-husky]

Have you ended up with too many rows? Might you have imported to a non empty table?

 

[ebs17]

Sample append query:

INSERT INTO
   AccessTable(
      Key1,
      Key2,
      Value1,
      Value2,
      Timestampfield
   )
SELECT DISTINCT
   E.Key1,
   E.Key2,
   E.Value1,
   E.Value2,
   Now()
FROM
   [excel 12.0 xml;hdr=yes;imex=1;DATABASE=X:\somewhere\YourWorkbook.xlsx].[TableName$] AS E
      LEFT JOIN AccessTable AS A
      ON E.Key1 = A.Key1
         AND
      E.Key2 = A.Key2
WHERE
   A.Key1 Is Null

Convert .TXT file to .XLSX file

You could also import directly from the text file in the same way.

 

[CJ_London]

You could also import directly from the text file in the same way.

Only if the txt file is actually csv. Otherwise you have to add creating a schema.ini file into the process (not difficult, just needs to specify the filename and delimiter) or mess around in the registry changing defaults

Or just use the import text wizard or transfertext rather than transferspreadsheet

 

[ebs17]

Only if the txt file is actually csv

The CSV file extension alone does not promise unstressed import success, it is given out generously.
For direct and immediate use, it would be important for the text file to contain a text table that can be read and evaluated as a database table using the import specification, Schema.ini or a randomly matching registry setting. The requirement is therefore more of a structure requirement than a requirement for the file extension (which can also be different).

In comparison, however, it must also be said that there are similar structural requirements for the table in an Excel worksheet (=> list, intelligent table).