text file row order - append line number

[CData]

greetings again... have a text file to import - let's call it FName.txt and pretend it is typically about 1500 records (fixed width with CRLF at end of each fixed width string)

FName.txt row 1, 2, and 3 (and sometimes more rows) all ultimately need to be written into the same single record of database table T1 i.e. record ID X
...and there is no unique record ID shared by rows 1, 2, 3 to tie/group them together.... (and this repeats 4,5,6,7 go to record ID Y, etc - I know by the content of the row when a new table record starts)

So I went to the source and said I need an explicit unique record Id to group txt rows together into a common table record - - and their large company bureaucratic reply is "hey no need cause the rows are all in order"...

well they don't import in the same order of the source file reliably.... I set up an import spec and a saved import - and the rows are not always in the same order as the source FName.txt. And I understand that in databases there is no ordinal property with tables....

so am thinking maybe could append a line count to the FName.txt either start or end of each existing line. Once I have that - there's enough info in the row to tie them together. An easy manual manipulation in excel but I have no experience in VBA to write a line count to each line of a txt file. It is in a fixed folder location with a fixed file name and will always have a fixed structure. So I hold out hope but can't find a code method. Or maybe there's another approach. TIA.

[HansV]

You could read the text file into a string variable, split it on CRLF and then loop through the resulting array. That should provide you with the lines in the same order as in the text file. I assume you can then split each line into the necessary variables. I'll try to whip up an example to give you the basic idea.

[HansV]

Here is some skeleton code. You'd want to open a recordset and append records to it of course.

Code: Select all

Sub ProcessTextFile()
    Dim strFile As String
    Dim f As Integer
    Dim strLines As String
    Dim arrLines() As String
    Dim strPrevLine As String
    Dim strLine As String
    Dim i As Long
    ' Prompt for a text file
    With Application.FileDialog(1)
        .Filters.Clear
        .Filters.Add "Text files", "*.txt"
        If .Show Then
            strFile = .SelectedItems(1)
        Else
            Beep
            Exit Sub
        End If
    End With
    ' Open the text file
    f = FreeFile
    Open strFile For Input As #f
    ' Read it into a string variable
    strLines = Input(LOF(f), #f)
    ' Close the file, we don't need it anymore
    Close #f
    ' Split into array of lines
    arrLines = Split(strLines, vbCrLf)
    ' Loop through the lines
    For i = 0 To UBound(arrLines)
        ' strLine is hte current line being processed
        strLine = arrLines(i)
        ' Do something with the line.
        '...
        ' The previous line is stored in strPrevLine
        strPrevLine = strLine
    Next i
End Sub

[CData]

well definitely a thank you as always... but hmmmmmm … I get the idea... .... an append nested inside of the loop...wonder if slow...
my guess to just count the CRLF and append that count to the text line not feasible....would like to keep the append outside the loop and work the entire file not line by line.....
it's a dilemma... changing strategy entirely - - since FName.txt is fixed location maybe I could use code to set up a link and try an append query instead of an import in hopes
that an append query would maintain the order - …. obviously I'm struggling with this one and just typing out loud..... always appreciate your input.

[HansV]

Let us know how you get on.

[CData]

in a Q/A string on Microsoft site one of the MVP suggested Append rather than Import - and that does keep the row order in my test. The table set up to be appended into just needs an Autonumber field to provide a numerical sequence. I don't know why one method works and the other does not - and my testing is pretty superficial as I only have 1 source file to test with..... I'm going to save your skeleton code as I think there is going to be a need for it sometime else in dealing with unstructured text files.