Jegajothy vythilingam
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
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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Mycards.txt
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
are you sure that the Cards 23.txt have the same format as the original text file ?
ASKER
Yes, it was not changed.
place this db and the text file in the same folder, and run the code in module1
db_28997219_ImportText.accdb
db_28997219_ImportText.accdb
did it work?
ASKER
thank u, it finally worked, evident that my eyesight is not that great.
change the path and file names accordingly in the codes in module1 Sub ImportText and run
db_28997219_ImportText.accdb