Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Import FixedLength Text File to Access Table using Schema.ini

    Help!
    I have a text file (created in the Mainframe world) with fixed-length fields and records. I want to import to an Access table from it, using Schema.ini to identify field names and lengths. The Access table has already been defined.
    I copied the following code from MSDN (Greg Stemp's article, "Much ADO About Text Files", 5 Mar 2004. I changed the value "strPathToTextFile" and the text filename in "Select * from .....". When I run the sub, it gets to the "Wscript" loop and runs 4ever, with no output.

    Does anyone have any ideas on what is wrong? I'm using MS Access 2003 SP1. Here is the code:

    On Error Resume Next
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H0001

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")

    strPathtoTextFile = "C:\Databases\"

    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strPathtoTextFile & ";" & _
    "Extended Properties=""text;HDR=YES;FMT=FixedLength"""

    objRecordset.Open "SELECT * FROM PhoneList.txt", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

    Do Until objRecordset.EOF
    Wscript.Echo "Name: " & objRecordset.Fields.Item("FirstName")
    Wscript.Echo "Department: " & objRecordset.Fields.Item("LastName")
    Wscript.Echo "Extension: " & objRecordset.Fields.Item("ID")
    objRecordset.MoveNext
    Loop

  2. #2
    Join Date
    May 2009
    Posts
    258
    Try removing the "On Error Resume Next" statement and the problem should be pretty apparent.

    It seems you are making it more difficult than it is though. That script you copied was meant to be used in a VBS file, while VBA in Access is much more robust. You'll really want to use something like the following:
    TransferText Method

    Regards,

    Ax

  3. #3
    Join Date
    Jun 2009
    Posts
    3

    Import Fixed Length...etc

    Hi Ax:

    when I remove the 'On Error Resume Next', you are correct. It is obvious what was missing. After fixing the name however (and the field name in the loop) I now get the message: "Run-Time Error 3265 Item cannot be found in the collection corresponding to the requested name or ordinal".

    I would have expected the table "ADD0DATA" would have been created by the "Select" statement earlier, but no table exists.

    The problem I have with "TransferText" is that I would need to build an ImportSpecification. My preference is the Schema.ini, since the field layout and field length for each of the 15 or so tables in the collection may change from time to time, and I do want to automate the procedure as much as possible. Now if someone could help me automate the creation of the Import Specifications from an already-created table, that would solve the problem.

    Thanks, and back to you.
    jnlw09

  4. #4
    Join Date
    May 2009
    Posts
    258
    The error means that the data you are trying to import does not match what is set up in the schema file. Double check your field names/lengths to make sure they are consistent between the two files.

    From the link I sent you:
    For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. To create a schema file, you can use the text import/export wizard to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.
    You can use the schema file.

  5. #5
    Join Date
    Jun 2009
    Posts
    3

    Almost there!

    Correct. I had extraneous fields at the end of the data .TXT file. Removed, and I can debug.print by field name. Great. However, as previously mentioned, I had expected the SELECT statemtent to populate the ADD0DATA
    table, but it does not. If I modify it to say SELECT * INTO ADD0DATA FROM ADD0DATA.TXT, then I get a permissions error insisting that the file is already opened exclusively by another user, or I need permission to view its data.

    I'm continuing to work on this, but if you have any suggestions in the interim, they would be gratefully received.

    .jnlw09.

  6. #6
    Join Date
    May 2009
    Posts
    258
    I'm still curious why the TransferText method will not work for you, but nonetheless...

    A normal SELECT query will just select data, it doesn't do anything with it. The SELECT * INTO table creates a new table based on the query results. If the ADD0DATA table already exists, you will want to use INSERT INTO, rather than SELECT * INTO. Not sure if the syntax you have is correct though.

    Here's a good resource on the methods available for importing text files into Access:
    ImportText.exe Importing Text into Access with ADO/RDO/DAO/Filesys/Automation

    You can modify the logic to use declared variables, rather than form fields.

    Regards,

    Ax

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •