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

In this article I will explain 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.

This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.