Import text file using schema.ini

[RD]

Hi all,

Anyone know how to do this? The article I found on the MSDN site was woefully
inadequate. I import large text files that are tilde delimited and lack column
names. In addition to specifying the delimiter, I'd like to assign the column
names, the data type and, especially, the width. These tables have gobs of
single character switches (Y/N) that, when imported, Access assigns a field size
of 255. Yikes! Importing a single file bloats Access to nearly 60 MB.

Any help?

Thanks,
RD

 

[John Nurick]

Basically:

1) Schema.ini must be in the same folder as the file you are importing
2) It must have a [section] whose name matches your filename, with a
line for each field.

These are the most useful articles I've found:

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


If you are going to normalise the data once you've imported it, it might
be worth taking a look at my txtnrm.pl, which converts wide text files
into tall narrow ones that can easily be imported.
http://www.j.nurick.dial.pipex.com/Code/Perl/index.htm

 

[RD]

Thanks, John!

Ok! Now I'm gettin' somewhere! That first link confirmed that I'd built my ini
file correctly. The second link showed how to use it more clearly than the page
I found. And the third one is what guided me in building the ini file,
yesterday.

Worked like a charm on the first go. If I understand what just happened, I now
have a recordset but haven't actually created a table yet. The rs is actually
based on the text file, right? This has me rethinking my strategy. It will be
pretty slick if I can just pull the records I need from the text file instead of
importing the whole thing and then querying a huge table.

I don't really normalize the data, I just query it. These text files are data
dumps from a huge gov't. database. They're intended as source files for canned
reports but we find them pretty handy for ad hoc reporting, pre-defining certain
populations without having to query the database itself.

I took a look at your Perl scripts. I'd heard Perl was a primo tool for text
manipulation but it all looks like Greek to me. Do you know of any good
learning material for a Perl "dummy"?

Thanks,
RD

Basically:

1) Schema.ini must be in the same folder as the file you are importing
2) It must have a [section] whose name matches your filename, with a
line for each field.

These are the most useful articles I've found:

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


If you are going to normalise the data once you've imported it, it might
be worth taking a look at my txtnrm.pl, which converts wide text files
into tall narrow ones that can easily be imported.
http://www.j.nurick.dial.pipex.com/Code/Perl/index.htm

Hi all,

Anyone know how to do this? The article I found on the MSDN site was woefully
inadequate. I import large text files that are tilde delimited and lack column
names. In addition to specifying the delimiter, I'd like to assign the column
names, the data type and, especially, the width. These tables have gobs of
single character switches (Y/N) that, when imported, Access assigns a field size
of 255. Yikes! Importing a single file bloats Access to nearly 60 MB.

Any help?

Thanks,
RD

 

[RD]

Heh, actually, I was having a hard time working with the rs so I went ahead and
created a table linked to the text file. This really slims down the database.

Thanks, John!

Ok! Now I'm gettin' somewhere! That first link confirmed that I'd built my ini
file correctly. The second link showed how to use it more clearly than the page
I found. And the third one is what guided me in building the ini file,
yesterday.

Worked like a charm on the first go. If I understand what just happened, I now
have a recordset but haven't actually created a table yet. The rs is actually
based on the text file, right? This has me rethinking my strategy. It will be
pretty slick if I can just pull the records I need from the text file instead of
importing the whole thing and then querying a huge table.

I don't really normalize the data, I just query it. These text files are data
dumps from a huge gov't. database. They're intended as source files for canned
reports but we find them pretty handy for ad hoc reporting, pre-defining certain
populations without having to query the database itself.

I took a look at your Perl scripts. I'd heard Perl was a primo tool for text
manipulation but it all looks like Greek to me. Do you know of any good
learning material for a Perl "dummy"?

Thanks,
RD

Basically:

1) Schema.ini must be in the same folder as the file you are importing
2) It must have a [section] whose name matches your filename, with a
line for each field.

These are the most useful articles I've found:

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


If you are going to normalise the data once you've imported it, it might
be worth taking a look at my txtnrm.pl, which converts wide text files
into tall narrow ones that can easily be imported.
http://www.j.nurick.dial.pipex.com/Code/Perl/index.htm

Hi all,

