HI John,
You are good and I appreciate the direct comments you have made.
I have eliminated (omit) the specification all together leaving a space
between the commas, just as you instructed. I tried this before posting the
question to this forum. However, when the import is completed using the
DoCmd.TransferText in an Access module, all the fields are lumped together
into one. The number of records is correct but with just one field. I will
copy a portion of the schema.ini file below here. In the one schema.ini file,
there are 27 tables set up and what I am pasting is the first part of the
first table. It follows just like this all the way through the file. After
the pasted section, I will sicsuss a little more of the subject (for example
delimited character)...
[am_ant_sys.txt]
Format=Delimited(|)
ColNameHeader=FALSE
MaxScanRows=0
Col1 ant_mode TEXT WIDTH 3
Col2 ant_sys_id LONG
Col3 application_id LONG
Col4 aug_count SHORT
Col5 bad_data_switch TEXT WIDTH 1
Col6 domestic_pattern TEXT WIDTH 1
Col7 dummy_data_switch TEXT WIDTH 1
Col8 efficiency_restricted SINGLE
Col9 efficiency_theoretical SINGLE
Concerning the delimiting character. ..
I plan to download the files from a web site regularily and to change all
the delimited characters from a pipe symbol to a TAB would be intensive
because there would be several million of these to change out on every
download. I would prefer to keep the delimiting symbol as a pipe symbol, | ,
just as it is from the source.
I read somewhere that if the schema.ini file is not read, there is a bug in
the Microsoft Jet Database engine and I would need to upgrade to the latest.
So far as I know, I have the latest (past service pack 8). I am puzzled why
the schema.ini file is either ignored or overridden. The import takes place
but all fields are lumped together in the record. This is when I use the
Access command TransferText in a module. I have not yet tried the DAO method
you suggested but will get there. I would like to solve the problem of why
Access ignores the schema.ini file using the built in TEXT driver.
Thank you for explaining well the issues involving ADO and DAO. I appreciate
that immensely and am willing to dedicate time to learning it better. The
microsoft documentation on the schema.ini use with the Jet Database engine is
quite sparce and doesn't go through enough examples for text related files or
other tutorial material for what to do in case the schema.ini file is
ignored, as it appears to be in my case.
I stress that the directory has the database.mdb file, the table.txt file to
import, and the schema.ini fle to define the structure of the table.txt file.
Thank you for helping to sort this out step by step. Once the schema.ini
file is recognized using the TransferText method, I will be so happy because
I can get on to other things like the relationships between the tables etc...
Otherwise, I guess I will be forced to initialize the DAO engine and use an
SQL statement (which by the way is not well documented either it seems, at
least). I didn't know the format until it was spelled out by you and that was
after I spent two hours searching the MSDN site and other third part sites.
There is a myriad of information to sort through and drilling down to just
the information needed is tough. So, thanks for your help.
Daniel Maxwell
John Nurick said:
Hi Daniel,
Comments inline.
On Sun, 23 Jan 2005 06:15:03 -0800, "Daniel M"
Thanks, John,
I have tried and tried using the Docmd.TransferText feature within Access
but the schema.ini file is ignored. I have tried to leave out the
"schema.ini" portion where the import specifications normally is and I have
also included the "schema.ini" file. I even tried adding the path
"CC:\Documents and Settings\Daniel Maxwell\My Documents\FCC\schema.ini" file.
This is the same folder that the 27 text files are located in and also where
the access database is stored.
I have searched for how to include the schema.ini file in an SQL statement
as well.
If you want to use schema.ini, omit the Specification argument from the
TransferText statement entirely. AIUI, if you don't specify a
specification, the database engine looks for schema.ini.
As far as I know it's not possible to specify schema.ini in SQL; it's
used if it's there and not if it's not.
Forgive me if I'm labouring the obvious, but that schema.ini needs to
contain a section for each file, with the name of the file as the
section header. E.g. if you have files Table1.txt and Table2.txt,
schema.ini must contain a section headed [Table1] that contains the
specifications for Table1.txt and another section headed [Table2] for
Table2.txt.
I really don't want to have to manually enter the specifications for every
table because there are 27 tables and each table (external text file) has
between 4 fields and 70 fields. I have already created a schema.ini file for
all of the tables and wish to use it but cann't get Microsoft Access to
successfully use the "schema.ini" file. It is frustrating. I have checked
that the current version of the Microsoft Jet engine is 4.0 service pack 8
(security release, or something that is related to security after service
pack 8).
Another reason to use the "schema.ini" file is that I can specify how long
the text width should be otherwise the database will be bloated with 255
characters for every text field. The advanced feature in the Import Text
wizard in Access 2003 does not allow us to specify how long the text field
should be in a delimited text file.
As a matter of fact Access only stores the actual text value and not all
255 characters, so "bloat" isn't an issue in this respect.
I use a pipe delimiter (|).
Are you specifying
Format=Delimited(|)
in the entry for each file?
Any help in troubleshooting the problems getting the schema.ini file to work
would be greatly appreciated.
Change the pipes to tabs and
Format=Delimited(|)
to
Format=TabDelimited
The line you wrote below could be useful. Let me modify it and check it out.
However, I noticed this is DAO. What is the difference between this and ADO?
It seems like the Access documentation on Microsoft web site suggests
converting to ADO.
This is a vexed issue. DAO is specifically designed to work with the Jet
database engine, i.e. with data in .mdb files. ADO is more versatile but
therefore incurs more overhead. There are some things that can be done
in DAO and not ADO and vice versa. A few years ago, Microsoft was
pushing ADO and deprecating DAO, but since then ADO has been overtaken
as the general purpose solution while DAO is still going strong in its
sphere, and even Microsoft people are now tending to recommend DAO for
working with Jet.
(I would like to simply use "mycurrentdatabase" in an Access module instead
of opening a database in DAO, is this possible?)
strSQL = "SELECT * INTO tbl_Facility FROM " _
& "[Text;HDR=NO;Database=C:\Documents and Settings\Daniel\My " _ & "Documents\FCC\;].facility#txt"
oDB.Execute strSQL
If the code is in an Access module, do something like this:
Dim oDB as DAO.Database
...
Set oDB = CurrentDB()
...
strSQL = blah blah blah
oDB.Execute strSQL
...
Set oDB = Nothing
'Don't close oDB because that will close your
'current database!
Thank you,
Daniel
:
Hi Daniel,
If you're working from within Access, just use the TransferText macro
action or the DoCmd.TransferText VBA statement.
If you want a utility that will work from the command line without
having to fire up Access, there's a sample VBScript below. Either way,
schema.ini needs to be in the same folder as the textfiles.
'CODE START
'Sample VBScript to import data from a textfile into
'a table in an MDB database without opening Access
'Modify strSQL and filenames as required
Option Explicit
Dim oJet 'DAO.DBEngine
Dim oDB 'DAO.Database
Dim strSQL 'String
Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase("D:\Folder\Database.mdb")
strSQL = "SELECT * INTO MyTable FROM " _
& "[Text;HDR=Yes;Database=D:\Folder\;].FileName#txt"
oDB.Execute strSQL
'Repeat the above pair of lines for as many files
'as you need to import.
oDB.Close
'CODE END
On Sat, 22 Jan 2005 14:47:02 -0800, "Daniel M"
I have created a schema.ini file which accurately describes several text
files and I am looking for a utility someone has already written to simply
import those text files into Microsoft Access tables. Please point to a
utility or write one for me and I will pay you for it. The files reside in a
folder and the Access database will be in the same folder. Thank you.
