Microsoft KB Archive/262537

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

Article ID: 262537

Article Last Modified on 7/1/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q262537



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



MORE INFORMATION
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
  Create a text file called textfile.txt that contains code similar to the following: &quot;F1&quot;,&quot;F2&quot;,&quot;F3&quot; &quot;one&quot;, &quot;two&quot;, &quot;three&quot; &quot;1a&quot;,&quot;2b&quot;, &quot;3c&quot; 1,2,3                    Create a new Standard EXE in Visual Basic. Form1 is created by default. 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. Add two CommandButton controls to the form, and name them CmdOpen and CmdInsert .  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 = &quot;C:\PathtoTextFile\&quot; PathtoMDB = &quot;C:\PathtoMDB\&quot; CmdOpen.Caption = &quot;Open textfile and display field value&quot; CmdInsert.Caption = &quot;Insert textfile values into MDB&quot; End Sub Private Sub CmdOpen_Click '============================method 1================================= cn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=&quot; & PathtoTextFile & &quot;;&quot; & _ &quot;Extended Properties=&quot;&quot;text;HDR=YES;FMT=Delimited&quot;&quot;&quot; rs.Open &quot;select * from TextFile.txt&quot;, _ cn, adOpenStatic, adLockReadOnly, adCmdText '============================method 2================================= 'cn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=&quot; & PathtoMDB & &quot;Northwind.mdb&quot; 'rs.Open &quot;SELECT * FROM [Text;Database=&quot; & PathtoTextFile & &quot;;&quot; & _ &quot;HDR=YES;FMT=Delimited].[textfile.txt]&quot;, _ 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 &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=&quot; & PathtoMDB & &quot;Northwind.mdb&quot; 'Open the Catalog Set Cat.ActiveConnection = cn 'delete the table if it exists On Error Resume Next Cat.Tables.Delete &quot;table1&quot; 'Create the table objTable.Name = &quot;Table1&quot; 'Create and Append a new fields to the &quot;table1&quot; Columns Collection objTable.Columns.Append &quot;F1&quot;, adWChar objTable.Columns.Append &quot;F2&quot;, adWChar objTable.Columns.Append &quot;F3&quot;, adWChar Cat.Tables.Append objTable 'Insert into table1 the contents of textfile.txt cn.Execute &quot;INSERT INTO Table1 SELECT * FROM &quot; & _ &quot;[Text;Database=&quot; & PathtoTextFile & &quot;;HDR=YES].[TextFile.txt]&quot; cn.Close MsgBox &quot;Finished Inserting into MDB&quot; End Sub  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.</ol>

<div class="references_section">