In this article, i will examined how to access XLS, XLSX, TXT or CSV files. It is very easy to access these files using ODBC drivers.
Here are Connection Strings
Excel Connection String
"Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";
Excel 2007 Connection String
"Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=\"Excel 12.0;HDR=No;IMEX=1;\"";
CSV or Text Connection String
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"text;HDR=NO;FMT=Delimited;\"";
where {0} represents filename
For complete list of connection strings, please see connectionstrings.com
Here is a code snippet that access XLS or XLSX files using above connection string.
1: using (OleDbConnection connection = new OleDbConnection(connectionString))
2: {
3: connection.Open();
4: DataTable worksheets = connection.GetSchema("Tables");
5: var q = from r in worksheets.AsEnumerable()
6: where r.Field<string>("TABLE_NAME").Contains("$")
7: select r.Field<string>("TABLE_NAME");
8:
9: string sheetName = q.FirstOrDefault();
10:
11: using (OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM [{0}]", sheetName), connection))
12: {
13: using (IDataReader rdr = cmd.ExecuteReader())
14: {
15: while (rdr.Read())
16: {
17: }
18: }
19: }
20: }
When the Text driver is used, the format of the text file is determined by using a schema information file. The schema information file is always named Schema.ini and always kept in the same directory as the text data source. Here is sample Schema.ini file.
1: [Sample.txt]
2: Format=CSVDelimited
Here is a code snippet that access CSV or Text file.
1: using (OleDbConnection connection = new OleDbConnection(connectionString))
2: {
3: connection.Open();
4:
5: using (OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM [{0}]", filename), connection))
6: {
7:
8: using (IDataReader rdr = cmd.ExecuteReader())
9: {
10: while (rdr.Read())
11: {
12:
13: }
14: }
15: }
16: }
To get a complete details of Schema.ini please visit to
MSDN.
Summary
In this article, we examined how to access XLS, XLSX, TXT or CSV files.
Subscribe
