problem import spreadsheet to access (1 Viewer)

eugzl

Member
Local time
Today, 16:40
Joined
Oct 26, 2021
Messages
127
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
 
Can you list all the steps you took that resulted with duplicates?
 
Did you use the append option and append multiple times?
 
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
 
Last edited:
Have you ended up with too many rows? Might you have imported to a non empty table?
 
Sample append query:
SQL:
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.
 
Last edited:
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
 
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).
 

Users who are viewing this thread

Back
Top Bottom