在Access VBA - INSERT INTO ... SELECT中转义双引号 [英] Escape double quotes in Access VBA - INSERT INTO ... SELECT
问题描述
我自己的VBA代码下面有很多文本文件到Access表。但是对于包含带有双引号的文本的.TXT文件的情况，会出现一个问题，因此会将此记录的所有其他字段以空值打断。
一个替换函数在选择产品字段，但不适用于双引号。与其他字符，它的工作，但双引号（不）...
你建议哪些调整？任何建议将不胜感激。
*注意：实际数据超过100万条记录...
SCHEMA.INI
[Test_temp.csv]
ColNameHeader = false
Format = Delimited（;）
Col1 =product文字
Col2 =priceDouble
文字档案CSV：test01.txt
电视三星21WIDESCREEN LED; 170
电视PHILIPS 27WIDESCREEN LED; 200
高清SEAGATE 1TB 7200RPM; 150
代码VBA存取权：
Sub TableImport（）
Dim strSQL As String
Dim db As DAO.Database
Dim strFolder As String
strFolder = CurrentProject.Path
设置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
.close
End Sub
公共函数fncReplace（varStr As Variant）As String
如果IsNull（varStr）then
fncReplace =
Else
fncReplace = Replace（Trim（varStr），，''）
结束如果
结束函数
更新 - 工作 - 建议者：Andre451
Sub TableImport（）
Dim strSQL As String
Dim db As DAO。数据库
Dim strFolder As String
strFolder = CurrentProject.Path
设置db = CurrentDb
strSQL =DELETE FROM tbTest
db.Execute strSQL，dbFailOnError
Dim strFile As String
strFile = Dir（strFolder&\test * .txt，vbNormal）
$ b b 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
当读取csv文件时，双引号被解释为文本分隔符。在SCHEMA.INI中似乎没有办法明确告诉Access没有文本分隔符！。
所以我建议使用导入规范。您可以通过文本导入向导手动导入csv文件来创建导入规范，并将其保存。作为产品进口规格。有关详情，请参阅
然后，您将文本文件并从中导入数据：
Public Sub ImportProducts（）
Dim S As String
'将csv文件链接为临时表
DoCmd.TransferText acLinkDelim，产品导入规范，linkData，D：\temp\Test01.csv，False
'从临时表插入到产品表
S =INSERT INTO tbProduct（product，price）SELECT product，price FROM linkData
CurrentDb.Execute S
'删除临时表
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
打开cFile输出为＃1
For i = 1到1000000
打印＃1，测试字符串编号& CStr（i）& ; & CStr（i）
下一页i
关闭＃1
结束子
Public Sub ImportProducts（）
Dim S As String
Dim snTime As Single
snTime = Timer
'清理产品表
CurrentDb.ExecuteDELETE * FROM tbProduct
Debug.PrintDELETE：&定时器 - snTime
'将csv文件链接为临时表
DoCmd.TransferText acLinkDelim，产品导入规范，linkData，cFile，False
Debug.PrintTransferText： & Timer-snTime
'从临时表插入到产品表
S =INSERT INTO tbProduct（product，price）SELECT product，price FROM linkData
CurrentDb.Execute S
Debug.PrintINSERT：&计时器 - snTime
删除临时表
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上。
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
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.
这篇关于在Access VBA - INSERT INTO ... SELECT中转义双引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
