Wednesday, July 14, 2010

OleDbConnection String for Excel, CSV and Tab Delimited Formats

Office 2003 and Lower till 97
Excel Format (xls)
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FilePathWithFileName;Extended Properties='Excel 8.0;HDR=YES;IMEX=1'

csv Format
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FilePathWithoutFileName;Extended Properties='text;HDR=YES;FMT=CSVDelimited'

Tab Delimited
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FilePathWithoutFileName;Extended Properties='text;HDR=YES;FMT=TabDelimited'


Office 2007 and 2010
Excel Format (xlsx)
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePathWithFileName;Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2

csv Format
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePathWithoutFileName;Extended Properties='text;HDR=YES;FMT=CSVDelimited'

Tab Delimited
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePathWithoutFileName;Extended Properties='text;HDR=YES;FMT=TabDelimited'

Note For Office 2010 you would need to download
Microsoft Access Database Engine 2010
If this is installed this is backward compatible to read older versions too (xls) so there is no need for having two different connection string.

Since this driver has a 64 bit version, the long standing bottleneck of not able to use the 64 bit CPU architecture is finally solved.

3 comments:

  1. in data source you have mentioned "FilePathWithoutFileName" . so where we should give the file name?

    ReplyDelete
  2. You select from the csv file name like:

    using (OleDbConnection excelConn = new OleDbConnection(excelConnStr))
    {
    excelConn.Open();
    OleDbCommand excelCommand = new OleDbCommand();
    OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();
    System.Data.DataTable textDt = new System.Data.DataTable();
    excelCommand = new OleDbCommand("SELECT * FROM [" + csvFileName + "]", excelConn);
    excelDataAdapter.SelectCommand = excelCommand;
    excelDataAdapter.Fill(textDt);
    return textDt;
    }

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete