Hi,
I am still stick on the text file being imported to database. Can anybody help?
I have just done the csv format files. And then I work on text and DAT formats, but I have problem to import these kinds of formats to MySQL ( and MS SQL)
Please see the attachments, and the text format is changed from DAT format, delimited by tabs or commas. I can't select and insert them into the database.
When I try:
select * from thisfile.txt and then insert these records into a designated table of the database.
the error message is:
Microsoft JET Database Engine error '80040e14'
Syntax error in FROM clause.
/insertfile.asp, line 46
' line 46 is
set rs = conn.execute("select * from thisfile.txt")
Thanks for any idea.
Atse
'below is the code of what I am going to do with a text file. Please ignore the typing errors and return lines when I make something changed from the original codes.(see the attachment "code.txt" if here the code messes). If the file is csv (thisfile.csv), it works fine.
'thisfile.txt is a text file, and I am using MySQL
sConnection="driver={MySQL ODBC 3.51 Driver};server=localhost;database=upload;uid=root; "
set conn=server.CreateObject("ADODB.Connection")
conn.open(sConnection)
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myPath & "\;Extended Properties=""text;HDRNo;FMT=Delimited"""
set conn2=server.CreateObject("ADODB.Connection")
conn2.open(constr)
myPath="D:\uploadtemp\"
file_name = "thisfile.txt"
fileStr="select * from " & file_name
set dRs=conn2.execute(fileStr) 'line 46
' insert the recordset into the database.
do until dRs.eof
insertStr="insert into file (filename "
for i = 1 to dRs.Fields.Count
insertStr=insertStr &", f" & i
next
insertStr = insertStr &") "
insertStr = insertStr & "values ('"&file_name&"'"
for k = 1 to dRs.Fields.Count
fieldata = dRs(k-1)
insertStr = insertStr &", '" & fieldata & "'"
next
insertStr = insertStr &") "
conn.Execute(insertStr)
dRs.MoveNext
loop
dRs.close : set dRs = nothing
conn.Close : set conn=nothing
conn2.Close : set conn2 = nothing