Loading Text Files Into A Database

Sometimes its very convenient to upload a text file and load it into your database using ASP. I do this for a mailing list of 3500-4500 records that changes each week. Originally I had to download the database, import the list in Access, and then upload the database, which was really a pain in the rear. Being basically lazy (ha!), and always short of time, I decided that automating the loading process would be a good thing, so I set out to create a script to do the work for me.

Here's a sample of the space-delimited file I get:

kathi kathi@attitude.com http://www.attitude.com/users/kathi/
jestme jestme@joke.com http://www.joke.com/jestme
rover redrover@comeover.com http://www.woof.com 

I use an include file to set my connection and open it. Then the fun begins! :)

<%
set rs=server.createobject("adodb.recordset")
rs.open "WelcomeSV",conn,3,3
Set fs = CreateObject("Scripting.FileSystemObject")
filename=server.mappath("/attitude/geo/sv.txt")
Set readfile=fs.OpenTextFile(filename,1,False)
Do while not readfile.atendofstream
    on error resume next
    Text=readfile.readline
    MemberInfo = split(Text, " ", 3)
    rs.addnew
    rs("member")=MemberInfo(0)
    rs("email")=MemberInfo(1)
    rs("site")=MemberInfo(2)
    rs.update
    count=count+1
loop
readfile.close
set readfile=nothing
set fs=nothing
rs.close 
set rs=nothing
conn.close
set conn=nothing
response.write "<center><b>" & Count & " records were loaded</b></center>"
%>
  

The first five lines are standard fare - setting the connection object, opening up a recordset (in this case, I'm going to be adding new records to the database, so I'm opening up the table "WelcomeSV" instead of doing a SELECT query), setting the filesystemobject object, and specifying the path to the textfile and opening the textfile for writing. I don't want to create a new file if the text file isn't there, so I set it to False - that way I'll get a nasty little error so I know there's a problem.

The next line starts a Do...While...Loop statement. What I'm doing here is looping through the text file, reading it line by line. Next, note that I'm using the SPLIT function. This is really a cool function that allows a delimited string to be broken up into individual substrings. The syntax for this is:

Split(expression[, delimiter[, count[, compare]]])

In simpler terms, "expression" is the string to split, delimiter is optional - by default, its a space but could be a comma or other character, count is the number of substrings to be returned (optional), and compare is the type of comparison - either binary (value 0) or textual (value 1) - also optional. Since I wanted 3 substrings, I threw the 3 in the split, but since my delimiter is a space, I left that out. Come to think of it, I could have used just SPLIT(Text) and it would work.

Next, I add the values of each substring to the appropriate field in the recordset, then update the recordset. The count was kind of an afterthought - I wanted to keep track of how many entries I had each week. Finally, the loop is ended, and everything closed and objects destroyed. The final line gives me a count of the records added.

I'm not sure of the upper limit to the number of records you can add this way. I tested using 22,000 records and that worked well, but it bombed out above 48,000 records because the script timed out. I suppose I could have tried setting the script timeout a bit higher to see if that would help, but really, 48,000 records *should* keep you busy for awhile ;)