Import fields from .csv with variable column structure

  • Thread starter Thread starter Snowspud
  • Start date Start date
S

Snowspud

I would like some help please, currently I'm importing a collection of .csv
files into Access using VBA TransferText, I import each file spearately via a
form where I include the file name from a textbox, but the number and names
of the fields in the .csv files are not consistent so I can't use a Import
Spec. How can I import the different files while making sure that all fields
are imported as text fields? There is a row including the field headings.

Thanks
Richard
 
I would like some help please, currently I'm importing a collection of .csv
files into Access using VBA TransferText, I import each file spearately via a
form where I include the file name from a textbox, but the number and names
of the fields in the .csv files are not consistent so I can't use a Import
Spec. How can I import the different files while making sure that all fields
are imported as text fields? There is a row including the field headings.

Thanks
Richard
Click to expand...

If there is a row that contains column headings, then there's your
answer. Set the First Row Contains Column Headings or whatever that
part is to True. Build and Save the import specification, and then
use it when you do the transfertext. Just name the thing, and you're
good to go.
 
Thanks,

The problem is that the files being imported are inconsistent in terms of
number of columns and column headings, so I don't think I can build an import
specification as there is literally nothing consistent on which to build the
specification except that I want the format of each field to be text. I have
tried adding a row of text only values to the beginning of the file but it
seems to ignore that. Is it possible to build an import specification where
the field names and number of fields are variable from one file to the next?
 
Thanks,

The problem is that the files being imported are inconsistent in terms of
number of columns and column headings, so I don't think I can build an import
specification as there is literally nothing consistent on which to build the
specification except that I want the format of each field to be text. I have
tried adding a row of text only values to the beginning of the file but it
seems to ignore that. Is it possible to build an import specification where
the field names and number of fields are variable from one file to the next?
 
I've had this problem a few times. You can import without a specification,
but it often defaults the fields to numeric or date when you really want
text, and then it kicks out the "import errors" when the field isn't really a
numeric/date.

Here's two ideas, neither of which are particularly elegant:

1. Create a generic import table with more fields than you would ever
possibly need. Label each field "Field 1","Field 2", etc. or something
equally generic, and make them all text fields. Then import the new table
into the generic import table. The first row will be the field names. Create
a recordset to find the "field names" that are in this first row, and use SQL
DDL* to create fields in another table with those field names. Then insert
the rest of the data into the new table. (There are dozens of variations on
this idea, such as adding the new fields to the same generic import table;
use what works for you.)

2. Open and read the datafile directly through code, and use SQL DDL to
create a table and import the data.

* SQL DDL is the data definition language, e.g. "ALTER TABLE Customers ADD
COLUMN Customer_ID Text(255)"
 
Back
Top