CODEWITHSUNDEEP
ms-accessvba
El_Mismisimo
El_Mismisimo

Reputation: 11

Parsing txt file in Access VBA

I have never used Access VBA, but I need to create a module that parses a txt file and then immediately imports it into a table.

A dumbed-down of the txt is this:

15686541

468469

48978965

456287

48666545

45684651

456788

I need to parse it in order to

  1. Remove all the line/rows that are not six characters long
  2. Add commas after the third and fifth characters

The final result being something like:

468,46,9

456,28,7

456,78,8

All this must be done in an Access VBA module so that the importing process becomes seamless.

Thanks a lot!

Sorry to bother

Upvotes: 0

Views: 3536

Answers (2)

Gustav
Gustav

Reputation: 55816

This function will do that - and very fast:

Public Function ImportLog(ByVal Filename As String) As Long

    Dim rs      As DAO.Recordset

    Dim File    As Integer
    Dim Data    As String
    Dim Data6   As String
    Dim Records As Long

    Set rs = CurrentDb.OpenRecordset("Select Top 1 * From YourTableName")

    File = FreeFile()
    Open Filename For Input As #File

    While Not EOF(File)
        Line Input #File, Data
        If Len(Data) = 6 Then
            Data6 = Space(6 + 2) ' Space for six digits and two commas.
            ' Build field value.
            Mid(Data6, 1, 3) = Mid(Data, 1, 3)
            Mid(Data6, 4, 1) = ","
            Mid(Data6, 5, 2) = Mid(Data, 4, 2)
            Mid(Data6, 7, 1) = ","
            Mid(Data6, 8, 1) = Mid(Data, 6, 1)
            rs.AddNew
                ' Adjust "Data" to your field name.
                rs.Fields("Data").Value = Data6  
            rs.Update
            Records = Records + 1
        End If
    Wend
    Close #File
    rs.Close

    Set rs = Nothing

    ImportLog = Records

End Function

The return value is the count of added records.

Upvotes: 3

mdialogo
mdialogo

Reputation: 473

Try this:

Sub Import()
    Dim fileNum As Integer
    Dim dataLine As String
    Dim column1 As String
    Dim column2 As String
    Dim column3 As String

    fileNum = FreeFile()
    Open "Filename.txt" For Input As #fileNum

    While Not EOF(fileNum)
        Line Input #fileNum, dataLine
        If Len(dataLine) = 6 Then
            column1 = Mid(dataLine, 1, 3)
            column2 = Mid(dataLine, 4, 2)
            column3 = Mid(dataLine, 6, 1)
            CurrentDb.Execute "INSERT INTO YourTable(Column1, Column2, Column3) VALUES('" & column1 & "', '" & column2 & "', '" & column3 & "')"
        End If
    Wend

    Close #fileNum
End Sub

Upvotes: 0

Related Questions