Rated out of 5 stars (4.3137) - 51 Total Votes

Submit Your ASP Article

Importing a text file to an Access database.

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 vatiables.

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.

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 dont 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.

Do While objFileTextStream.AtEndOfStream <> True  Keep reading each line until we reach the end of the file (objFileTextStream.AtEndOfStream = True)
  strLine = objFileTextStream.ReadLine  read the current line and assign it to a variable
  strLinePart = split(strLine,"|")  Split each part of the line using the delimiter
  for i = 0 TO UBound(strLinePart)  Loop through each part of the line.
    If i = 0 Then
      strFirstName = Trim(strLinePart(i))  If we are at the first part of the line then assign the variable
    ElseIf i = 1 Then
      strLastName = Trim(strLinePart(i))  Line part 2 same thing
    ElseIF i = 2 Then
      strGender = Trim(strLinePart(i))  Line part 3
    ElseIF i = 3 then
      intAge = Trim(strLinePart(i))  Line part 4
    End If
  Next

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

  intAge = Cint(intAge)  If the DataType for the AGE field in the database is an integer then we do this.
  strSQL = "INSERT INTO tbl_people ([FNAME],[LNAME],[SEX],[AGE]) Values('"&strFirstName&"','"&strLastName&"','"&strGender&"','"&intAge&"')"
  conn.Execute strSQL
Loop  Loop through the rest of the text file and enter the info into the database.

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 cant 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