Home > Microsoft Office 2007 > Excel 2007 Provider Connection Strings – Explained

Excel 2007 Provider Connection Strings – Explained

Currently working on SQL Server Integration Services (SSIS) packages, which i’m using the Excel Provider to read an Excel spreadsheet and populate a SQL Server 2008 database table. You can alter the connection string within the Connection Manager for your Excel spreadsheet. Below is a quick summary of a couple of attributes.

Connection String examples for .config files:

<add name="xls"connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\YourSpreadsheet.xls;Extended Properties=Excel 8.0"/>
<add name="xlsx"connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\YourSpreadsheet.xlsx;Extended Properties=Excel 12.0"/>

The connection string xls is used above for Excel files pre-Excel 2007.  The .xlsx extension was introduced with Excel 2007.

Working with Excel 2007 files

You can add an attribute of HDR=YES to indicate the first row is your column names. Use HDR=NO if not.

  • Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\YourSpreadsheet.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

If 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 then use IMEX=1.

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

There are some limitations of the Excel 2007 providers which are used by SSIS. You may fine a combination of formatting the source spreadsheet using ‘Format Cells’ option and using connection string attribute IMEX, to provide you with the solution you desire.

Greg Olsen
Yellow Duck Guy

Categories: Microsoft Office 2007
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: