import txt into database

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

  1. atse

    atse Guest

    Hi,

    I still have problem to import the text file delimited by tabs or commas
    into the database.
    I refer to http://www.connectionstrings.com/ for the connection, ODBC
    doesn't work and OLD DB works only for csv files. Any idea? Does anybody has
    workable samples? Thanks a lot.

    ODBC
    Standard:
    "Driver={Microsoft Text Driver (*.txt;
    *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;"

    OLE DB
    Standard:
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended
    Properties=""text;HDR=Yes;FMT=Delimited"""

    Atse
     
    atse, Oct 14, 2003
    #1
    1. Advertising

  2. atse

    Ray at Guest

    Ah, are you moving your data out of the text files and migrating to a DB
    now? Outstanding! What kind of troubles are you having, and if relevant,
    what does the data in the text file look like? Also, what kind of DB are
    you importing into?

    Ray at work

    "atse" <> wrote in message
    news:p4_ib.334944$...
    > Hi,
    >
    > I still have problem to import the text file delimited by tabs or commas
    > into the database.
    > I refer to http://www.connectionstrings.com/ for the connection, ODBC
    > doesn't work and OLD DB works only for csv files. Any idea? Does anybody

    has
    > workable samples? Thanks a lot.
    >
    > ODBC
    > Standard:
    > "Driver={Microsoft Text Driver (*.txt;
    > *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;"
    >
    > OLE DB
    > Standard:
    > "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended
    > Properties=""text;HDR=Yes;FMT=Delimited"""
    >
    > Atse
    >
    >
     
    Ray at, Oct 14, 2003
    #2
    1. Advertising

  3. atse

    atse Guest

    Thanks for your coming again, Ray.
    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 again for your further help.

    Atse

    "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
    news:O$...
    > Ah, are you moving your data out of the text files and migrating to a DB
    > now? Outstanding! What kind of troubles are you having, and if relevant,
    > what does the data in the text file look like? Also, what kind of DB are
    > you importing into?
    >
    > Ray at work
    >
    > "atse" <> wrote in message
    > news:p4_ib.334944$...
    > > Hi,
    > >
    > > I still have problem to import the text file delimited by tabs or commas
    > > into the database.
    > > I refer to http://www.connectionstrings.com/ for the connection, ODBC
    > > doesn't work and OLD DB works only for csv files. Any idea? Does anybody

    > has
    > > workable samples? Thanks a lot.
    > >
    > > ODBC
    > > Standard:
    > > "Driver={Microsoft Text Driver (*.txt;
    > > *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;"
    > >
    > > OLE DB
    > > Standard:
    > > "Provider=Microsoft.Jet.OLEDB.4.0;Data

    Source=c:\txtFilesFolder\;Extended
    > > Properties=""text;HDR=Yes;FMT=Delimited"""
    > >
    > > Atse
    > >
    > >

    >
    >
     
    atse, Oct 15, 2003
    #3
  4. atse

    Ray at Guest

    What is "thisfile.txt?" Is that the name of a table in an Access database,
    or is that your filename? What is your code?

    Ray at home

    "atse" <> wrote in message
    news:q20jb.336869$...

    >
    > 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")
    >
    >
     
    Ray at, Oct 15, 2003
    #4
  5. atse

    atse Guest

    'thisfile.txt is a text file, and I am using MySQL but not Access

    'below is the code of what I am going to do with a text file. Please ignore
    the typing errors when I make something changed from the original codes. If
    the file is csv (thisfile.csv), it works fine.

    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)

    ' 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



    "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
    news:...
    > What is "thisfile.txt?" Is that the name of a table in an Access

    database,
    > or is that your filename? What is your code?
    >
    > Ray at home
    >
    > "atse" <> wrote in message
    > news:q20jb.336869$...
    >
    > >
    > > 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")
    > >
    > >

    >
    >
     
    atse, Oct 15, 2003
    #5
    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. SAH
    Replies:
    7
    Views:
    5,882
    Adrienne
    Sep 9, 2003
  2. Sameen
    Replies:
    2
    Views:
    446
    Victor Bazarov
    Aug 29, 2005
  3. Jochen Brenzlinger
    Replies:
    7
    Views:
    5,577
    Roedy Green
    Sep 15, 2011
  4. Mike Turco
    Replies:
    0
    Views:
    138
    Mike Turco
    Aug 24, 2006
  5. WorkerBee
    Replies:
    3
    Views:
    194
Loading...

Share This Page