Connecting to an Excel Database & Text File in ADO.NET


This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

Connecting to an Excel Database

The nice thing about ODBC is there are drivers for so many different databases and structures. Listing 5-24 shows how to connect to an Excel spreadsheet and retrieve data from it. As you can see, you can use the xls ODBC driver to connect to an Excel spreadsheet. The Employees.xls file is the Northwind database's Employee table exported as an .xls file from Access 2000.

As I said earlier, you must include Microsoft.Data.Odbc namespace reference before using ODBC data provider classes.

Listing 5-24. Reading data from an Excel Spreadsheet using ODBC

// Connection string for ODBC Excel Driver

string
ConnectionString = "Driver = {Microsoft Excel Driver(*.xls) }; " +
"DBQ =c: \\ Employee.xls"
;
Odbc connection conn = new OdbcConnection(ConnectionString);

// Table in Excel can be though of as sheets and are queried as shown
string
sql = " Select EmployeeID, FirstName, LastName FROM Employees";
conn.Open();
OdbcDataAdapter da = new odbcDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds,  "Employees");

Connection to a Text File

You could write the same code for extracting data from a text file by simply changing the connection string and the query. The connection string simply points to the directory where the text files are stored. Each file acts as a table (see listing 5-25).

As I said earlier, you must include Microsoft.Data.Odbc namespace reference before using ODBC data provider classes.

Listing 5-25. Reading data from a tab-delimited text file using ODBC


// Connection string for a Text file

string
ConnectionString = "Driver={Microsoft Text Driver "+ " (*.txt; *.csv) }; DBQ =c:\\";

// Query the TextDB.Txt file as a table

OdbcConnection conn = new OdbcConnection(ConnectionString);
conn.Open();
OdbcDataAdapter da = new OdbcDataAdapter
("Select * FROM TextDB.Txt", conn);
DataSet ds = new DataSet();
da.Fill(ds, "TextDB");
dataGrid1.DataSource = ds.DefaultViewManager;


// close the Connection

conn.Close();

Listing 5-25 uses TextDB.txt, which is tab-delimited file of rows and columns. It looks like Table 5-21.

Table 5-21. Tab- Delimited Text File of Employee Data

EMPLOYEEID

FIRSTNAME

LASTNAME

TITLE

1

Mike

Kramer

President

2

Fred

Jones

Secretary

3

Bill

Leste

VP Sales

4

Ted

Silver

VP Manufacturing


A tab separates each element of a row; however, you could also delimit your file commas.

Conclusion

Hope this article would have helped you in understanding
Connecting to an Excel Database & Text File in ADO.NET. See my other articles on the website on ADO.NET.
adobook.jpg This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.


Similar Articles
Mindcracker
Founded in 2003, Mindcracker is the authority in custom software development and innovation. We put best practices into action. We deliver solutions based on consumer and industry analysis.