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
All you need is to wrap double-quotes in single-quotes:
That said, I would find it easier to first link the file as a table, then use the linked table as source.
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:
Edit:
I created a csv file with 1.000.000 lines (36 MB) and used that as import file:
Result:
After adding an autonumber field as primary key to tbProduct:
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.
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?