Excel as a data source

Applies to Infiniti v8.1 or later

 

In some rare cases it is appropriate to use MS Excel as a data source. This can be achieved in Infiniti by using an OLEDB data source.

There are multiple ways to achieve this but the instructions below pertain to the Microsoft.Jet.OLEDB.4.0 provider and an Excel 97-2003 workbook (.xls) spread sheet.

The following checks should be carried out before attempting to connect to Excel

  • Ensure your spread sheet is not opened. Sometimes Excel files open exclusively.
  • The Microsoft.Jet.OLEDB.4.0 provider is installed on the server. For Sever 2008 it is installed by default.
  • Ensure 32 bit applications are enabled, as the Microsoft.Jet.OLEDB.4.0 provider is 32 bit. This property is an Application Pool setting in IIS.

  • 32 Bit Apps

For this article we will use the following excel sheet with two columns and two rows

Excel

Firstly create a new Data Source and use the connection string below, with the correct path to your Excel Data Source.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\data.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Click the test connection button to ensure your connection is functioning.

For Excel 2013:

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

 

Excel Data Source

Click Data Objects and Create New Data object.

Enter a cell range into the Data Object/Name. For the example above we will use A1:C100. Also enter a meaningful display Name.

Your data source is now ready for use.

If your Excel file contains multiple worksheets, a specific sheets information can be referenced by entering [SheetName$A:C] into the Data Object Name / Definition field.

For more information on Connections Strings see http://www.connectionstrings.com/excel/.         

 

Related Articles

 

 

Keywords

 

excel data source oledb datasource application pools dataobject