Import using schema.ini file

[RD]

In an import spec you can tell Access to skip a field/column. Can you
do the same in a schema file?

I'm helping a colleague import survey results and had to manually
create a schema file; 227 columns with some very bad column names. We
have no control over how we get the data (other than choosing xls or
csv). The last column isn't really a column at all, just a
non-printing character in the column names row but, Access says it's a
column and wants to import it. I'd like to be able to tell Access to
skip it.

Also, since some of the columns will not be used for reporting
purposes, it would be handy to skip various columns in the file.

Possible?

TIA,
RD

 

[RD]

In addition to the question below, I've another:

In an import spec you can also define a Text Qualifier. The default is
a double quote: ". I have a bunch of Memo fields in these files
containing commas, single quotes and double quotes. Fortunately, the
Memo fields (and only the Memo fields) have double quote text
qualifiers. Can you define a text qualifier in a schema.ini file?

It's starting to look like I'm going to need to dump the idea of a
schema file and just work with import/export specs.

What a pain. Lesson learned. When a colleague approaches with a
"simple" request ... run. Run away.

Thanks for any help on either of these questions,
RD

 

[Stuart McCall]

Can you define a text qualifier in a schema.ini file?

Yes. Create an entry:

TextDelimiter="

It's starting to look like I'm going to need to dump the idea of a
schema file and just work with import/export specs.

What a pain. Lesson learned. When a colleague approaches with a
"simple" request ... run. Run away.

There are 6 words I hate to hear: "while you're here can you just"

 

[david]

Start by exporting a table. That creates a schema.ini file for you.

The import spec includes the skipped columns.
schema.ini needs to do the same.

Once you have the schema, you can import using an import
query. The import query only has to list the names of the fields
which you wish to import. Only the columns you request will
be imported, but you first need to define the file: that is what
schema.ini is for.

If this is a very simple import, you can import directly to the
target table.

If this is normally a simple import, you can import to a text
table first, then fix up the data, then copy it across to the
target table.

If this is generally something you can't depend on, at all, you
are better off using straight VBA to read the file into a table.
This allows you to better handle lines which are not normal
data lines, but are mixed in with the normal data.

(david)

 

[RD]

Thanks for the reply and the tip.

I'm getting a "Error 3011: The Microsoft Jet database engine could not
find the object ''." error, now. Not sure what's wrong. I've never had
a problem with this sort of thing in the past.

I'm also trying to actually build an import spec but I think it's too
big. It keeps hanging after I click OK or Save As.

This is just turning into a nightmare. I may end up doing this through
file I/O.

This is the first of four datasets and I believe the largest
(structure-wise). The others should be easier. However, I've already
been told that all of them are subject to a few changes with each
iteration.

Ugh. Shoot me now.

 

[RD]

Thanks for the reply David but, I don't have a table to export. I'm
trying to import CSV files with really rotten column names. Unless
you're suggesting I build a table with better column names and proper
data types. I guess that would probably be easier than working with
the spec wizard in terms of usability. Still time consuming, though.
Hm ... I'll have to consider that. Might be the way to go.

I finally got a list of the columns needed for reporting purposes so I
at least know which columns I can have Access skip. It's still a big
task (one I wish I hadn't agreed to).

 

[david]

Yes, if you have a complex import, I suggest that you build a
better table with proper names and types, but not to use as
the import target - I'd be importing into an intermediate table.

For the intermediate table, I'd either go with the source
column names or the target column names depending on
which is less work and how much transformation is required.

Remember that the target column names don't have to be
the same as the source or intermediate column names. Once
you have the target table and the schema.ini (or VBA or
import schema), you can tell it to ignore the column names.

If it is a large import, you don't need to put the intermediate
table into the target database. You can use (another) linked
database for temp tables. It's slower than using a local table,
but avoids the bloating problem.

(david)