Escape double quotes in Access VBA - INSERT INTO …

问题:

I own VBA code below that matter many text files to an Access table. But there is a problem for cases of .TXT files containing text with double quotation marks and consequently breaks all the other fields of this record with null values.

I tried to put a Replace function in the selection of the Product field, but did not work for Double Quotes. With other characters it works, but double quotes (no) ...

Which adjustments do you recommend? Any suggestions would be appreciated.

*Note: The actual data is more than 1 million records...

SCHEMA.INI
[Test_temp.csv]
ColNameHeader=false
Format=Delimited(;)
Col1="product" Text
Col2="price" Double

TEXT FILE CSV : test01.txt
TV SAMSUNG 21" WIDESCREEN LED;170
TV PHILIPS 27" WIDESCREEN LED;200
HD SEAGATE 1TB 7200RPM;150

Code VBA Access:

Sub TableImport()

    Dim strSQL As String
    Dim db As DAO.Database

    Dim strFolder As String
    strFolder = CurrentProject.Path

    Set db = CurrentDb

    strSQL = "DELETE FROM tbTest"
    db.Execute strSQL, dbFailOnError

    Dim strFile As String
    strFile = Dir(strFolder & "\test*.txt", vbNormal)

    Do Until strFile = ""

        FileCopy strFolder & "\" & strFile, strFolder & "\Test_temp.csv"

        strSQL = ""

        strSQL = " INSERT INTO tbTEST(product,price)"
        strSQL = strSQL & " SELECT fncReplace(product),price"
        strSQL = strSQL & " FROM [Text;HDR=no;FMT=Delimited;DATABASE=" & strFolder & "].Test_temp.csv"

        db.Execute strSQL, dbFailOnError

        strFile = Dir

    Loop

    db.Close

End Sub


Public Function fncReplace(varStr As Variant) As String
    If IsNull(varStr) Then
        fncReplace = ""
    Else
        fncReplace = Replace(Trim(varStr), """", "''")
    End If
End Function

UPDATE - It worked - Suggested by: Andre451

Sub TableImport()

    Dim strSQL As String
    Dim db As DAO.Database

    Dim strFolder As String
    strFolder = CurrentProject.Path

    Set db = CurrentDb

    strSQL = "DELETE FROM tbTest"
    db.Execute strSQL, dbFailOnError

    Dim strFile As String
    strFile = Dir(strFolder & "\test*.txt", vbNormal)

    Do Until strFile = ""

        FileCopy strFolder & "\" & strFile, strFolder & "\Test_temp.csv"

        DoCmd.TransferText acLinkDelim, "specIMPORTAR", "linkData", strFolder & "\Test_temp.csv", False

        strSQL = ""
        strSQL = " INSERT INTO tbTEST(product,price)"
        strSQL = strSQL & " SELECT product,price"
        strSQL = strSQL & " FROM linkData"

        db.Execute strSQL, dbFailOnError

        strFile = Dir

        DoCmd.DeleteObject acTable, "linkData"

    Loop

    db.Close

End Sub

回答1:

When reading the csv file, the double quote is interpreted as text delimiter. In SCHEMA.INI there seems to be no way to explicitly tell Access "there is NO text delimiter!".

So I suggest using an import specification instead. You create the import specification by importing the csv file once manually via the Text import wizard, and saving it e.g. as "Product import specification". For details see 1. in this answer.

In the specification, you set "none" as text delimiter. In German Access:

Then you link the text file and import the data from it:

Public Sub ImportProducts()

    Dim S As String

    ' Link csv file as temp table
    DoCmd.TransferText acLinkDelim, "Product import specification", "linkData", "D:\temp\Test01.csv", False

    ' Insert from temp table into product table
    S = "INSERT INTO tbProduct (product, price) SELECT product, price FROM linkData"
    CurrentDb.Execute S

    ' Remove temp table
    DoCmd.DeleteObject acTable, "linkData"

End Sub

Edit:

I created a csv file with 1.000.000 lines (36 MB) and used that as import file:

Const cFile = "G:\test.csv"

Public Sub CreateCSV()

    Dim S As String
    Dim i As Long

    Open cFile For Output As #1
    For i = 1 To 1000000
        Print #1, "Testing string number " & CStr(i) & ";" & CStr(i)
    Next i
    Close #1

End Sub

Public Sub ImportProducts()

    Dim S As String
    Dim snTime As Single

    snTime = Timer

    ' Clean up product table
    CurrentDb.Execute "DELETE * FROM tbProduct"
    Debug.Print "DELETE: " & Timer - snTime

    ' Link csv file as temp table
    DoCmd.TransferText acLinkDelim, "Product import specification", "linkData", cFile, False
    Debug.Print "TransferText: " & Timer - snTime

    ' Insert from temp table into product table
    S = "INSERT INTO tbProduct (product, price) SELECT product, price FROM linkData"
    CurrentDb.Execute S
    Debug.Print "INSERT: " & Timer - snTime

    ' Remove temp table
    DoCmd.DeleteObject acTable, "linkData"

End Sub

Result:

DELETE: 0
TransferText: 0,6640625
INSERT: 4,679688

After adding an autonumber field as primary key to tbProduct:

TransferText: 0,6640625
INSERT: 8,023438

8 seconds is not really all that slow.
Make sure that both the Access database and the imported CSV file are on a local disk, not on a network drive. If possible, on a SSD.

回答2:

Since you are copying the file from test01.txt to temp_test.csv, why not take the opportunity to crack it open and replace the unwanted quotes with a Unicode 'smart quote' character (e.g. ”) that isn't going to foul up the CSV read?

Sub TableImport()

    Dim strSQL As String, f As Long, strm As String, ln as long
    Dim db As DAO.Database, rs As DAO.Recordset

    Dim strFolder As String
    strFolder = Environ("TEMP") 'CurrentProject.Path

    Set db = CurrentDb

    strSQL = "DELETE FROM tbTest"
    db.Execute strSQL, dbFailOnError

    Dim strFile As String
    strFile = Dir(strFolder & "\test*.txt", vbNormal)

    Do Until strFile = ""

        strm = vbNullString
        f = FreeFile
        Open strFolder & "\" & strFile For Binary Access Read As #f
        strm = Input$(LOF(f), f)
        Close #f
        strm = Replace(strm, Chr(34), ChrW(8221))   '<~~ replace double-quote character with Unicode right smart quote character
        'optionally strip off the first 5 lines
        for ln = 1 to 5
            strm = mid$(strm, instr(1, strm, chr(10)) + 1)
        next ln
        Kill strFolder & "\Test_temp.csv"
        f = FreeFile
        Open strFolder & "\Test_temp.csv" For Binary Access Write As #f
        Put #f, , strm
        Close #f

        strSQL = vbNullString
        strSQL = "INSERT INTO tbTEST(product,price)"
        strSQL = strSQL & " SELECT F1, F2"
        strSQL = strSQL & " FROM [Text;HDR=no;FMT=Delimited(;);DATABASE=" & strFolder & "].[Test_temp.csv]"

        db.Execute strSQL, dbFailOnError + dbSeeChanges

        strFile = Dir

    Loop

    db.Close

End Sub

        

回答3:

All you need is to wrap double-quotes in single-quotes:

Public Function fncReplace(varStr As Variant) As String
    fncReplace = Replace(Trim(Nz(varStr)), Chr(39), Chr(34) & Chr(39))
End Function

That said, I would find it easier to first link the file as a table, then use the linked table as source.