Anyone know how to do this? The article I found on the MSDN site was woefully
inadequate. I import large text files that are tilde delimited and lack column
names. In addition to specifying the delimiter, I'd like to assign the column
names, the data type and, especially, the width. These tables have gobs of
single character switches (Y/N) that, when imported, Access assigns a field size
of 255. Yikes! Importing a single file bloats Access to nearly 60 MB.

Any help?

Thanks,
RD

 

[John Nurick]

Glad it's working.

Linking to a big text file usually works fine. One advantage of
importing is that you can create indexes on the fields you're querying
on; this can make queries run much faster.

Many people think Perl code looks like line noise on a modem. I've been
using it so long that I've almost forgotten how I learnt it, but I
remember a lot of bafflement in the early days. The documentation that
comes with ActivePerl is fine if you know all about programming in C and
Unix shells, but pretty unfriendly for a newcomer from Windows. All I
can suggest is that you browse the computer shelf in a good bookshop
until you find a beginner's guide that you like and that doesn't assume
you're running Unix or Linux. Supplement that with the *excellent* Perl
Pocket Reference by Johan Vromans (published by O'Reilly).

Heh, actually, I was having a hard time working with the rs so I went ahead and
created a table linked to the text file. This really slims down the database.

Thanks, John!

Ok! Now I'm gettin' somewhere! That first link confirmed that I'd built my ini
file correctly. The second link showed how to use it more clearly than the page
I found. And the third one is what guided me in building the ini file,
yesterday.

Worked like a charm on the first go. If I understand what just happened, I now
have a recordset but haven't actually created a table yet. The rs is actually
based on the text file, right? This has me rethinking my strategy. It will be
pretty slick if I can just pull the records I need from the text file instead of
importing the whole thing and then querying a huge table.

I don't really normalize the data, I just query it. These text files are data
dumps from a huge gov't. database. They're intended as source files for canned
reports but we find them pretty handy for ad hoc reporting, pre-defining certain
populations without having to query the database itself.

I took a look at your Perl scripts. I'd heard Perl was a primo tool for text
manipulation but it all looks like Greek to me. Do you know of any good
learning material for a Perl "dummy"?

Thanks,
RD

Basically:

1) Schema.ini must be in the same folder as the file you are importing
2) It must have a [section] whose name matches your filename, with a
line for each field.

These are the most useful articles I've found:

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


If you are going to normalise the data once you've imported it, it might
be worth taking a look at my txtnrm.pl, which converts wide text files
into tall narrow ones that can easily be imported.
http://www.j.nurick.dial.pipex.com/Code/Perl/index.htm



Hi all,

Anyone know how to do this? The article I found on the MSDN site was woefully
inadequate. I import large text files that are tilde delimited and lack column
names. In addition to specifying the delimiter, I'd like to assign the column
names, the data type and, especially, the width. These tables have gobs of
single character switches (Y/N) that, when imported, Access assigns a field size
of 255. Yikes! Importing a single file bloats Access to nearly 60 MB.

Any help?

Thanks,
RD

 

[RD]

Thanks. I'll look into it.

Glad it's working.

Linking to a big text file usually works fine. One advantage of
importing is that you can create indexes on the fields you're querying
on; this can make queries run much faster.

Many people think Perl code looks like line noise on a modem. I've been
using it so long that I've almost forgotten how I learnt it, but I
remember a lot of bafflement in the early days. The documentation that
comes with ActivePerl is fine if you know all about programming in C and
Unix shells, but pretty unfriendly for a newcomer from Windows. All I
can suggest is that you browse the computer shelf in a good bookshop
until you find a beginner's guide that you like and that doesn't assume
you're running Unix or Linux. Supplement that with the *excellent* Perl
Pocket Reference by Johan Vromans (published by O'Reilly).

<snip>

 

[macanucaire]

I have found many silly and frustrating things with Access over the years but my recent experience with schema.ini has to be the worst. I created a query and used the macro command Transfer Text and Export to Windows Merge and it worked fine. However I then modified the query and it no longer worked. It gave different errors when I ran the macro form a button or when I ran it directly. Grrrrrrrrrr......
Eventually I can across this schema.ini business. I had to delete this file for it to work again. It is the only place I've found where Access uses a fiel outside the mdb file to store a specification and is not noted within Access at all.
MS you should do better.