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:

<connectionStrings>
<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"/>
</connectionStrings>

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

Advertisements
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: