473,322 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

import a text file to database

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
Jul 19 '05 #1
0 4784

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Bethany Holliday | last post by:
Hi all, I have a file with an extension of .sdf. I "believe" it is a text file of some sort but I am uncertain. The source agency hasn't returned any of my calls so I'm wondering if anyone is...
4
by: news | last post by:
Our production database in an exported textfil runs about 60 MB. Compressed that's about 9 MB. I'm trying to import the export into another machine running FC3 and mySQL 11.18, and it appears as...
2
by: David Berry | last post by:
Hi All. I'm looking for any help or sample code that can show me how to make a file import wizard in ASP.NET (VB preferred) like the one that MS Access uses. I'm working on a web site where the...
1
by: Dan | last post by:
Could someone please help me with auto importing a series of data files into an Access table. I tried to follow code given below in a previous messagebut i'm getting error messages. Here's my...
5
by: Johnny Meredith | last post by:
I have seven huge fixed width text file that I need to import to Access. They contain headers, subtotals, etc. that are not needed. There is also some corrupt data that we know about and can...
4
by: Steve Jorgensen | last post by:
I'm restarting this thread with a different focus. The project I'm working on now id coming along and will be made to work, and it's too late to start over with a new strategy. Still, I'm not...
1
by: Child of His | last post by:
I have been through every trick I know, or has been suggested. I have a one to two million line fixed field database in text format. I want to bring it into Access 97. When I use the external...
7
by: Pete | last post by:
I need to import multiple tab delimited files for which I do not have the formats. All files have column headers in the the line. The files must be dynamic. The only common value is that the...
9
by: a | last post by:
Dear friends I want import data from CSV file to mdb file How can I do that in vb.net?
6
by: provor | last post by:
Hello, I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.