asked on

Access 2016 importing text files

My OS is win 10 pro 64 bit and I have office 365 and use Access 2016.
I have a small table in Access where I have to enter bulk data on a regular basis. Attached is a test file, that shows the file I want to import and append to a table in Access. I am not sure how the template should be designed in the Txt file, so that I can add to this template, then import into the table in Access. Grateful for your help. Thanks and regards
test-file.txt
test-file.txt

Rey Obrero (Capricorn1)

test this sample,
change the path and file names accordingly in the codes in module1 Sub ImportText and run
db_28997219_ImportText.accdb

Jegajothy vythilingam

ASKER

In response to Rey, thank u for your response. Can u please check my revised code to see what I am doing wrong.
The name of the directory where the files are in : c:\BUSINES DEALS\Jason Wilson. And the name of access file is : Jason-Justin.mdb. And the table that this text file is appended into is : Imporrted tblJasonJustin. The name of the text file is "Cards 23rd.txt".
my vb is very rusty. Thank u.
amended-code.txt

Rey Obrero (Capricorn1)

test this

Sub ImportText()
Dim txtFile As String
Dim str As String, datArr() As String, j As Integer
Dim rs As DAO.Recordset
Dim Path As String
Path = "c:\BUSINESS DEALS\Jason Wilson\"
Set rs = CurrentDb.OpenRecordset("Imporrted tblJasonJustin")
txtFile = Path & "Cards 23rd.txt"
MsgBox txtFile
MsgBox rs
Open txtFile For Input As #1
Do Until EOF(1)
    Line Input #1, str
    If Len(Trim(str)) > 0 Then
        datArr = Split(str, ",")
        rs.AddNew
        For j = 0 To UBound(datArr)

            If Trim(Mid(datArr(j), InStr(datArr(j), ":") + 1)) <> "" Then
                rs(j) = Trim(Split(datArr(j), ":")(1))
                
            End If
        Next
        rs.Update
    End If
Loop
Close #1
End Sub

Open in new window

Jegajothy vythilingam

ASKER

in response to Rey, I tried the code, but I get an error at Open Txtfile, it says file not found, though the previous msgbox displays the file correctly. I stepped thru the code. Thanks.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jegajothy vythilingam

ASKER

In response to Rey, I corrected that error, but now I get an error : subscript out of range. And this test file has only one line of data.
Mycards.txt

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jegajothy vythilingam

ASKER

Using the original file, I still the subscript out of range error. So let us work on the original file of Cards 23rd.txt.

Rey Obrero (Capricorn1)

are you sure that the Cards 23.txt have the same format as the original text file ?

Jegajothy vythilingam