SCHEMA.INI FILE

[jay]

Is there some magic about the Schema.ini file? I have placed it in the same
directory as the TEST.TXT file with the following info:

[test.txt]
MaxScanRows=0

I then go through and try to open a new database, select the TEST.TXT file,
the import Wizard comes on and only shows 25 lines of the 41 line file.

Meanwhile Excel can read all 41 lines.

I am dazed and confused

Thanks

Jay

 

[John Nurick]

Hi Jay,

Is this one of your files in which different lines have different
numbers of fields?

If so, remember what I said before: the Access/Jet textfile routines are
designed to handle ordinary data files in which every line has the same
structure. You'll need either to make your files conform, or else write
your own import routine.

Is there some magic about the Schema.ini file? I have placed it in the same
directory as the TEST.TXT file with the following info:

[test.txt]
MaxScanRows=0

I then go through and try to open a new database, select the TEST.TXT file,
the import Wizard comes on and only shows 25 lines of the 41 line file.

Meanwhile Excel can read all 41 lines.

I am dazed and confused

Thanks

Jay

 

[Jay]

Ohhhhhhh, nooooooooooooooooooo

Did I miss something?

The files are comprised of 4 types of records, each type of record having
different field width. So there is no way of telling ACCESS to say "Please
open up 60 fields and dump in each record using the CR/LF as the end of each
record" ?

I guess that would fall under the scenario that its attempting to place the
same data under each field.

I guess I have to figure out how to load a database.

Thanks for your staying in there

Jay





--
*******************Confidentiality and Privilege Notice*******************
This email is intended only to be read or used by the addressee. It is
confidential and may contain legally privileged information. If you are not
the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to
anyone, and you should destroy this message and kindly notify the sender by
reply email. Confidentiality and legal privilege are not waived or lost by
reason of mistaken delivery to you

Hi Jay,

Is this one of your files in which different lines have different
numbers of fields?

If so, remember what I said before: the Access/Jet textfile routines are
designed to handle ordinary data files in which every line has the same
structure. You'll need either to make your files conform, or else write
your own import routine.

Is there some magic about the Schema.ini file? I have placed it in the same
directory as the TEST.TXT file with the following info:

[test.txt]
MaxScanRows=0

I then go through and try to open a new database, select the TEST.TXT file,
the import Wizard comes on and only shows 25 lines of the 41 line file.

Meanwhile Excel can read all 41 lines.

I am dazed and confused

Thanks

Jay

 

[Jay]

I just read your other response......

--
*******************Confidentiality and Privilege Notice*******************
This email is intended only to be read or used by the addressee. It is
confidential and may contain legally privileged information. If you are not
the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to
anyone, and you should destroy this message and kindly notify the sender by
reply email. Confidentiality and legal privilege are not waived or lost by
reason of mistaken delivery to you

Hi Jay,

Is this one of your files in which different lines have different
numbers of fields?

If so, remember what I said before: the Access/Jet textfile routines are
designed to handle ordinary data files in which every line has the same
structure. You'll need either to make your files conform, or else write
your own import routine.

Is there some magic about the Schema.ini file? I have placed it in the same
directory as the TEST.TXT file with the following info:

[test.txt]
MaxScanRows=0

I then go through and try to open a new database, select the TEST.TXT file,
the import Wizard comes on and only shows 25 lines of the 41 line file.

Meanwhile Excel can read all 41 lines.

I am dazed and confused

Thanks

Jay

 

[Joe Fallon]

You have to avaoid the wizard and write code to import files that do not
have a table like structure.
Here is an outline for how to do it.
You need 1 recodset for each type of data in your file. Sounds like you have
4.

===================================

Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to a String
variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub

--
Joe Fallon
Access MVP

Ohhhhhhh, nooooooooooooooooooo

Did I miss something?

The files are comprised of 4 types of records, each type of record having
different field width. So there is no way of telling ACCESS to say "Please
open up 60 fields and dump in each record using the CR/LF as the end of
each
record" ?

I guess that would fall under the scenario that its attempting to place
the
same data under each field.

I guess I have to figure out how to load a database.

Thanks for your staying in there

Jay





