How to import a txt file in MS Access with VBA (1 Viewer)

S

SachAccess

Active member
Local time
Today, 19:51
Joined
Nov 22, 2021
Messages
389
Hi,

I have a txt file. I am trying to import this txt file in MS Access.
I tried various codes from Google however am getting bug at one or the other line.

My txt file name is 'Sample', values are separated with | in the text file.
Am trying to import the file in Table1 of my Access file.
Can anyone please help me in this.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 15:21
Joined
Sep 21, 2011
Messages
14,238
Really! Not much to go on, not even which error you get? :(
Not even code shown?

I would manually import and create and save a specification when doing so.
Then use that when importing with VBA.
 
S

SachAccess

Active member
Local time
Today, 19:51
Joined
Nov 22, 2021
Messages
389
Hi @Gasman sir, sorry for incomplete information. Will post codes tried and bug as well. Thanks.
 
S

SachAccess

Active member
Local time
Today, 19:51
Joined
Nov 22, 2021
Messages
389
This is one example.

The action or method requires a Table Name argument.

Code: 
Sub InsertData()
    DoCmd.TransferText TransferType:=acImportDelim, _
    SpecificationName:="Generic", _
    FileName:="D:\MMM\Personal Documents\MyAccess\Sample.txt", _
    hasfieldnames:=False
End Sub
 
S

SachAccess

Active member
Local time
Today, 19:51
Joined
Nov 22, 2021
Messages
389
Second example for your reference. Please note, am importing TXT file in MS Access for the first time.

Run-Time Error 3625
The text file specification 'Table1 Import Specification' does not exist.
You cannot import, export, or link using the Specification.

Code: 
Sub InsertData()
    DoCmd.TransferText transfertype:=acImportFixed, _
    specificationname:="Table1 Import Specification", _
    tablename:="Table1", _
    FileName:="D:\MMM\Personal Documents\MyAccess\Sample.txt", _
    hasfieldnames:=True
End Sub
 
S

SachAccess

Active member
Local time
Today, 19:51
Joined
Nov 22, 2021
Messages
389
This code is giving bug at below line.
CurrentDb.Execute SQL
Run-time error 3134
Syntax error in INSERT INTO statement.

Code: 
Private Sub Example()
    strFile = "D:\MMM\Personal Documents\MyAccess\Sample.txt"
    fs = CreateObject("Scripting.FileSystemObject").OpenTextFile(strFile, 1).readall
    fs = Replace(fs, "/Groups", vbNewLine & "Groups")
    For Each itm In Split(fs, vbNewLine & vbNewLine)
        tbl = "Table1"
        flds = ""
        vals = ""
        fLines = Split(Trim(itm), vbNewLine)
        
        Select Case UBound(fLines) + 1
        Case 9:   tbl = "tbl1"
        Case 13:  tbl = "tbl2"
        End Select


        If tbl <> "" Then
            For Each i In fLines
                f = Splitter(i)
                If Not IsEmpty(f) Then
                    If Len(flds) Then
                        flds = flds & ","
                        vals = vals & ","
                    End If
                    flds = flds & f(0)
                    vals = vals & f(1)
                End If
            Next
            SQL = "insert into [" & tbl & "] (" & flds & ") values (" & vals & ")"
            Debug.Print SQL
            CurrentDb.Execute SQL
        End If
    Next
End Sub
Private Function Splitter(s)
    If Trim(s) = "" Then Exit Function
    s = Replace(s, "/", "=")
    s = Replace(s, "[", "")
    s = Replace(s, "]", "")
    s = Replace(s, "|", "")
    If InStr(s, "=") Then
        a = Split(s, "=")
        a(1) = "'" & a(1) & "'"
        Splitter = a
    End If
End Function
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 15:21
Joined
Sep 21, 2011
Messages
14,238
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 15:21
Joined
Sep 21, 2011
Messages
14,238
SachAccess said:
Second example for your reference. Please note, am importing TXT file in MS Access for the first time.

Run-Time Error 3625
The text file specification 'Table1 Import Specification' does not exist.
You cannot import, export, or link using the Specification.

Code: 
Sub InsertData()
    DoCmd.TransferText transfertype:=acImportFixed, _
    specificationname:="Table1 Import Specification", _
    tablename:="Table1", _
    FileName:="D:\MMM\Personal Documents\MyAccess\Sample.txt", _
    hasfieldnames:=True
End Sub
Click to expand...
I seem to recall that there are two types of specifications, and only one is available to VBA.
You still have to create the one used in VBA in the first place?
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 15:21
Joined
Sep 21, 2011
Messages
14,238
SachAccess said:
This code is giving bug at below line.
CurrentDb.Execute SQL
Run-time error 3134
Syntax error in INSERT INTO statement.

Code: 
Private Sub Example()
    strFile = "D:\MMM\Personal Documents\MyAccess\Sample.txt"
    fs = CreateObject("Scripting.FileSystemObject").OpenTextFile(strFile, 1).readall
    fs = Replace(fs, "/Groups", vbNewLine & "Groups")
    For Each itm In Split(fs, vbNewLine & vbNewLine)
        tbl = "Table1"
        flds = ""
        vals = ""
        fLines = Split(Trim(itm), vbNewLine)
       
        Select Case UBound(fLines) + 1
        Case 9:   tbl = "tbl1"
        Case 13:  tbl = "tbl2"
        End Select


        If tbl <> "" Then
            For Each i In fLines
                f = Splitter(i)
                If Not IsEmpty(f) Then
                    If Len(flds) Then
                        flds = flds & ","
                        vals = vals & ","
                    End If
                    flds = flds & f(0)
                    vals = vals & f(1)
                End If
            Next
            SQL = "insert into [" & tbl & "] (" & flds & ") values (" & vals & ")"
            Debug.Print SQL
            CurrentDb.Execute SQL
        End If
    Next
End Sub
Private Function Splitter(s)
    If Trim(s) = "" Then Exit Function
    s = Replace(s, "/", "=")
    s = Replace(s, "[", "")
    s = Replace(s, "]", "")
    s = Replace(s, "|", "")
    If InStr(s, "=") Then
        a = Split(s, "=")
        a(1) = "'" & a(1) & "'"
        Splitter = a
    End If
End Function
Click to expand...
Ok, the debug.print SQL is there for a reason?
Copy and paste the output if you cannot see your error.

Compare the statement with one you generate via the QBE GUI
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:21
Joined
May 7, 2009
Messages
19,230
does Sample.txt and table table1 has the same column count and column name?
 
S

SachAccess

Active member
Local time
Today, 19:51
Joined
Nov 22, 2021
Messages
389
Hi,

Below seems to be working for me. Still checking, thanks a lot. :)

