How To Open Delimited Text Files Using the Jet Provider's Text IIsam

This article discusses how to open a delimited text file by using ADO and the Microsoft Jet Provider's Text IIsam driver. In this article, the Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) is used to create a table to accept the text file.

A form is created in the following steps. There are two command buttons on the form. The first button provides two methods for opening a text file; the second button demonstrates how to import a text file into an existing table.

Steps to Accomplish Behavior

1. Create a text file called textfile.txt that contains code similar to the following:

   "F1","F2","F3"
   "one", "two", "three"
   "1a","2b", "3c"
   1,2,3
   					

2. Create a new Standard EXE in Visual Basic. Form1 is created by default.
3. On the Project menu, click to select References, and then select Microsoft Active Data Objects and Microsoft ADO Ext.2.x for DDL and Security.
4. Add two CommandButton controls to the form, and name them CmdOpen and CmdInsert.
5. Add the following code to the form. You need to change the paths to reflect locations on your machine.

   Dim cn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim PathtoTextFile As String
   Dim PathtoMDB As String
   Dim myarray() As Variant
   Private Sub Form_Load()
     PathtoTextFile = "C:\PathtoTextFile\"
     PathtoMDB = "C:\PathtoMDB\"
     CmdOpen.Caption = "Open textfile and display field value"
     CmdInsert.Caption = "Insert textfile values into MDB"
   End Sub
   Private Sub CmdOpen_Click()
     '============================method 1=================================
     cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & PathtoTextFile & ";" & _
             "Extended Properties=""text;HDR=YES;FMT=Delimited"""
     rs.Open "select * from TextFile.txt", _
             cn, adOpenStatic, adLockReadOnly, adCmdText
     '============================method 2=================================
     'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & PathtoMDB & "Northwind.mdb"
     'rs.Open "SELECT * FROM [Text;Database=" & PathtoTextFile & ";" & _
             "HDR=YES;FMT=Delimited].[textfile.txt]", _
             cn, adOpenStatic, adLockReadOnly, adCmdText
     '=====================================================================
     MsgBox rs(0)
     rs.Close
     cn.Close
   End Sub
   Private Sub CmdInsert_Click()
     Set Cat = New ADOX.Catalog
     Set objTable = New ADOX.Table
     cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & PathtoMDB & "Northwind.mdb"
     'Open the Catalog
     Set Cat.ActiveConnection = cn
     'delete the table if it exists
     On Error Resume Next
     Cat.Tables.Delete "table1"
     'Create the table
     objTable.Name = "Table1"
     'Create and Append a new fields to the "table1" Columns Collection
     objTable.Columns.Append "F1", adWChar
     objTable.Columns.Append "F2", adWChar
     objTable.Columns.Append "F3", adWChar
     
     Cat.Tables.Append objTable
     
     'Insert into table1 the contents of textfile.txt
     cn.Execute "INSERT INTO Table1 SELECT * FROM " & _
                "[Text;Database=" & PathtoTextFile & ";HDR=YES].[TextFile.txt]"
     cn.Close
     MsgBox "Finished Inserting into MDB"
   End Sub
   					

6. Run the code. The Cmdopen command opens the text file and displays one. The CmdInsert command creates table1 in the Northwind database and inserts the information in textfile.txt into table1. Comment the code in method 1, uncomment the code in method 2, and then run again the code.

The Jet Database Engine Programmer's Guide (2nd Edition), Microsoft Press By Dan Haught, Jim Ferguson (ISBN 1-57231-342-0)