Excel connection strings
CData ADO.NET Provider for Excel
-
Standard
Excel File=C:\myExcelFile.xlsx;
-
No headers in Excel sheet
Excel File=C:\myExcelFile.xlsx;Header=False;
-
Caching data
Excel File=C:\myExcelFile.xlsx;Cache Location=C:\cache.db;Auto Cache=true;
Offline=false; -
Caching data and metadata
Excel File=C:\myExcelFile.xlsx;Cache Location=C:\cache.db;Auto Cache=true;
Offline=false;Cache Metadata=true; -
-
Cached data only / Offline mode
Excel File=C:\myExcelFile.xlsx;Offline=true;Query Passthrough=true;
Cache Location=C:\cache.db; -
Using an External Cache Provider
RSSBus drivers have the ability to cache data in a separate database such as SQL Server or MySQL instead of in a local file using the following syntax:
Cache Provider=Provider.Namespace;
Cache Connection='Connection String to Cache Database'; -
Empty cells always NULL
Excel File=C:\myExcelFile.xlsx;Empty Text Mode=EmptyAsNull;
-
Empty cells always empty string
Excel File=C:\myExcelFile.xlsx;Empty Text Mode=NullAsEmpty;
-
Suppress formula calculation errors
Excel File=C:\myExcelFile.xlsx;Ignore Calc Error=true;
-
Read "tilted sheets", where rows are headers and columns are rows
Excel File=C:\myExcelFile.xlsx;Orientation=Horizontal;
-
Do not use formulas, only values
Do not treat values starting with equals (=) as formulas during inserts and updates.
Excel File=C:\myExcelFile.xlsx;Allow Formula=false;
Microsoft ACE OLEDB 12.0
-
Xlsx files
Connect to Excel 2007 (and later) files with the Xlsx file extension. That is the Office Open XML format with macros disabled.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES"; -
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"; -
Xlsb files
Connect to Excel 2007 (and later) files with the Xlsb file extension. That is the Office Open XML format saved in a binary format. I e the structure is similar but it's not saved in a text readable format as the Xlsx files and can improve performance if the file contains a lot of data.
Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=c:\myFolder\myBinaryExcel2007file.xlsb;
Extended Properties="Excel 12.0;HDR=YES"; -
Xlsm files
Connect to Excel 2007 (and later) files with the Xlsm file extension. That is the Office Open XML format with macros enabled.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;
Extended Properties="Excel 12.0 Macro;HDR=YES"; -
Excel 97-2003 Xls files with ACE OLEDB 12.0
You can use this connection string to use the Office 2007 OLEDB driver (ACE 12.0) to connect to older 97-2003 Excel workbooks.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xls;
Extended Properties="Excel 8.0;HDR=YES";
Microsoft Jet OLE DB 4.0
-
Standard (Excel)
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"; -
Standard alternative
Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.
OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
.NET Framework Data Provider for OLE DB
-
Use an OLE DB provider from .NET
Provider=any oledb provider's name;OledbKey1=someValue;OledbKey2=someValue;
Microsoft Excel 2007 ODBC Driver
-
Standard
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
DBQ=C:\MyExcel.xlsx; -
Standard (for versions 97 - 2003)
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
DBQ=C:\MyExcel.xls;
Microsoft Excel ODBC Driver
-
Standard
Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;
DefaultDir=c:\mypath; -
Specify ReadOnly
[Microsoft][ODBC Excel Driver] Operation must use an updateable query. Use this connection string to avoid the error.
Driver={Microsoft Excel Driver (*.xls)};Dbq=C:\MyExcel.xls;ReadOnly=0;
.NET Framework Data Provider for ODBC
-
Use an ODBC driver from .NET
Driver={any odbc driver's name};OdbcKey1=someValue;OdbcKey2=someValue;
.NET xlReader for Microsoft Excel
-
Excel file with header row
Data Source =c:\myExcelFile.xlsx;HDR=yes;Format=xlsx;
-
Excel file without header row
Data Source =c:\myExcelFile.xlsx;HDR=no;Format=xlsx;
-
Excel file with header row (for versions 97 - 2003)
Data Source =c:\myExcelFile.xls;HDR=yes;Format=xls;
-
Excel file without header row (for versions 97 - 2003)
Data Source =c:\myExcelFile.xls;HDR=no;Format=xls;
Connect
Articlesread all »
- Jet for Access, Excel and Txt on 64 bit systems
- The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine
- Open a password-protected Excel workbook
- Formating Rules for Connection Strings
- Connection Strings Explained
- Store Connection String in Web.config
- Connection Pooling
- The Provider Keyword, ProgID, Versioning and COM CLSID Explained
- SQL Server Data Types Reference
- Network Protocol for SQL Server Connection
Didn't find your connection string?
Start over from the connection string reference index - or try a search!
In the Q&A forums you can ask your own question and let somebody help you.
The knowledge articles contains solutions and guides.