Please note, this is a STATIC archive of website www.htmlgoodies.com from 16 Nov 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
Wednesday, November 16, 2022

Connect to CSV Data Files using Microsoft Data Access Components

The Microsoft Jet (Joint Engine Technology) Database Engine was created in 1992 for Microsoft Access. Although it has been incorporated into several Microsoft products since then, it’s still synonymous with the Microsoft Access product line. The driver that allows programs and scripts to interface with the JET engine is called the Jet OLE DB Provider (where OLE refers to Microsoft’s Object Linking and Embedding technology).

It’s one of several Microsoft Data Access Components (commonly abbreviated to MDAC), a framework of Microsoft technologies that presents programmers with a consistent way of developing applications that can interact with almost any data store. Other MDAC technologies include ActiveX Data Objects (ADO) and Open Database Connectivity (ODBC).

In a recent article, we used the Access Export Wizard as a means of generating a Comma-separated-Values (CSV) file. In today’s follow-up, we’re going to construct some connection strings that will allow us to perform queries against the contents of the CSV file using the various Microsoft data providers.

The Who’s Who of MDAC Technologies

OLE DB Providers

The OLE DB providers were created to access everything from databases such as Access, Oracle, and SQL Server, to simple data stores such as Excel spreadsheets, and of course, text files. The Microsoft OLE DB Provider for Jet is by far the most utilized of these. The OleDb .NET data provider provides access to OLE DB providers with the help of native OLE drivers. If you installed any Microsoft Office applications or Access 2000 and above you will have the MDAC drivers. Otherwise, you can download version 2.8 from the Microsoft site

ODBC .NET

The ODBC .NET data provider delivers access to ODBC data sources with the help of native ODBC drivers. One of the best things about working with ADO.NET data providers is all data providers define the similar class hierarchy. The only things you need to change are the classes and the connection string. Unlike the OleDb data providers, the ODBC data provider is an add-on component to the .NET Framework. If the ODBC .NET provider isn’t installed on your system, you can download it from the .NET Software Development Kit (SDK) and Visual Studio .NET. You can find the ODBC .NET data provider on the Microsoft site.

Text ODBC Driver

The Microsoft Data Access Components (MDAC) also include the Microsoft Text ODBC Driver. This provider pre-dates .NET and still provides backwards compatibility.

MDA

Making Sure that You’ve Got A Driver Installed

In the Microsoft Support Article #239114 entitled How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine, Microsoft suggest that performing a search for the Msjet40.dll file in the WindowsSystem32 folder is the best way to make sure that you have the Jet OLE DB Provider 4.0 driver. If you have any of the MS Office applications installed on your PC, you should have the driver as well. If not, you will have to download the applicable Service Pack for your particular operating system (see above for instructions).

Creating a Connection to the CSV File

The Microsoft.Data.Odbc.dll which contains the ODBC .NET data provider resides in the Program FilesMicrosoft.NETOdbc.NET directory. If you’re unsure whether or not you’ve already installed the ODBC data provider add-on component, you can check to see whether or not it’s there.

The Text ODBC Driver is contained in the Odbcjt32.dll and should also be located in the WindowsSystem32 folder.

The Connection String

The connection to the OLE data source is managed by the OleDbConnection object. It’s part of the System.Data.OleDb Namespace, which is the .NET Framework Data Provider for OLE DB. The easiest way to establish a connection is to use the constructor which accepts a connection string. Here is the format to use for CSV files:

Dim objConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
                                     & "Data Source='<File Directory>';" _
                                     & "Extended Properties='text;HDR=Yes;FMT=Delimited';")

Notice that the Data Source points to the folder which contains the text file. That allows for the accessing of more than one file. Think of it as a repository of data files, much like a database is a repository of tables and views. Hence, each file represents one table or view.

The connection to the ODBC .NET data source is managed by the OdbcConnection object of the Microsoft.Data.Odbc Namespace. It also provides a constructor which accepts a connection string:

Dim objConnection As OdbcConnection = New OdbcConnection("Driver={Microsoft Text Driver (*.txt; *.csv)};" _
                                    & "Dbq='<File Directory>';" _
                                    & "Extensions=asc,csv,tab,txt;")

The Text ODBC Driver can be incorporated into older coding technologies such as VBScript or classic ASP since it predates .Net. The following example demonstrates the creation of an ADO connection using classing ASP:

Dim Conn
Set Conn = CreateObject("ADODB.Connection")

Conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" _
        & "Dbq='<File Directory>';" _
        & "Extensions=asc,csv,tab,txt;")

Conclusion

Today we saw how to establish a connection to a CSV data file using a variety of Microsoft drivers. In the next article, we will perform a query against a data file based on criteria from a Web form and display the results in an ASP.NET DataGrid Control.

Robert Gravelle
Robert Gravelle
Rob Gravelle resides in Ottawa, Canada, and has been an IT guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.

Popular Articles

Featured