i own vba code below matter many text files access table. there problem cases of .txt files containing text double quotation marks , consequently breaks other fields of record null values.
i tried put replace function in selection of product field, did not work double quotes. other characters works, double quotes (no) ...
which adjustments recommend? suggestions appreciated.
*note: actual data more 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 string dim db dao.database dim strfolder string strfolder = currentproject.path set db = currentdb strsql = "delete tbtest" db.execute strsql, dbfailonerror dim strfile string strfile = dir(strfolder & "\test*.txt", vbnormal) until strfile = "" filecopy strfolder & "\" & strfile, strfolder & "\test_temp.csv" strsql = "" strsql = " insert tbtest(product,price)" strsql = strsql & " select fncreplace(product),price" strsql = strsql & " [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 variant) string if isnull(varstr) fncreplace = "" else fncreplace = replace(trim(varstr), """", "''") end if end function
update - worked - suggested by: andre451
sub tableimport() dim strsql string dim db dao.database dim strfolder string strfolder = currentproject.path set db = currentdb strsql = "delete tbtest" db.execute strsql, dbfailonerror dim strfile string strfile = dir(strfolder & "\test*.txt", vbnormal) until strfile = "" filecopy strfolder & "\" & strfile, strfolder & "\test_temp.csv" docmd.transfertext aclinkdelim, "specimportar", "linkdata", strfolder & "\test_temp.csv", false strsql = "" strsql = " insert tbtest(product,price)" strsql = strsql & " select product,price" strsql = strsql & " linkdata" db.execute strsql, dbfailonerror strfile = dir docmd.deleteobject actable, "linkdata" loop db.close end sub
when reading csv file, double quote interpreted text delimiter. in schema.ini there seems no way explicitly tell access "there no text delimiter!".
so suggest using import specification instead. create import specification importing csv file once manually via text import wizard, , saving e.g. "product import specification". details see 1. in this answer.
in specification, set "none" text delimiter. in german access:
then link text file , import data it:
public sub importproducts() dim s string ' link csv file temp table docmd.transfertext aclinkdelim, "product import specification", "linkdata", "d:\temp\test01.csv", false ' insert temp table product table s = "insert tbproduct (product, price) select product, price linkdata" currentdb.execute s ' remove temp table docmd.deleteobject actable, "linkdata" end sub edit:
i created csv file 1.000.000 lines (36 mb) , used import file:
const cfile = "g:\test.csv" public sub createcsv() dim s string dim long open cfile output #1 = 1 1000000 print #1, "testing string number " & cstr(i) & ";" & cstr(i) next close #1 end sub public sub importproducts() dim s string dim sntime single sntime = timer ' clean product table currentdb.execute "delete * tbproduct" debug.print "delete: " & timer - sntime ' link csv file temp table docmd.transfertext aclinkdelim, "product import specification", "linkdata", cfile, false debug.print "transfertext: " & timer - sntime ' insert temp table product table s = "insert tbproduct (product, price) select product, price 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 autonumber field primary key tbproduct:
transfertext: 0,6640625 insert: 8,023438 8 seconds not slow.
make sure both access database , imported csv file on local disk, not on network drive. if possible, on ssd.
Comments
Post a Comment