import a text file to database

Discussion in 'ASP General' started by atse, Oct 20, 2003.

  1. atse

    atse Guest

    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
    atse, Oct 20, 2003
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,371
    Carl Prothman [MVP]
    Jan 12, 2005
  2. Paul Opal
    Replies:
    12
    Views:
    934
    Paul Opal
    Oct 11, 2004
  3. JPractitioner
    Replies:
    13
    Views:
    20,127
    Roedy Green
    Feb 24, 2006
  4. bienwell
    Replies:
    2
    Views:
    4,115
    bienwell
    Jun 17, 2006
  5. Max2006
    Replies:
    4
    Views:
    592
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
    Oct 9, 2007
Loading...

Share This Page