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

AC <andrewfreestuff@gmail.com> wrote in
news:1193346793.376073.255380@i38g2000prf.googlegroups.com: 

> 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.




-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

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.
-- 
Dave Hargis, Microsoft Access MVP


"AC" wrote:

> 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
> 
> 

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


On Thu, 25 Oct 2007 14:13:13 -0700, AC <andrewfreestuff@gmail.com>
wrote:

>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
--
John Nurick - Access MVP

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.

"AC" wrote:

> 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" <rquintal@sPAmpatico.ca> skrev i melding 
news:Xns99D4BA342278DBQuintal@66.150.105.47...
> AC <andrewfreestuff@gmail.com> wrote in
> news:1193346793.376073.255380@i38g2000prf.googlegroups.com:
>
>> 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.
>
>
>
>
> -- 
> Bob Quintal
>
> PA is y I've altered my email address.
>
> -- 
> Posted via a free Usenet account from http://www.teranews.com
>