ProjectSMM.com
Gonzo TechNet

ADO.NET

Issue(s):
How to use ADO.NET to read a text file.
How to open a text file using ADO.NET.
How to open/read a CSV/text file with an ADO.NET DataReader or DataAdapter.
What is the connection string/ConnectionString used to open/read a CSV/text file with an ADO.NET DataReader or DataAdapter
What are the "Extended Properties" for an ADO.NET connection to open a Text file?
How to read the PerfMon .csv output file and import it into an MS-SQL 2005 database.
Description:

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?

Solution:

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

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

Possible problems

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

"Can't update. Database or object is read-only"

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

Or,You can update the registry to allow the extension you are using (yeah, right! Iíll be sure to do that in my app for EVERY extension I encounter)

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

Home | TechNet | ADO.Net | DOS | ASP.NET | IIS | VB.NET | VIM (vi) | Windows | XHtml
MS-SQL | T-SQL | SSIS | Oracle