Pamplemousse
Pamplemousse

Reputation: 113

OLEDB connection string for excel 2013

I'm trying to adapt my old connection string to an excel 2007 file for an excel 2013 file (.xlsx)

Here is my old one:

strConnectionString = string.Format(
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};" + 
    "Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", 
    strFile);

What can I do regarding the new one?

Upvotes: 4

Views: 13812

Answers (2)

Prasan Karunarathna
Prasan Karunarathna

Reputation: 395

Treating data as text

Use this one when you want to treat all data in the file as text, overriding Excels column type "General" to guess what type of data is in the column.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash.

To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash.

Upvotes: 0

Pacheco
Pacheco

Reputation: 1032

Have You Tried this one...

string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text'", arq)

Upvotes: 0

Related Questions