467,879 Members | 1,175 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,879 developers. It's quick & easy.

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
  • viewed: 9747
Share:
4 Replies
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Steve Holden | last post: by
5 posts views Thread by Steve Holden | last post: by
15 posts views Thread by DesignGuy | last post: by
2 posts views Thread by David Berry | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.