Fetching CSV File Data using Microsoft Data Access Components
WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
Microsoft Data Access Components (MDAC) is a framework of technologies that provides programmers with a uniform and consistent way to develop applications that can access almost any data store. Components include ActiveX Data Objects (ADO), Open Database Connectivity (ODBC), as well as the Microsoft Jet (Joint Engine Technology) Database Engine, the one that everyone knows as the engine that drives MS Access.
This article is the third installment in a series on the MDAC. In the first article, entitled "Work with Text File Data using the Microsoft Text Driver: Creating a CSV Data File", we used the Access Export Wizard to generate a Comma-separated-Values (CSV) file. Then, we saw how to construct connection strings for our data source using the various Microsoft data providers. Today we're going to write an SQL query to extract data from a CSV file.
Getting Information About the Data File
One minor challenge when dealing with data files is the path. As I'm sure you know full well, working with paths in the web world can be a pain. There are virtual paths, relative paths, and of course, the absolute path. Here are a few tricks to removing some of the challenges.
The MapPath() method of the Server object maps a specified relative or virtual path to the corresponding physical directory on the server. To make things even easier, it doesn't care whether you use forward (/) or backward () slashes for the directory separator. You can designate the server's web root directory using a tilde (~). Hence, the following code sets the path variable to a file in the server root:
Dim path As String = Server.MapPath("~/tas_vessel_type_unformatted.csv")
Another helpful utility is the FileInfo class. As the name suggests, it stores many file attributes such as the name, size, and path. We'll be using it to retrieve both our text file's path and name:
Dim file As FileInfo file = New FileInfo(path)
We can use the file.DirectoryName property in the connection string as follows:
objConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source='" & file.DirectoryName & "';" _ & "Extended Properties='text;HDR=Yes;FMT=Delimited';")
The Extended Properties portion of the connection string contains a string within a string with specialized attributes pertaining to the specific data format. The first part tells it that the data is a text file. The HDR abbreviation is short for "Headers". Setting it to Yes as we did here tells the driver that the first row contains header information. The third and last part of the Extended Properties string contains details on the file formatting. It can be one of:
- Delimited: contains the default of comma-delimited values.
- Delimited(): contains delimited values using a custom character - e.g., Delimited (;).
- FixedLength: The values are of a fixed length. That value requires a Schema.ini file in the same folder as the text file that specifies the length of each field.
Today's file is similar the one that we created in the last article, except that it contains the more standard comma-separated values.
The ReadCsvData.aspx Page
As alluded to in the intro, we're going to write an SQL query to extract data from a CSV file. The criteria for the query will be built on the value of an ASP Textbox control, while the results of the query will be displayed using an ASP DataGrid control. These are just two controls that are part of the ASP.NET language:
<form enctype="application/x-www-form-urlencoded" method="get"><p>Search for vessel information by description. Choose from the list below to find a match. Otherwise, leave the textbox empty to return everything.</form>
The ASP.NET Script
The first line in the page is the Page Directive. It's where we the scripting engine what language we are using (VBScript in this case). Adding the Debug attribute and setting it to True configures the ASP.NET run-time process to compile the application with symbolic information so that the application can be debugged - never a bad idea when developing:
Creating the Command Object
All of the action takes place within the btnSearch's OnClick() Event Sub. The search code makes use of several .NET objects as well as a few local variables:
- The path stores the absolute path to the CSV file using Server.MapPath().
- The objConnection is an OleDbConnection object that will store information about our connection to the data source. It requires an Import at the top of the page so that the compiler knows that it belongs to the System.Data.OleDb package.
- The objCommand is an OleDbCommand object that ill execute our query against the data source using the objCommand connection object.
- The strSearch String will hold the text entered into the txtSearch TextBox.
- The strSQLQuery String will hold the SQL query.
- The file is a FileInfo object that will store information about our CSV file. It requires us to import the System.IO package.
Here is the code to get the search string, connect to the database, and create the OleDbCommand object based on the SQL statement and OleDbConnection object. It is important to stick with the same type of object for both the connection and command, i.e., OleDbConnection and OleDbCommand:
Populating the DataGrid Control
Just as we used Ole objects to establish the database connection and create the command object, we now need to use an OleDbAdapter to execute the command against the data source and produce a DataSet. Hence, the OleDbDataAdapter serves as a bridge between a DataSet and data source in the data retrieval process. The OleDbCommand object is passed to the OleDbDataAdapter via the constructor and the DataSet is provided to the Fill() method. From there, it's just a matter of binding the DataSet to the dgSearchResults data grid control by assigning it to the data grid's DataSource property and calling the grid's DataBind() method:
' Get a DataSet to bind the DataGrid to objAdapter = New OleDbDataAdapter(objCommand) objDataSet = New DataSet() objAdapter.Fill(objDataSet) 'DataBind the DataGrid to the DataSet dgSearchResults.DataSource = objDataSet dgSearchResults.DataBind() objConnection.Close()
Microsoft's Data Access Components provide several ways to easily work with CSV data files and offers a light-weight alternative to relational databases. MDACs are a great way to perform lookups and even to import data into database tables.