import txt into database

[atse]

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]

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]

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]

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]

'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