Importing a Text file with variable length 1st field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have taken over managing a database where an import is run to bring data
from a .txt file, the import had worked fine when run for the first time
before i took over.

I have now run the import for the first time and the first field is shorter
so it has cut off the first 2 digits of the next field.

Looking at the import Macro the TransferText function has a specification
name but I can't seem to locate or access the specification.

If anyone could help with 2 points
1) where or how does access store the specification's or how do i access them
2) what is the best way to allow for the variable first field length would
it be possible to have an input box to specify the field length when running
the import and what would be the best way of doing this?
 
1) where or how does access store the specification's or how do i access them
Click to expand...
Access stores Import / Export specs in two system tables, MSysIMEXSpecs
and MSysIMEXColumns. You can display the tables if you go Tools >
Options, tab View, and check to see Hidden Objects, but you cannot
change them.

2) what is the best way to allow for the variable first field length
Click to expand...
Nothing you can do on a fixed width text file, unless change the import
spec manually. The only way to do this is to start the import manually,
so the wizard takes you to the specification and lets you change and
save it. The sad part is this is just as good as doing a manual import
every time!

would it be possible to have an input box to specify the field length
when running the import
Click to expand...
No.

In my experience, a varying field width suggests a more or less manual
export of the text file. The downside is what you get (inconsistencies),
the upside is there's a good chance you can get whoever does it to
export to some sort of a delimited file instead (tab, comma, semicolon)
which will solve your import problem.

HTH,
Nikos
 
2) what is the best way to allow for the variable first field length
I had a problem like this recently. I needed automatic import/connection to
fixed with text files, 5-15 columns of variable width.
I ended up with a working sequence that
1) read the first line of the text file and extracted column names and
width.
2) Used that info write my own schema.ini textfile like this
http://support.microsoft.com/kb/q155512/
3) Then ssed the schema.ini file to import or connect to the text file like
this
http://support.microsoft.com/kb/q149090/

I'm not sure if this applies to your task, but I think it can be adapted.
While it's running it should be able to present you with an inputbox asking
for the width of the first column or that info could be extracted
automatically from the file.


Jesper, Denmark
 
Back
Top