Database connection Parameters

One, Microsoft Excel

1 Microsoft ACE OLE DB 12.0

1.1 Xlsx Files

Connect to Excel (and later) files with the Xlsx file extension. That's the Office Open XML format with macros disabled.

Provider=Microsoft.ACE.OLEDB. 12.0;D ata Source=c:\myFolder\myExcel2007file.xlsx; Extended Properties=12.0Xml; HDR=YES ";

"Hdr=yes;" indicates that first row contains columnnames, not data. "Hdr=no;" indicates the opposite.

1.2 Treating data as text

The use of this one is want to treat all data in the file as text, overriding Excels column type ' General ' to guess what Ty PE of data is in the column.

Provider=Microsoft.ACE.OLEDB. 12.0;D ata Source=c:\myFolder\myExcel2007file.xlsx; Extended Properties=12.0Xml; 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 dat A is numeric, with imex=1 to avoid crash.
• Imex=1 is a safer-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.

1.3 Xlsm Files

Connect to Excel (and later) files with the Xlsm file extension. That's the Office Open XML format with macros enabled.

Provider=Microsoft.ACE.OLEDB. 12.0;D ata Source=c:\myFolder\myExcel2007file.xlsm; Extended Properties=12.0Macro; HDR=YES ";

1.4 Excel 97-2003 Xls files with ACE OLE DB 12.0

You can use this connection string to use the Office for OLE DB driver (ACE 12.0) to connect to older 97-2003 Excel WORKBO OKs.

Provider=Microsoft.ACE.OLEDB. 12.0;D ata Source=C:\myFolder\myOldExcelFile.xls; Extended Properties=8.0; HDR=YES ";

Database connection Parameters