Importing Text Files Error

[Joe]

i am using the TransferText method to import a text file into a table.
when importing the data, one field is converted into currentcy, even
though it is declared as a string.

' Declare Variables.
Dim stStudentID, stImport, stTable As String
Dim stMAAddr1, stMAAddr2, stMAAddr3, stMACity, stMAState,
stMAPostCode, stMACountry As String

' Dump All Records to Temorary Database.
stImport = stPath & "IMPORT\address_file.txt"
stTable = "tmp_ADDRESS"

DoCmd.TransferText , , stTable, stImport, 1


Here is an example of the text file. The first field is converted into
currency, or an error is thrown because of a type conversion. currently
there are no text qualifiers for the fields.

STAFF_ID,MAaddr1,MAaddr2,MAaddr3,MAcity,MAstate,MApostcode,MAcounty
961132B,123 Main Street,,,WASHINGTON,VIC,3000,
843040B,229 Baker Street,,,OHIO,VIC,3095,
S001900,111 Minor Street,,,FLORIDA,,3200,



joe.

 

[John Nurick]

Hi Joe,

Those variable declarations have no effect on the way the text file is
imported. One way to fix this would be to create the table before
importing, and define the first field as a text field. Another is to use
an import specification (to create one, import the file once manually,
and click the Advanced... button in the text import wizard, then define
field names and types and save the resulting specification).

 

[Joe]

hi john, thanks for the info.

how do i then use the specification when using the transfertext method ?

joe

 

[Alankar]

Joe,

When exporting out text files if they are not fixed with you can set
up a specification and this allows you to say what the field
delimiters are and what the text qualifiers are, and you can set both
to none, also you can set the datatype of the fields.

To set up a specification, manually export the table, and you will
then get some options on wether its fixed with or delimited, and as
you go through you will see and advanced button which allows you to
set up a specification, save that specifiation and then insert that
specification name in your transfertext command.

 

[John Nurick]

Hi Joe,

TransferText accepts the name of an import/export specification as one
of its arguments.

 

[Joe]

thanks john. i pre-empted. thanks very much. you made importing look
easy !

joe