Code: 
'https://stackoverflow.com/questions/32390960/vba-code-import-a-text-file-into-an-access-table-with-condition
Sub MyTableImport()
    Dim sqlStr As String

    sqlStr = "SELECT * INTO NewTable "
    sqlStr = sqlStr & " FROM [Text;HDR=Yes;FMT=Delimited;Database=D:\MMM\Personal Documents\MyAccess].Sample.txt "
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL sqlStr
    DoCmd.SetWarnings True
End Sub
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 10:21
Joined
Jan 23, 2006
Messages
15,379
Here is a sample that may help.
My Sample.txt has fieldnames in row 1 and uses | delimiter.
Sample.txt
pername|perCity|perstate|perDoB
John| Miami|FL|04/21/1998
Bob| Chicago|IL|11/15/1992
Fred|Toronto | ON| 08/30/2001

Code creates table: SachTable1

Code: 
Public Sub JRead_RawTextFile()

          Dim rs As DAO.Recordset
          Dim varHold As Variant
          Dim sFile As String  'full path and filename of text file
          Dim sLine As String  'individual line/record in text file
          Dim i As Integer
          Dim j As Integer
          Dim writecnt As Integer 'counter for records written to table
          Dim strSQL_Create As String
       
          ' SQL to Create the fixed format table
          'You only need to create this table once.
        
        
10        strSQL_Create = "CREATE TABLE SachTable1 " & _
              "(PersName varchar(35)," & _
              "PersCity  varchar(12)," & _
              "PersState varchar(10)," & _
              "PersDoB date);"
20        If i = 0 Then CurrentDb.Execute strSQL_Create, dbFailOnError
30        Set rs = CurrentDb.OpenRecordset("SachTable1")

40        sFile = "c:\users\jp\documents\documents_lenovo\documents\Sample.txt"     'text file that contains | delimiter
50        Open sFile For Input As #1
60        While Not EOF(1)
70            i = i + 1
80            rs.AddNew
90            Line Input #1, sLine               'read a record
100           varHold = Split(sLine, "|")
110           For j = LBound(varHold) To UBound(varHold)
120               Debug.Print varHold(j)
130               If i = 1 Then GoTo get_next         'first record contains field names
140               rs.Fields(j) = Trim(varHold(j))
150           Next
160           rs.Update
170     writecnt = writecnt + 1
get_next:
180       Wend

190      Debug.Print "Records read: " & i & "    Records written: " & writecnt
End Sub
 
S

SachAccess

Active member
Local time
Today, 19:51
Joined
Nov 22, 2021
Messages
389
Hi @jdraw , thanks a lot for the help. :)
Am trying code at my desk.
Am getting a bug at rs.Fields(j) = Trim(varHold(j)) this line. Line starting with 140 in the code.
Run-time error 3421
Data type conversion error.

Could you please help me understanding this if you get time.
PS - My file does not have any headers. Forgot to mention earlier, edited now.
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:21
Joined
Feb 28, 2001
Messages
27,150
Typically, "Data type conversion error" means you are trying to use something that is the wrong data type for the field in question. If you get to the line in question and J is 4, it points to a date field. I don't think Trim works correctly with date fields because dates are internally stored as numbers. Only the displayed date is text-oriented.
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 10:21
Joined
Jan 23, 2006
Messages
15,379
Can you post your sample.txt file --at least a few records?

My sample was delimited with |. I'd also like to see the design of Table1.
Update1
Just saw Doc's comment ---he's probably right about the trim with Dates.
Give me a few records and we'll get it working.

But it worked with my sample (my regional setting has DD-MMM-YY)
SachTable1 SachTable1

PersNamePersCityPersStatePersDoB
JohnMiamiFL
21-Apr-98​
BobChicagoIL
15-Nov-92​
FredTorontoON
30-Aug-01​
 
Last edited:
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 15:21
Joined
Sep 21, 2011
Messages
14,238
I've just created that spec shown below and used it to create a new table, as I created it with New Table option
Code: 
Sub TestImport()
DoCmd.TransferText acImportDelim, "VBA Transactions Import Specification", "NewVBAImport", "c:\temp\transactions.csv"

End Sub

The spec was save via the Advanced button.

HTH
 
You must log in or register to reply here.

Similar threads

N
Solved Mass Importing Txt Files
Replies
14
Views
380
ebs17
E
N
Code does not support certain characters such as full-width symbols or Chinese
Replies
1
Views
332
ebs17
E
S
Solved Import .CSV VBA Run time Error 3011
Replies
4
Views
332
sathsaj
S
C
How do i remove quotations in text file export.
Replies
8
Views
413
Isaac
Isaac
D
  • Question
Exporting to txt with data split by condition
Replies
5
Views
283
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom