Transfertext import from csv- want text fields but saves as number field so lose data.

[AC]

Hi

I am importing some csv data into Access.

the data is a mixture of text and numerical, ie each field may contain
some text data or some numerical data.

If i import using the wizard it correctly imports into a new table
with each field text.

However i need to automate this so i have some vba to import the
data. When using DoCmd.Transfer text it imports into a new table but
sets each field as a number type, so I lose the text data (i also get
a myData_ImportErrors table created).

Maybe use a spec? .... BUT....
Here is the rub:
I dont think I can create and use a spec because the size of this
file, ie the number of fields, is not static, it may be different
every time.
I have tried a spec and then changed the file size and it just drops
the new/additional fields which were not present when making the spec.
(eg make a spec with 10 fields, then when import a 12 field data file
it just drops the 2 additional fields).

Ideas?
Can i create and use a simple spec on the fly which just says "make
all fields strings"?
if so, can someone point me to how to do it.

i could pre-parse the file to figure out how many columns are in it,
is it easy to make a spec with that additional info?

I am sort of hoping to avoid having to make a table on the fly, ie
just use TransferText to do all the work for me.

Thanks in advance
A

 

[Bob Quintal]

Hi

I am importing some csv data into Access.

the data is a mixture of text and numerical, ie each field may
contain some text data or some numerical data.

If i import using the wizard it correctly imports into a new table
with each field text.

However i need to automate this so i have some vba to import the
data. When using DoCmd.Transfer text it imports into a new table
but sets each field as a number type, so I lose the text data (i
also get a myData_ImportErrors table created).

Maybe use a spec? .... BUT....
Here is the rub:
I dont think I can create and use a spec because the size of this
file, ie the number of fields, is not static, it may be different
every time.
I have tried a spec and then changed the file size and it just
drops the new/additional fields which were not present when making
the spec. (eg make a spec with 10 fields, then when import a 12
field data file it just drops the 2 additional fields).

Ideas?
Can i create and use a simple spec on the fly which just says
"make all fields strings"?
if so, can someone point me to how to do it.

i could pre-parse the file to figure out how many columns are in
it, is it easy to make a spec with that additional info?

I am sort of hoping to avoid having to make a table on the fly, ie
just use TransferText to do all the work for me.

Thanks in advance
A

one thing you can do is write some code that imports the entire row
as a single column, then parse out the rows using mid() function
calls.

 

[Klatuu]

It is not correct to post a question to multiple groups.
But, to answer the question, you cannot create a specification that will
allow for different numbers of columns. What you are doing sound very odd.
It would not work well at all in a relational database. You may actually be
better off using Excel for this. It is much more flexible in this regard.

 

[John Nurick]

Hi A,

1) Can you tweak the software that creates the CSV file so that all
the field values are enclosed in quotes? That way, the Access/Jet
import routine will recognise them as text even if they contain
numbers and therefore import them into text fields, and you won't need
to use an import specification.

2) Or can you ensure that the first record in every CSV file contains
only text values? This will make the import routine treate them as
text fields. Maybe you could add a dummy record, then delete it from
the tables after import.

3) Otherwise you can write VBA code that parses the CSV file and
creates an import specification in the form of a schema.ini file.
There's some documentation here:
Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
Schema.ini File (Text File Driver)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

 

[Lance]

Make your import spec with the maximum number of fields you'll ever have, if
any given import file has less fields Access will fill out the extra fields
as null.

 

[hassan]

one thing you can do is write some code that imports the entire row
as a single column, then parse out the rows using mid() function
calls.