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