--
*******************Confidentiality and Privilege Notice*******************
This email is intended only to be read or used by the addressee. It is
confidential and may contain legally privileged information. If you are
not
the addressee indicated in this message (or responsible for delivery of
the
message to such person), you may not copy or deliver this message to
anyone, and you should destroy this message and kindly notify the sender
by
reply email. Confidentiality and legal privilege are not waived or lost by
reason of mistaken delivery to you

Hi Jay,

Is this one of your files in which different lines have different
numbers of fields?

If so, remember what I said before: the Access/Jet textfile routines are
designed to handle ordinary data files in which every line has the same
structure. You'll need either to make your files conform, or else write
your own import routine.

Is there some magic about the Schema.ini file? I have placed it in the same
directory as the TEST.TXT file with the following info:

[test.txt]
MaxScanRows=0

I then go through and try to open a new database, select the TEST.TXT file,
the import Wizard comes on and only shows 25 lines of the 41 line file.

Meanwhile Excel can read all 41 lines.

I am dazed and confused

Thanks

Jay

 

[John Nurick]

Ohhhhhhh, nooooooooooooooooooo

Did I miss something?

Looks like it.

The files are comprised of 4 types of records, each type of record having
different field width. So there is no way of telling ACCESS to say "Please
open up 60 fields and dump in each record using the CR/LF as the end of each
record" ?

Normally if there are 4 different kinds of record a database designer
would put them into 4 different tables. If you want to put them into a
single Access table you must - no alternative - structure that table so
that it can accommodate any of the 4 kinds of record with *corresponding
fields from each kind of record& going into the *same* field in the
table.

For instance, if all the records have a PartNumber field, but it's the
fourth field in a P record and the fifth in a R record, they must both
map to the same PartNumber field in the "combined" table.

Otherwise, querying and analysing the data in Access ranges from very
difficult to totally impossible. (If you're not intending to query and
analyse the data, don't bother to import it into Access.)

I guess that would fall under the scenario that its attempting to place the
same data under each field.

I guess I have to figure out how to load a database.

First, can you go back to square one and remind us what you actually
need to do. Not in terms of the detailed steps you envisage with the
hundreds of little files, but more in terms of the real-world entities
and business processes.

 

[Jamie Collins]

So there is no way of telling ACCESS to say "Please
open up 60 fields and dump in each record using the CR/LF as the end of each
record" ?

Looking at your earlier post

http://groups.google.com/[email protected]

the data appears to be pipe (|) delimited. Therefore, you can achieve
your aims if you edit your schema.ini file to look like this:

[test.txt]
Format=Delimited(|)
Col1=MyCol1 Text Width 255
Col2=MyCol2 Text Width 255
Col3=MyCol3 Text Width 255
.... etc etc...
Col60=MyCol60 Text Width 255

and then query it e.g.

SELECT *
INTO MyNewTable
FROM [Text;Database=C:\MyFolder\;].test#txt
;

Jamie.

--

 

[Gregory Paret]

Is there some magic about the Schema.ini file? I have placed it in the same
directory as the TEST.TXT file with the following info:

[test.txt]
MaxScanRows=0

I then go through and try to open a new database, select the TEST.TXT file,
the import Wizard comes on and only shows 25 lines of the 41 line file.

It does seem that the wizard does only display the first 25 lines, regardless
of the MaxScanRows setting, but you can still use the Import Specification to
define the "unseen" columns. If you haven't been there yet, use the
Advanced... button on the Link/Import Wizard to get to the Link/Import
Specification screen, and verify and/or add to the fields the wizard picked
up. Also, use the Specs... button on the Specification screen to access
previously saved specifications.

As John and Joe have pointed out in other replies, however, all this is going
to do is get your data into a table in "column-for-column" format, with no
knowledge of which columns really contain which data. This is going to make
accessing or processing the data much more difficult (e.g., always having to
refer to each record's record type,) than if a given record type's field data
were put into corresponding table fields (or even into different tables as I
might suspect, given some apparent data references from one record type to
another.) This would take, as was pointed out, more than just what the import
wizard can do, but is not beyond what a little VBA can.

-Greg.