Text file to MySQL

[telmessos]

Hi all,

I need a simple ASP script to move some information from a txt file to a mysql database. The information on the text file is separated by tabs. I want to create a loop to split the each line from the tabs and write each line's result to MySQL database. I tried it but couldn't make it work.

Can anyone send me an example code with some dummy database field names?

Thanks
telmessos

Note: There are 5 tabs on each line and as you can understand 6 fields on MySQL database.

[Old Pedant]

A couple of ways to do this, but one way:

Set fso = Server.CreateObject("Scripting.FileSystemObject")
Set infile = fso.OpenTextFile( Server.MapPath("/virtual/path/to/the/file.txt") )

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "...your mysql connection string ..."

Do Until infile.AtEndOfStream
line = infile.ReadLine
fields = Split( line, vbTab )
If UBound(fields <> 5 ) Then
Response.Write "Did not find 5 tabs in this line: " & Join( fields, "::" ) & "<hr>"
Else
line = Replace( line, "'", "\'" ) ' excape apostrophes in MySQL style
line = "'" & Replace( line, vbTab, "','" ) & "'" ' put in the needed apostrophes and commas
SQL = "INSERT INTO table ( field1, field2, field3, field4, field5, field6 ) " _
& " VALUES(" & line & ")"
' comment out next line after testing:
Response.Write "DEBUG SQL: " & SQL & "<br/>" & vbNewLine
conn.Execute SQL
End If
Loop
infile.close
conn.close
%>

[telmessos]

Thanks Old Pedant,

Do you also know how do I start from a specific line of the text file ? For example:

Instead of first line start from 100.000th line

Thanks
telmessos

[Old Pedant]

Sure.

Set fso = Server.CreateObject("Scripting.FileSystemObject")
Set infile = fso.OpenTextFile( Server.MapPath("/virtual/path/to/the/file.txt") )

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "...your mysql connection string ..."

linecount = 0
Do Until infile.AtEndOfStream
line = infile.ReadLine
linecount = linecount + 1
If linecount >= 100000 Then
fields = Split( line, vbTab )
If UBound(fields <> 5 ) Then
Response.Write "Did not find 5 tabs in this line: " & Join( fields, "::" ) & "<hr>"
Else
line = Replace( line, "'", "\'" ) ' excape apostrophes in MySQL style
line = "'" & Replace( line, vbTab, "','" ) & "'" ' put in the needed apostrophes and commas
SQL = "INSERT INTO table ( field1, field2, field3, field4, field5, field6 ) " _
& " VALUES(" & line & ")"
' comment out next line after testing:
Response.Write "DEBUG SQL: " & SQL & "<br/>" & vbNewLine
conn.Execute SQL
End If
End If
Loop
infile.close
conn.close
%>

No, there's really not any simpler way. Text files do *NOT* support random positioning. Nothing to do with ASP, per se.

[telmessos]

thanks. that will help. ;)