ASP Article

Importing a text file to a database

This article will explain how to import the contents of a text file (.txt) when using MS text or OLE DB Drivers wont do! We will be using the FileSystem and TextStream objects for this lesson!

This article assumes you have basic knowledge of HTML and ASP

For the purposes of this article we will be using a text file delimited using the "|" symbol.
Here is the contents of the text file we will be using - info.txt.

We will be importing the text file to a database called "people.mdb" with one table called "tbl_people"
consisting of 4 fields - "FNAME", "LNAME", "SEX", "AGE"

Now on with the article!

First lets declare some variables.

                    
Dim conn, fs, objFile, objFileTextStream, i, strSQL
Dim strLine, strLinePart, strFirstName, strLastName, strGender, intAge
                    
                

Next lets open a connection to the database we will be using.

                    
Set conn = Server.CreateObject("ADODB.Connection") 
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("people.mdb")&";User Id=Admin;Password="
                    
                

Now we need to create a FileSystem Object to handle the text file.

                    
Set fs = Server.CreateObject("Scripting.FileSystemObject")
                    
                

Let's get the file and open it.

                    
Set objFile = fs.GetFile("info.txt") Set objFileTextStream = objFile.OpenAsTextStream(1, 2)
                    
                

Since the first line of the text file is a header, which we don't want to enter into the database, we will skip that line.

                    
objFileTextStream.skipLine
                    
                

Now we will loop through lines of the text file and read each line into the database.

Keep reading each line until we reach the end of the file.

                    
Do While objFileTextStream.AtEndOfStream <> True
                    
                

Read the current line and assign it to a variable.

                    
strLine = objFileTextStream.ReadLine
                    
                

Split each part of the line using the delimiter.

                    
strLinePart = split(strLine,"|")
                    
                

Loop through each part of the line.

                    
for i = 0 TO UBound(strLinePart)
                    
                

If we are at the first part of the line then assign the variable.

                    
If i = 0 Then strFirstName = Trim(strLinePart(i))
                    
                

Line part 2 same thing.

                    
ElseIf i = 1 Then strLastName = Trim(strLinePart(i))
                    
                

Line part 3.

                    
ElseIf i = 2 Then strGender = Trim(strLinePart(i))
                    
                

Line part 4.

                    
ElseIf i = 3 then intAge = Trim(strLinePart(i))
                    
                

Now that we have each part of the first line set in variables we can enter them into the database.

                    
  End If 
Next
                    
                

The DataType for the AGE field in the database is an integer.

                    
intAge = Cint(intAge)
                    
                

Loop through the rest of the text file and enter the info into the database.

                    
  strSQL = "INSERT INTO tbl_people ([FNAME],[LNAME],[SEX],[AGE]) Values('"&strFirstName&"','"&strLastName&"','"&strGender&"',"&intAge&")" 
  conn.Execute strSQL 
Loop
                    
                

Lets not forget to clean up our mess!

                    
objFileTextStream.Close: Set objFileTextStream = Nothing 
Set fs = Nothing conn.Close: Set conn = Nothing
                    
                

I have probably forgotten something (I usually do!) though I can't imagine what because this works for me!
I suppose some ASP Developers might do it a little different but this is the way I do it and it serves my purposes and I hope yours too!

Here is what the whole thing looks like with out all the comments

                    
Dim conn, fs, objFile, objFileTextStream, i, strSQL
Dim strLine, strLinePart, strFirstName, strLastName, strGender, intAge
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("people.mdb")&";User Id=Admin;Password="
Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set objFile = fs.GetFile("info.txt")
Set objFileTextStream = objFile.OpenAsTextStream(1, 2)
objFileTextStream.skipLine
Do While objFileTextStream.AtEndOfStream <> True
  strLine = objFileTextStream.ReadLine
  strLinePart = split(strLine,"|")
  For i = 0 TO UBound(strLinePart)
    If i = 0 Then
      strFirstName = Trim(strLinePart(i))
    ElseIf i = 1 Then
      strLastName = Trim(strLinePart(i))
    ElseIf i = 2 Then
      strGender = Trim(strLinePart(i))
    ElseIf i = 3 then
      intAge = Trim(strLinePart(i))
    End If
  Next
  intAge = Cint(intAge)
  strSQL = "INSERT INTO tbl_people ([FNAME],[LNAME],[SEX],[AGE]) Values('"&strFirstName&"','"&strLastName&"','"&strGender&"','"&intAge&"')"
  conn.Execute strSQL
Loop
objFileTextStream.Close: Set objFileTextStream = Nothing
Set fs = Nothing
conn.Close: Set conn = Nothing
                    
                

That's all there is to it! Happy Coding!