Importing from a text file

[MarionD]

Hi all ,

I have to import from a text file that has the follwing format:
[1]
Line1
Line2
Line3
[2]
Line1
Line2
[3]
Line1
[4]

etc.
Each number represents one record.

Previously (before the exporting Program did a revamp) each record had 6 lines, and these were exported even if they were empty. I started my import with a "do until freefile.eof", then in this loop a for I=1 to 6 and evaluated each line for one record, wrote the record into my table.

Now empty lines are not exported, hence I don't know how many lines represent 1 record, or which lines are missing.
Does anyone have any idea how I can code this. I know I have to work on the number line somehow, but how do I say in VB:
Evaluate each line till you hit a number, then move back one line, write the record to the table and start over again, with the number line being the first line of the new record.

Wow, does that make sense to anybody?

Thanks
Marion

 

[ByteMyzer]

Let me see if I understand correctly. You'd like to take a file like this:

[1]
Line1
Line2
Line3
[2]
Line1
Line2
[3]
Line1
[4]
Line1
Line2
Line3
Line4
Line5
Line6

...and import it into a table something like this?:

Key | Line1 | Line2 | Line3 | Line4 | Line5 | Line6
---------------------------------------------------
  1 | Line1 | Line2 | Line3 |       |       |
  2 | Line1 | Line2 |       |       |       |
  3 | Line1 |       |       |       |       |
  4 | Line1 | Line2 | Line3 | Line4 | Line5 | Line6

Also, if this is the case, are the lines always filled in consecutively? That is to say, you wouldn't have a record with skips in the information like:

Key | Line1 | Line2 | Line3 | Line4 | Line5 | Line6
---------------------------------------------------
  5 | Line1 | Line2 |       | Line4 |       | Line6

If you do have something like the example just shown, perhaps you can supply a sample of the file you are looking to import (with confidential data properly masked as you see fit).

 

[MarionD]

Hi there,
Thanks for the reply - Thats more or less the idea.
The individual lines, I divide into different Tables checking if they are SE1, SE2, SE3 etc. There are not more than SE4. So each record has an Identifier, then a "Kennsatz" (Description) then the data in 4 Lines SE1 to SE4. Problem is that now, sometimes SE3 is missing, sometimes SE2 or SE4.
(See attachment)

 

[ByteMyzer]

Hmm, based on the data in the attached sample, the situation seems slightly more complex. Could you also provide an example of the expected result in the tables?

 

[MarionD]

Getting the data into the tables is not really that bad - each "field" is divided by the vertical bar (char124). The problem is not knowing how many lines each record has. I've attached the code as I have it at the moment.
Thanks for the help! Really appreiate it!

 

[ByteMyzer]

Without seeing the exact code you are using to evaluate and manipulate the data, I can't advise you on the most effective method.

However, I can provide a sample solution that demonstrates how a similar, basic flex-file structure may be imported into an Access table.

Assume a table structure in Access like the following:

tblAddressData
--------------
ID
Name
Address1
Address2
City
State
Zip
Phone

Now assume an input file (C:\MyFolder\MyFile.txt) like the following (Note that all entries do not have all of the fields):

[1]
Name: John Doe
Address1: 123 Skiddoo Street
Address2: Apt 4
City: New York
State: NY
Zip: 10023
[2]
Name: Richard Roe
Address1: 21 Jump Street
City: Los Angeles
State: CA
zIP: 90001
[3]
Name: Bob Smith
Address1: 101 White Road
City: San Jose
State: CA
Zip: 95127
Phone: 408-555-1212

The following Sub will import the data from the file into the table:

Public Sub ImportAddressData(ByVal sFilePath As String)
    
    Dim ff As Integer
    Dim rs As DAO.Recordset
    Dim sBuffer As String
    
    Set rs = DBEngine(0)(0).OpenRecordset("tblAddressData")
    
    ff = FreeFile
    Open sFilePath For Input As ff
    
    sBuffer = ""
    If EOF(ff) = False Then Line Input #ff, sBuffer
    
    Do
        If InStr(1, sBuffer, "[") > 0 Then
            If rs.EditMode > 0 Then rs.Update
            rs.AddNew
            rs.Fields("ID") = Val(Mid(sBuffer, 2))
        ElseIf InStr(1, sBuffer, ": ") > 0 Then
            rs.Fields(Split(sBuffer, ": ")(0)) = Split(sBuffer, ": ")(1)
        End If
        If EOF(ff) = True Then Exit Do
        Line Input #ff, sBuffer
    Loop
    If rs.EditMode > 0 Then rs.Update
    
    Close #ff
    
    rs.Close
    Set rs = Nothing
    
End Sub

Calling the sub with:
ImportAddressData "C:\MyFolder\MyFile.txt"

...will pull the data into the table. With the above sample, you should see the following result:

ID | Name        | Address1           | Address2 | City        | State | Zip   | Phone
---------------------------------------------------------------------------------------------
 1 | John Doe    | 123 Skiddoo Street | Apt 4    | New York    | NY    | 10023 | 
 2 | Richard Roe | 21 Jump Street     |          | Los Angeles | CA    | 90001 | 
 3 | Bob Smith   | 101 White Road     |          | San Jose    | CA    | 95127 | 408-555-1212

 

[MarionD]

Thank you so much for your trouble. You've just filled out my weekend!! (Ughhhhh) Will let you know how I get on!

Marion

 

[MarionD]

Hi there! Well I spent a very exciting weekend learning all about the split function which I hadn't come accross previously! My import work fine now ! Thank you very much!

One last question if you still have a moment! I found this little example somewhere in the help

dim txt,a
txt="Hello World!"
a=Split(txt)
document.write(a(0) & "<br />")
document.write(a(1))
Output:
Hello
World

Can you tell me what the & "<br />") part does? Some sort of formatting I thought?

thanks again!

 

[Ron_dK]

Can you tell me what the & "<br />") part does? Some sort of formatting I thought?

I'm not sure , but I guess that the <br/> inserts a line break ?