excel - Access VBA中的转义双引号 - INSERT INTO ... SELECT

Question

我拥有下面的 VBA 代码，这对 Access 表有很多文本文件。但是对于包含带双引号的文本的 .TXT 文件的情况，存在一个问题，因此会使用空值打破该记录的所有其他字段。

我试图在产品字段的选择中放置一个替换功能，但对双引号不起作用。与其他字符一起使用，但双引号(否)...

您建议进行哪些调整？任何建议，将不胜感激。

*注:实际数据超过100万条记录...

架构.INI
[Test_temp.csv]
ColNameHeader=false
格式=分隔(;)
Col1="产品"文本
Col2="价格"双倍

文本文件 CSV:test01.txt
三星 21"宽屏 LED 电视；170
电视飞利浦 27"宽屏 LED；200
高清希捷 1TB 7200RPM；150

代码 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

更新 - 它有效 - 建议: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

Answer 1

读取 csv 文件时，双引号被解释为文本分隔符。在 SCHEMA.INI 中似乎没有办法明确告诉 Access“没有文本分隔符!”。

所以我建议改用导入规范。您可以通过文本导入向导手动导入 csv 文件并保存它来创建导入规范，例如作为“产品进口规范”。详见this answer中的1. .

在规范中，您将“无”设置为文本分隔符。在德国 Access :



然后链接文本文件并从中导入数据:

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

编辑:

我创建了一个 1.000.000 行 (36 MB) 的 csv 文件并将其用作导入文件:

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

结果:

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

将自动编号字段作为主键添加到 tbProduct 后:

TransferText: 0,6640625
INSERT: 8,023438

8 秒并不是真的那么慢。
确保 Access 数据库和导入的 CSV 文件都在本地磁盘上，而不是在网络驱动器上。如果可能，在 SSD 上。