CODEWITHSUNDEEP
vbams-access
NewMe
NewMe

Reputation: 55

Read text file in Access VBA

I wrote some VBA code to write data from a text file. In the file I have this text: 

H|3434|0104-00000107844/18|Peter|Smith|
D|345345345|"III"|

| character is a delimiter for separating columns in the table. 

Private Sub Polecenie8_Click()

    Dim fieldname As String
    fieldname = "d:\odebrane\tekst1.txt"
    Dim strLineInput As String
    Dim tekst As String
    Dim strLineArray As Variant
    Dim FileNum As Integer

    FileNum = FreeFile()

    Open fieldname For Input As #FileNum

    Do While Not EOF(FileNum)
        Line Input #FileNum, strLineInput

        strLineArray = Split(strLineInput, "|")
        tekst = strLineArray(3)

    Loop
    Me.Tekst0 = tekst

    Close #FileNum

End Sub

strLineArray(0) is equal D not H and I don't know why. I want to save this array in a table, so I want strLineArray(0) to be equal to H and strLineArray(5) to be equal to D.

Upvotes: 2

Views: 11786

Answers (1)

ASH
ASH

Reputation: 20302

How about something like this?

Sub imptxttable()
Dim DB As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field

Dim strSQL As String

Dim strfilepath As String


strfilepath = "your_file.txt"

strSQL = "Select * FROM " & strfilepath

Set DB = OpenDatabase("c:\test", False, False, "Text; Format=Delimited(|);HDR=Yes;CharacterSet=437")
Set rst = DB.OpenRecordset(strSQL)
Debug.Print rst.Fields(0).Name, rst.Fields(0)
With rst
    .MoveLast
    Debug.Print .RecordCount
    .MoveFirst
    For Each fld In rst.Fields
        Debug.Print fld.Name
    Next

End With

rst.Close
DB.Close
Set rst = Nothing
Set DB = Nothing
End Sub

Upvotes: 3

Related Questions