Table not populating after DoCmd.TransferText

[mp3909]

Hi!

I have a csv file called Data.csv that annoyingly contains duplicate records.

So I open up this file, manually delete these duplicate records using the =COUNTIF(A:A,A2) formula (provided row 1 contains headers) then I apply a filter to delete all rows where the COUNTIF formula returns > 1.



Then I save the changes made to the file and close it as DataAmended.csv (still keeping it as a csv).

So nothing has changed apart from removing duplicates and file name.

Now in my access program, when it gets to the code

DoCmd.TransferText acImportDelim, "Standard Output", "tbl_Security", "C:\Txtfiles\DataAmended.csv"

a table called tbl_Security gets created but when I open it, it is empty!!

But when I point the above code to the original file Data.csv i.e.

DoCmd.TransferText acImportDelim, "Standard Output", "tbl_Security", "C:\Txtfiles\Data.csv"

a table called tbl_Security gets created and when I open it, it is not empty

Any advice please?

Thanks!

[JoeM]

Note that you must be careful in opening/editing CSV files in Excel. Excel will do things like drop leading zeroes, that could change the nature of your data.

Why not just set up the shell of the table in Access first, and make the first field your primary key field (so it will not allow duplicates), and then use your command above to import the file?
Then all duplicates will simply be dropped/ignored, while the rest of the data gets imported into your table.

[mp3909]

Hi JoeM,

Thanks for your suggestion, I will give that a go!
Actually, column D in my CSV file has to be unique so can I make set field 4 to be the primary key field or does it always have to be the first field?

By the way, can this all be set up using the import textfile wizard?

Thanks!

[JoeM]

Actually, column D in my CSV file has to be unique so can I make set field 4 to be the primary key field or does it always have to be the first field?

You can make any field the primary key field.

By the way, can this all be set up using the import textfile wizard?

What I typically do is the following:
1. Set up the table structure, will all the proper field names, data types, and primary key
2. Invoke the Import Wizard choosing to Append the data from your file to the blank table you just set up
3. Go through all of the steps of the Import Wizard, but before completing, click the Advanced option, and save the Import Specification
4. Set up a Macro or VBA to import the file (like the code you had originally), being sure to enter the proper file name, table name, and import specification name, and use it going forward

[mp3909]

Hi JoeM,

I am falling into problem when I set my column D to be the primary key field.
It doesn't work because although there are duplicates in column D, there are distinct values in other columns, so the access program does not which record to delete or keep.
Does that make sense?

Thanks!

[JoeM]

You need to set up the table this way BEFORE putting any data in it.
If you already have data in it, delete it all, then set the 4th column to be your primary key field.

Alternatively, you could import the data the first time to initially set up the table, delete all the table, make column 4 the primary key field, then re-import the data.
Note that you will get a warning message that all records will not be imported because of a Primary Key violation. That is precisely what you want to happen.
It will import all unique records, and drop all duplicates.

If this error message is throwing you or you are concerned it may confuse users, you can suppress it by adding the following line to your VBA code before the import step:

DoCmd.SetWarnings False

Just be sure to turn it back on after the Import line like this:

DoCmd.SetWarnings True

[CJ_London]

although there are duplicates in column D, there are distinct values in other columns,

so how are you deciding which is the correct record?

[ssanfu]

Whaaatttt????? I'm confused.....again.

Post #3

Actually, column D in my CSV file has to be unique

Post #5

I am falling into problem when I set my column D to be the primary key field.
It doesn't work because although there are duplicates in column D, there are distinct values in other columns

A primary key field MUST be unique! If column D has duplicates, it CANNOT be the primary key.



See Microsoft Access Tables: Primary Key Tips and Techniques