My original project task was pretty straight forward. I had a number of .csv files generated by SysMon/Perfmon and needed to import them into a MS-SQL 2005 database for analysis. As you know, the .csv file output by Perfmon has a linear format rather than a relational one. Meaning that each record/line in the .csv file is formatted using:

"Datetime of the sample", "\\<hostname>\<CounterClass1>\<CounterName1>", "\\<hostname>\<CounterClass2>\<CounterName2>", "\\<hostname>\<CounterClass3>\<CounterName3>", "\\<hostname>\<CounterClass4>\<CounterName4>", "\\<hostname>\<CounterClass(n...)>\<CounterName(n...)>",

So, the question became, if you have a text file that is comma delimited (or delimited by some character) and you want to open and read it using ADO.NET. What "Provider", connection string, and "Extended Properties" do you use to access a text file with?

Um...This was tricky at first, but once I found the answer, it turned out be unbelievably simple. The problem (as usual) was trying to get/find the techinical information from the manufacturer itself (Ie. Microsoft). I probably spent an entire day searching the MSDB, MS-TechNect, Support, Help files, etc., trying to find the answer.

The short answer is, Microsoft ADO.NET does NOT have a "provider" for text files. Instead, you have to use the "OLEDB" provider. Once I figured this out, it then became a battle to try and find a list of "Extended Properties" for a text file connection string. Which, as usual with Microsoft, is nowhere to be (easily) found.

So...Here's how..."

• Build the ConnectionString for a text file using the following:
  • Create a ConnectionString variable Dim cnStr as string
  • Set its provider to Microsoft JET OLEDB
    Provider=Microsoft.Jet.OLEDB.4.0
  • Set the "Data Source" to the DIRECTORY where the file is located
    Data Source=C:\MyDir\Perfmon\Files
  • Set the "Extended Properties" to those neccessary for reading Text files:
    "Extended Properties='text;HDR=YES;FMT=CSVDelimited'" Note: The values used for the Extended Proeprties must be contained within quotes. Since VB.Net uses double quotes(") for string assignments, you can use single-quotes (') to wrap the extended property values.
    (More about "Extended Properties" for text file connection strings later...)
  • The final result will look like this:
    

    cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    	"Data Source=C:\MyDir\Perfmon\Files;" & _
    	"Extended Properties='text;HDR=YES;FMT=CSVDelimited';" 
    

• Create an Oledb.OledbConnection and pass the text file connectionString to it
  Using myCN as New System.Data.Oledb.OledDBConncetion(cnStr)
• Open the connection..
  cn.Open()
• Create the QueryString "SELECT" statement that will be used to read the file. If you remember, in the text file ConnectionString you set the "Data Source" to a DIRECTORY. The way you "read" a text file in ADO.NET is by issuing an SQL style "SELECT" against it. To read the file "myfile.txt" you would create a QueryString as follows:
  dim strQuery = "SELECT * FROM [myfile.txt]" Note: ALWAYS enclose the file name in brackets []
  SELECT * FROM [My-File-Name from+@Hell_!_.txt] If the file name contains spaces or other special characters such as "-,+,*,%" etc., and it is NOT enclosed in brackets, the select statement will generate an OleDB error attempting to open or read the file such as: Syntax error in FROM clause.
• Create a new Oledb.OledbCommand passing the Connection object and QueryString to it:
  Using myCmd As New System.Data.Oledb.OldbCommand(cn,strQuery)
• Create an Oledb.OledDbDataReader for reading the file:
  Note: You could use a DataAdapter/DataSet combo, but, if the text file is large, you will consume a LOT of system resources. For my task, since I'm just going to read the file in line-by-line and process the results, a datareader is just fine. (More efficient, and probably faster.)
  Dim myReader as OleDb.OleDbDataReader
• Initialize/open the DataReader using the command object:
  myReader = myCmd.ExecuteDataReader()
• Using the DataReader, just loop through reading and processing the file line-by-line.
  Note: If the first row had "Column Headers" and you set the "Extended Properties" to expect such, then you can reference the columns by thier name. Otherwise, you'll have to use their ordinal value.
  

  Using myCMD As New OleDb.OleDbCommand(strQuery,myCN)
  	'Init the reader
  	myReader = myCMD.ExecuteReader()
  	While myReader.Read
  		xDate = myReader(0)
  		xVal1 = myReader(1)
  		xVal2 = myReader(2)
  		xVal3 = myReader(3)
  		xValn... = myReader(n..)
  		'Process data.....
  	End While
  End Using
  	

Voila! You can now use ADO.NET/VB.NET to read and process a text file with a DataReader (or DataAdapter/DataSet combo..)

BEWARE! Depending on the file extension, you might receive the following error when you attempt to open and read a text file:

For security reasons, the latest service pack for Jet (by default) allows only text files with the following extensions to be imported:

• .txt
• .csv
• .tab
• .asc
• .htm
• .html

I hope this helps you in your endeavors. Naturally, MicroSoft was a hinderance by failing to provided any easily reachable examples or documentation. But then, when you are a unscrupulous monopoly, what does customer service matter?

Here is a link to the list of extended properties I've discovered so far: Extended Properties