Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import mixed .TXT files (Access 97 SR1)

    Hi Thanks for help given before I wonder if you can help me with this one. I am sent a .TXT file which is made up of different record sets each record set has different fields and each set is set out differently. The first 2 numbers of each record show what type of record it is. What I wanted to do is import the .txt file in and split each record set into a different table matching that record set.
    Example
    70, Address, number, street, town,
    71, customer, trade, payment, outstanding invoices,
    71, customer, trade, payment, outstanding invoices,
    71, customer, trade, payment, outstanding invoices,
    72, suppliers, materials, credits, debt's,
    ECT these are only examples a it would take to long to recreate the real files.
    I Could set up a table for each record type as it is a predefined size, length and type. But I cannot work out how to import and split across tables.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import mixed .TXT files (Access 97 SR1)

    The easiest way out is to ask the people who sent you the text file to send you something that a database recognizes, e.g. separate text files.

    If that is not possible, you'll have to read the text file line by line, then parse the contents. You need to create a table for each possible record type first. I don't have time right now, but if nobody else reacts and if you still need this, I'll see what I can come up with later.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import mixed .TXT files (Access 97 SR1)

    Additional question: in your example, each record type has the same number of fields. Is this true for all record types, or can there be record types with more or fewer fields?

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Import mixed .TXT files (Access 97 SR1)

    A sample of one of these files would allow us to make a better assessment of what the solution may be.

  5. #5
    New Lounger
    Join Date
    Jul 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import mixed .TXT files (Access 97 SR1)

    Hi Thanks for your response, I was just making up the fields for examples. Each record type has the same number and type of record within it but each record type is different and is made up of different types of fields and lenths.

    What I would like is to say import from the .txt file and as Access imports and gets to a different file type it moves it to the table of the same name.
    EG have table 70, 71 ,72 , 73, as the records are imported type 70's go to table 70 then when Access finds a record 71 they are added to table 71 and so on. Each set of records are grouped so it would not need to move back and forth the records come in in order grouped together.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import mixed .TXT files (Access 97 SR1)

    I have tried to write some code to do what you want. Since you haven't provided much information about the organization of your text file, you will have to tweak the code yourself.

    The code assumes that you have created a table that describes the structure of each of the table types. It looks like this:

    <table border=1><td align=center>TableID</td><td align=center>FieldID</td><td align=center>FieldName</td><td align=center>FieldType</td><td align=center>FieldSize</td><td align=right>71</td><td align=right>1</td><td>Address</td><td align=right>10</td><td align=right>50</td><td align=right>71</td><td align=right>2</td><td>Number</td><td align=right>10</td><td align=right>10</td><td align=right>71</td><td align=right>3</td><td>Street</td><td align=right>10</td><td align=right>50</td><td align=right>71</td><td align=right>4</td><td>Town</td><td align=right>10</td><td align=right>30</td><td align=right>72</td><td align=right>1</td><td>Customer</td><td align=right>10</td><td align=right>50</td><td align=right>72</td><td align=right>2</td><td>Trade</td><td align=right>10</td><td align=right>50</td><tr><td align=right>72</td><td align=right>3</td><td>Payment</td><td align=right>5</td><td align=right>
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Jul 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import mixed .TXT files (Access 97 SR1)

    Thanks Hans I will have started working out the lay out of the requered tables I will let you know how I get on when I am done. Mick.

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import mixed .TXT files (Access 97 SR1)

    Hi Hans

    I was trying to get your code working and get "Invalid procedure or call argument" error on following statement:

    strSQL = Left(strSQL, Len(strSQL) - 2) & ") VALUES (" & _
    Left(strValues, Len(strValues) - 2) & ")"

    Value of strSQL at this point is "INSERT INTO [70]("

    Any thoughts?

    John

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

    Sub ImportTextFile()

    Dim fso As New FileSystemObject
    Dim tstr As TextStream
    Dim strLine As String
    Dim dbs As DAO.Database
    Dim rstFields As DAO.Recordset
    Dim strSQL As String
    Dim strValues As String
    Dim strVal As String
    Dim intPos As Integer
    Dim strTable As String

    'On Error GoTo ErrHandler

    Set dbs = CurrentDb

    Set tstr = fso.OpenTextFile(strFile, ForReading)
    Do While Not tstr.AtEndOfStream
    strLine = tstr.ReadLine
    intPos = InStr(strLine, strSeparator)
    strTable = Left(strLine, intPos - 1)
    strLine = Mid(strLine, intPos + 1)
    strValues = ""
    strSQL = "INSERT INTO [" & strTable & "] ("
    Set rstFields = dbs.OpenRecordset("SELECT FieldName, FieldType " & _
    "FROM tblTables WHERE TableID = " & strTable & " ORDER BY FieldID")
    Do While Not rstFields.EOF
    strSQL = strSQL & "[" & rstFields!FieldName & "], "
    intPos = InStr(strLine, strSeparator)
    If intPos > 0 Then
    strVal = Left(strLine, intPos - 1)
    strLine = Mid(strLine, intPos + 1)
    Else
    strVal = strLine
    End If
    If strVal = "" Then
    strValues = strValues & "Null, "
    Else
    Select Case rstFields!FieldType
    Case 1 To 7
    strValues = strValues & strVal & ", "
    Case 8
    strValues = strValues & "#" & strVal & "#, "
    Case 10, 12
    strValues = strValues & Chr(34) & strVal & Chr(34) & ", "
    Case Else
    MsgBox "Routine can't handle this data type.", vbInformation
    End Select
    End If
    rstFields.MoveNext
    Loop
    rstFields.Close
    strSQL = Left(strSQL, Len(strSQL) - 2) & ") VALUES (" & _
    Left(strValues, Len(strValues) - 2) & ")"
    dbs.Execute strSQL, dbFailOnError
    Loop

    ExitHandler:
    On Error Resume Next
    rstFields.Close
    Set rstFields = Nothing
    Set dbs = Nothing
    Set tstr = Nothing
    Set fso = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import mixed .TXT files (Access 97 SR1)

    John,

    This means that the procedure failed to find any fields for the table to be filled in the tblTables table. In my example database, tblTables contains descriptions of the fields of tables named "71", "72" and "73", not for a table named "70" - the name you are using, witness the value of strSQL when you get the error.

    You will either have to add records in tblTables to describe the fields in "70", or change 70 to 71 or so in the text file.

    BTW, I never heard from Tweeky how (s)he fared with the code.

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import mixed .TXT files (Access 97 SR1)

    Hi Hans

    Having proper test data did the trick.

    Thanks, John

    PS: This is great code and technique, to bad some forum users don't provide feedback, you spent some time trying to provide a solution.

Posting Permissions

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