Importing Delimited files

[Shaun]

Hi,

I'm trying to import several files at once using some VB. I can get the CSV
files fine and understand the basics of the TransferText method. However,
when my code gets to that line (transfertext), it says
"Field F1 doesn't exist in destination table, 'tablename'" (tablename is
one of several tables corresponding to the several CSVs). I'm importing to
preexisting tables so shouldn't it use those field names? Both the import
and destination fields have the same format and data types. Thanks in
advance.

 

['69 Camaro]

Hi, Shaun.

However,
when my code gets to that line (transfertext), it says
"Field F1 doesn't exist in destination table, 'tablename'"

Jet will assign a default field name of the letter F followed by digits
whenever a column name in the source violates the rules for column names
within Access. For example, two columns of the same name will result in the
second one being named F1 upon import. A column name beginning with a space
character will be renamed as well.

To troubleshoot which column is causing the problem, attempt to import the
file manually with the Import Text Wizard. When you come to the dialog
window displaying the names of each column to be imported, you'll notice that
it is labeled Field1 instead. Look at that column name in the CSV file and
change it to a legal name, then attempt to import again.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Shaun]

There are no field names in the CSV file, that's the whole point of importing
to an existing table.

Also, if I import it manually, it works fine (no need to manually change
field names). There are no problems with the field names then and they match
up perfectly.

 

['69 Camaro]

Hi, Shaun.

There are no field names in the CSV file

If the columns in the file have no headers, then you'll need to tell Jet
which columns to map to. One way to do this is to create an import
specification manually when you import the text file the first time, then use
this import specification on subsequent use of the TransferText method. You
can also use a schema.ini file to format the imported fields if your records
are of fixed length. A third option is to use an append query which names
the fields in the order that they appear in the file. For example:

INSERT INTO tblTextImport
SELECT F1 AS FirstName, F2 AS LastName, F3 AS Address
FROM [TEXT;HDR=NO;DATABASE=C:\Test\].TextImportNoHdr.csv;

.. . . where tblTextImport is the name of the table in the database,
FirstName is the first column in the file, LastName is the second column in
the file, Address is the third column in the file, and
C:\Test\TextImportNoHdr.csv is the path and file name of the file to be
imported.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Shaun]

Yeah kinda thought I'd have to do the import specs, was hoping there was an
automatic way of doing it. How does the Import menu command get around this
pain?

Hi, Shaun.

There are no field names in the CSV file

If the columns in the file have no headers, then you'll need to tell Jet
which columns to map to. One way to do this is to create an import
specification manually when you import the text file the first time, then use
this import specification on subsequent use of the TransferText method. You
can also use a schema.ini file to format the imported fields if your records
are of fixed length. A third option is to use an append query which names
the fields in the order that they appear in the file. For example:

INSERT INTO tblTextImport
SELECT F1 AS FirstName, F2 AS LastName, F3 AS Address
FROM [TEXT;HDR=NO;DATABASE=C:\Test\].TextImportNoHdr.csv;

. . . where tblTextImport is the name of the table in the database,
FirstName is the first column in the file, LastName is the second column in
the file, Address is the third column in the file, and
C:\Test\TextImportNoHdr.csv is the path and file name of the file to be
imported.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

There are no field names in the CSV file, that's the whole point of importing
to an existing table.

Also, if I import it manually, it works fine (no need to manually change
field names). There are no problems with the field names then and they match
up perfectly.

 

['69 Camaro]

Hi, Shaun.

How does the Import menu command get around this
pain?

It sets defaults according to the ordinal number of the column. As long as
the file's column data types are in the same order as the columns defined
for the table, it's a one-for-one match for the appended records. For
example, if the first column is text and the second column is a date in the
file to be imported, and the table's first column is the Text data type and
the second column is the Date/Time data type, then the imported file doesn't
require headers, and the records will append just fine. However, if one
doesn't use the Wizard, then one must supply the mapping of which column in
the file matches to which column in the table.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Yeah kinda thought I'd have to do the import specs, was hoping there was
an
automatic way of doing it. How does the Import menu command get around
this
pain?

Hi, Shaun.

There are no field names in the CSV file

If the columns in the file have no headers, then you'll need to tell Jet
which columns to map to. One way to do this is to create an import
specification manually when you import the text file the first time, then
use
this import specification on subsequent use of the TransferText method.
You
can also use a schema.ini file to format the imported fields if your
records
are of fixed length. A third option is to use an append query which
names
the fields in the order that they appear in the file. For example:

INSERT INTO tblTextImport
SELECT F1 AS FirstName, F2 AS LastName, F3 AS Address
FROM [TEXT;HDR=NO;DATABASE=C:\Test\].TextImportNoHdr.csv;

. . . where tblTextImport is the name of the table in the database,
FirstName is the first column in the file, LastName is the second column
in
the file, Address is the third column in the file, and
C:\Test\TextImportNoHdr.csv is the path and file name of the file to be
imported.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

There are no field names in the CSV file, that's the whole point of
importing
to an existing table.

Also, if I import it manually, it works fine (no need to manually
change
field names). There are no problems with the field names then and they
match
up perfectly.

:

Hi, Shaun.

However,
when my code gets to that line (transfertext), it says
"Field F1 doesn't exist in destination table, 'tablename'"

Jet will assign a default field name of the letter F followed by
digits
whenever a column name in the source violates the rules for column
names
within Access. For example, two columns of the same name will result
in the
second one being named F1 upon import. A column name beginning with
a space
character will be renamed as well.

To troubleshoot which column is causing the problem, attempt to
import the
file manually with the Import Text Wizard. When you come to the
dialog
window displaying the names of each column to be imported, you'll
notice that
it is labeled Field1 instead. Look at that column name in the CSV
file and
change it to a legal name, then attempt to import again.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact info.


:

Hi,

I'm trying to import several files at once using some VB. I can
get the CSV
files fine and understand the basics of the TransferText method.
However,
when my code gets to that line (transfertext), it says
"Field F1 doesn't exist in destination table, 'tablename'"
(tablename is
one of several tables corresponding to the several CSVs). I'm
importing to
preexisting tables so shouldn't it use those field names? Both the
import
and destination fields have the same format and data types. Thanks
in
advance.