How to import a txt file in MS Access with VBA

SachAccess · Jan 31, 2022

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 · Jan 31, 2022

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.

SachAccess · Jan 31, 2022

Hi @Gasman sir, sorry for incomplete information. Will post codes tried and bug as well. Thanks.

jdraw · Jan 31, 2022

Have you tried using the FileDialog as you did in the Similar Thread post?
Here's a link/sample without fileDialog.

SachAccess · Jan 31, 2022

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

SachAccess · Jan 31, 2022

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

SachAccess · Jan 31, 2022

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 · Jan 31, 2022

SachAccess said:
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

Well yes it does. See https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transfertext

SachAccess · Jan 31, 2022

Gasman said:
Well yes it does. See https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transfertext

Yes sir, but am not able to understand how to resolve this, could you please help if you get time.

Gasman · Jan 31, 2022

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

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 · Jan 31, 2022

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

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

Gasman · Jan 31, 2022

These are the specs I was thinking of.

VBA Method TransferText - SpecificationName

social.msdn.microsoft.com

arnelgp · Jan 31, 2022

does Sample.txt and table table1 has the same column count and column name?

SachAccess · Jan 31, 2022

Hi @arnelgp , no, table1 is blank, there is no data in the table. Thanks for the help.

SachAccess · Jan 31, 2022

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 · Jan 31, 2022

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

SachAccess · Jan 31, 2022

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 · Jan 31, 2022

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 · Jan 31, 2022

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

PersName	PersCity	PersState	PersDoB
John	Miami	FL	21-Apr-98
Bob	Chicago	IL	15-Nov-92
Fred	Toronto	ON	30-Aug-01

Gasman · Jan 31, 2022

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

How to import a txt file in MS Access with VBA

Active member

Enthusiastic Amateur

Active member

Super Moderator

Active member

Active member

Active member

Enthusiastic Amateur

Active member

Enthusiastic Amateur

Enthusiastic Amateur

Enthusiastic Amateur

..forever waiting... waiting for jellybean!

Active member

Active member

Super Moderator

Active member

Immoderate Moderator

Super Moderator

Enthusiastic Amateur

Similar threads

Users who are viewing this thread