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.
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.
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"
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"
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.
ASKER
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 file has not changed.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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...
ASKER
Thanks rockiroads it worked great!
No probs
Its painstaking to create it first time, but easy thereafter
Its painstaking to create it first time, but easy thereafter
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.