Re: Import Text with existing schema.ini file utility

From: John Nurick (j.mapSoN.nurick_at_dial.pipex.com)
Date: 01/23/05 

Date: Sun, 23 Jan 2005 18:25:28 +0000

Hi Daniel,

Comments inline.

On Sun, 23 Jan 2005 06:15:03 -0800, "Daniel M"
<DanielM@discussions.microsoft.com> wrote:

>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
>
>"John Nurick" wrote:
>
>> 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"
>> <DanielM@discussions.microsoft.com> wrote:
>>
>> >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.
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>> 

--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.

Relevant Pages

  • Re: Access database records not sorted
    ... I was using DAO but I tried your example of ADO. ... not sordted until I "Compact and Repair" the database ... > Also you need to specify whether your code is using ADO ...
    (microsoft.public.word.vba.general)
  • Re: DAO Security with a Secured Access Database
    ... > Can anyone tell me how I can use DAO to connect to a secured Access ... search the registry for it. ... The registry key you specify in the call to ... open the database must contain at the minimum the same information. ...
    (microsoft.public.data.oledb)
  • Re: KirbyBase
    ... creating objects from the database records was much easier. ... Hal, I don't know if you have had a chance to take a look at the beta yet, but I basically tried to implement a uniform way to specify one-to-one links, one-to-many links, and calculated fields in the ... I suppose it would in effect be embedding an array where all the ... My first couple of attempts at adding more complexity to KirbyBase did not honor this concept. ...
    (comp.lang.ruby)
  • Re: Run action query in back-end from front-end database
    ... whatnot in the back end database. ... The queries are housed in the back end ... The reason I have the upload tables in the back end is that it will be on ... Rick's code uses DAO, and Access 2000 doesn't have a reference set by ...
    (microsoft.public.access.externaldata)
  • Re: Database testing with automated verifications of your modifications ?
    ... Using some kind of capture/playback tool? ... Or some specify the changes in some xml file? ... but some XML file could be okay too. ... .NET that can be used for database testing? ...
    (comp.software.testing)