Importing a text file to an Access database.

Rated out of 5 stars (4.3636) - 55 Total Votes

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!

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.

Text File

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
                      

About the Author

Steve Frazier has been a classic ASP developer since 2003. He has developed CLASP applications for Fortune 500 companies and popular website's. He has also developed many ASP Scripts of his own! He is Web-master of HTMLJunction as well as its sister site - ASP Junction. He is currently working on a Web Portal that has the functionality of all the most popular Forums and Portals.

I hope you found this article useful!
Rate this Article
Good
5
4
3
2
1
Poor