Writing Custom Import Filters

[Jürgen Germonpré]

Hi all,

I wondered if there is a method to write my own custom import filters... On
the same principle like you would
File > Get External Data > Import, and then select the type of file to
import (this will be mostly TXT (delimited or fixed length or Excel).
In case of text file you get the advanced feature, where you can manualy
change code page, fieldnames and data types. That is okay, but in Excel I
always get those "auto-chosen" datatypes. That is, if the filter detects a
numerical value in the first two records then it assumes that all values are
numerical, which in my case isn't true. So I want to create some kind of
filter where I can overrule that featurer and thus have some fields to be
treatenen as plain text, or if I want to, have it imported with another
codepage (dos/unicode or whatever) Further more I would like to create a
kind of template of a mapping which I can reuse afterwards (like when you
click the save specs button in the advanced window)

Is there an easy way by which I could use some functionality of the existing
filters instead of reprogramming from scratch (and that for every type of
file (tex/excel etc...)

Any clues, tips, even examples would be more than welcome.

Thanks.

JG

 

[John Nurick]

Hi Jürgen,

A few thoughts, in no particular order.

You can get quite a long way by writing SQL queries that link to the
text or Excel files. The SQL is the same as when working with ordinary
Access tables, so you can alias field names and use calculated fields to
map data types or make other adjustments. For the FROM clause, can use
syntax like this:

Excel sheet, field names in first row:
FROM [Excel 8.0;HDR=Yes;Database=C:\File.xls;].[Sheet1$]
Range of cells on sheet, no field names (uses F1, F2, F3...)
FROM [Excel 8.0;HDR=No;Database=C:\File.xls;].[Sheet1$E4:K999]
Named range:
FROM [Excel 8.0;HDR=No;database=C:\File.xls;].[MyRange]
Text file, no field names:
FROM [Text;HDR=No;Database=C:\MyFolder\;].MyFile#txt

There are other parameters that can be set, but I can never find the
documentation when I want it.

When importing text files under program control, it's often simpler to
put the specification in a schema.ini file than to use an import/export
specification. Schema.ini is documented in the Help topic "Initializing
the Text Data Source Driver".

There are some registry settings that affect how Excel data is linked or
imported. See
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
and http://support.microsoft.com/?id=257819

You may find this useful too:
How To Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/?id=257819

When it comes to text files, I often preprocess them using Perl or
VBScript to get them into a form that the standard Access import filters
can handle. There are a couple of examples on my web site, e.g.
http://www.j.nurick.dial.pipex.com/Code/VBS/TallToWide.vbs

Finally, there's been a lot of good stuff in
microsoft.public.access.externaldata over the years.

 

[Jürgen Germonpré]

Dear John,

This is very useful !

Thanks a lot !

JG

Hi Jürgen,

A few thoughts, in no particular order.

You can get quite a long way by writing SQL queries that link to the
text or Excel files. The SQL is the same as when working with ordinary
Access tables, so you can alias field names and use calculated fields to
map data types or make other adjustments. For the FROM clause, can use
syntax like this:

Excel sheet, field names in first row:
FROM [Excel 8.0;HDR=Yes;Database=C:\File.xls;].[Sheet1$]
Range of cells on sheet, no field names (uses F1, F2, F3...)
FROM [Excel 8.0;HDR=No;Database=C:\File.xls;].[Sheet1$E4:K999]
Named range:
FROM [Excel 8.0;HDR=No;database=C:\File.xls;].[MyRange]
Text file, no field names:
FROM [Text;HDR=No;Database=C:\MyFolder\;].MyFile#txt

There are other parameters that can be set, but I can never find the
documentation when I want it.

When importing text files under program control, it's often simpler to
put the specification in a schema.ini file than to use an import/export
specification. Schema.ini is documented in the Help topic "Initializing
the Text Data Source Driver".

There are some registry settings that affect how Excel data is linked or
imported. See
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
and http://support.microsoft.com/?id=257819

You may find this useful too:
How To Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/?id=257819

When it comes to text files, I often preprocess them using Perl or
VBScript to get them into a form that the standard Access import filters
can handle. There are a couple of examples on my web site, e.g.
http://www.j.nurick.dial.pipex.com/Code/VBS/TallToWide.vbs

Finally, there's been a lot of good stuff in
microsoft.public.access.externaldata over the years.

Hi all,

I wondered if there is a method to write my own custom import filters...
On
the same principle like you would
File > Get External Data > Import, and then select the type of file to
import (this will be mostly TXT (delimited or fixed length or Excel).
In case of text file you get the advanced feature, where you can manualy
change code page, fieldnames and data types. That is okay, but in Excel I
always get those "auto-chosen" datatypes. That is, if the filter detects a
numerical value in the first two records then it assumes that all values
are
numerical, which in my case isn't true. So I want to create some kind of
filter where I can overrule that featurer and thus have some fields to be
treatenen as plain text, or if I want to, have it imported with another
codepage (dos/unicode or whatever) Further more I would like to create a
kind of template of a mapping which I can reuse afterwards (like when you
click the save specs button in the advanced window)

Is there an easy way by which I could use some functionality of the
existing
filters instead of reprogramming from scratch (and that for every type of
file (tex/excel etc...)

Any clues, tips, even examples would be more than welcome.

Thanks.

JG