473,242 Members | 3,171 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,242 software developers and data experts.

import txt into database

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
Jul 19 '05 #1
4 9954
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" <du******@yahoo.com> wrote in message
news:P4**********************@news01.bloor.is.net. cable.rogers.com...
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

Jul 19 '05 #2
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$**************@TK2MSFTNGP10.phx.gbl...
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" <du******@yahoo.com> wrote in message
news:P4**********************@news01.bloor.is.net. cable.rogers.com...
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





Jul 19 '05 #3
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" <du******@yahoo.com> wrote in message
news:q2********************@news01.bloor.is.net.ca ble.rogers.com...

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")

Jul 19 '05 #4
'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:ee**************@TK2MSFTNGP11.phx.gbl...
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" <du******@yahoo.com> wrote in message
news:q2********************@news01.bloor.is.net.ca ble.rogers.com...

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")


Jul 19 '05 #5

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

Similar topics

4
by: Steve Holden | last post by:
I'm trying to load module code from a database, which stores for each module its full name, code, load date and a Boolean indicating whether it's a package or not. The following simple program:...
5
by: Steve Holden | last post by:
This is even stranger: it makes it if I import the module a second time: import dbimp as dbimp import sys if __name__ == "__main__": dbimp.install() #k = sys.modules.keys() #k.sort() #for...
1
by: Xif | last post by:
Hello Everyone! Here's a problem with relative imports: Suppose I have a package called some_package (in a separate directory included in the PYTHONPATH, with an __init__.py file etc.) This...
3
by: Doug Baroter | last post by:
Hi, One of my clients has the following situation. They use Access DB for data update etc. some business functions while they also want to view the Access data quickly and more efficiently in...
15
by: DesignGuy | last post by:
I've inherited a site that has 1000+ product pages that follow a similar layout. I would like to import these pages into a database for ease of future updating. The pages have the usual banners,...
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...
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...
3
by: Iavor Raytchev | last post by:
Hello, We a situation with a central database that contains the data that needs to be presented at N off-line terminals (N can be 5 000 can be 15 000). Each terminal presents unique data. The...
0
by: A3AN | last post by:
Hi. I receive a database backup on a daily basis. I then import this dump on another server which I use for software development. There is two db's being hosted on this server. We test software...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
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
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...

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.