Link to home
Start Free TrialLog in
Avatar of printmedia
printmedia

asked on

Field 'F1' doesn't exist in destination table

Hi all.

I keep getting this error when I try to run a macro that imports data into a table called Prices from a csv file called Prices.csv


This macro has been working for the past 2 years and today it failed so I tried to "Run" it myself and it gives this error:

Field 'F1' doesn't exist in destination table 'Prices'

My csv file does not have any headers and the macro has the "Has Field Names" set to no.

Thank you in advance.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

If you import data from a csv without that csv file having field names on top, Access will assume that the field names are F1, F2, F3, F4, etc.

If you import your data into a new table, this is no big deal, as Access will create them for you.

If you import your data into an existing table, and it does not have fields named F1, F2, F3, then the error is correct.
Try building a specification and specify fields in your table

To build an import specification:

1. Go to File/Get External Data/Import, select filetype .txt
2. Click the, "Advanced...," button to open the specification;
3. Edit the specification as desired; and
4. Save.

Pass the specification name to TransferText to have Access use your saved definition

DoCmd.TransferText acExportDelim, "SpeciName", "tableName", "Filename", "Hasfieldnames"
Avatar of printmedia
printmedia

ASKER

The csv file has no field names, but the table does have field names, but I noticed that my csv file only has 5 columns while the table in access has an extra column called upsize_ts, which is empty. Could this be the problem?
If you import a csv file into a table without headers and "Has Field Names" is set to No, don't all the fields get named F1, F2, F3,...Fn?  Does a table get created and does some other part of the macro try to operate on field F1?  Has the format of the csv file changed?  Obviously, something has.
The macro first deletes the data in the Prices table then it imports the csv file. The table already has the 5 field names. So it tried to run but stopped, so the table is now empty because it did the first part of deleting the old data.

The file has not changed.
The first Action of the macro is RUNSQL which deletes the data in the Prices table.

The second Action is TransferText and the Transfer Type is Import Delimited, I specify the Table Name as Prices and the File Name is the path where the csv file resides and finally "Has Field Names" is set to No.
ASKER CERTIFIED SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
follow Rocki's first entry.
Rename the old table

Go to file/get external data/Import

go find your .csv file

when the wizard opens, set field names to no

click on the advanced button

when that opens you can name your fields whatever you want

finish that up and SAVE it. Remember the name, that's your new Import Spec

save to a new table (your old table name)

and it creates the new table and imports the data.

Voila!! No More F1, F2, etc...


Thanks rockiroads it worked great!
No probs

Its painstaking to create it first time, but easy thereafter