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

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...b;EN-US;155512

How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default...b;EN-US;149090
Schema.ini File (Text File Driver)
http://msdn.microsoft.com/library/de...a_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


On Tue, 12 Jun 2007 16:26:19 -0700, RD <(E-Mail Removed)> wrote:

>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 [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

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


On Wed, 13 Jun 2007 07:24:51 +0100, John Nurick <(E-Mail Removed)>
wrote:

>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...b;EN-US;155512
>
>How to Use Schema.ini for Accessing Text Data
>http://support.microsoft.com/default...b;EN-US;149090
>Schema.ini File (Text File Driver)
>http://msdn.microsoft.com/library/de...a_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
>
>
>On Tue, 12 Jun 2007 16:26:19 -0700, RD <(E-Mail Removed)> wrote:
>
>>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

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.


On Wed, 13 Jun 2007 09:52:12 -0700, RD <(E-Mail Removed)> wrote:

>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
>
>
>On Wed, 13 Jun 2007 07:24:51 +0100, John Nurick <(E-Mail Removed)>
>wrote:
>
>>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...b;EN-US;155512
>>
>>How to Use Schema.ini for Accessing Text Data
>>http://support.microsoft.com/default...b;EN-US;149090
>>Schema.ini File (Text File Driver)
>>http://msdn.microsoft.com/library/de...a_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
>>
>>
>>On Tue, 12 Jun 2007 16:26:19 -0700, RD <(E-Mail Removed)> wrote:
>>
>>>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

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).

On Wed, 13 Jun 2007 11:59:26 -0700, RD <(E-Mail Removed)> wrote:

>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.
>
>
>On Wed, 13 Jun 2007 09:52:12 -0700, RD <(E-Mail Removed)> wrote:
>
>>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
>>
>>
>>On Wed, 13 Jun 2007 07:24:51 +0100, John Nurick <(E-Mail Removed)>
>>wrote:
>>
>>>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...b;EN-US;155512
>>>
>>>How to Use Schema.ini for Accessing Text Data
>>>http://support.microsoft.com/default...b;EN-US;149090
>>>Schema.ini File (Text File Driver)
>>>http://msdn.microsoft.com/library/de...a_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
>>>
>>>
>>>On Tue, 12 Jun 2007 16:26:19 -0700, RD <(E-Mail Removed)> wrote:
>>>
>>>>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 [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

Thanks. I'll look into it.


On Wed, 13 Jun 2007 22:22:07 +0100, John Nurick <(E-Mail Removed)>
wrote:

>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.