If your data is in an Excel spreadsheet you can link to this data by entering the following information into the ADO connection string box.

 

For Office versions 2007 and later

This one is for connecting to Excel 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";

"HDR=Yes;" indicates that the first row contains column names, not data. "HDR=No;" indicates the opposite.

 

For versions prior to Office 2007

Provider=Microsoft.Jet.OLEDB.4.0;

Extended Properties=Excel 8.0

The second line that references the extended properties command will need to change based on the version of Excel that you are using.

Extended Properties=Excel 3.0 is used for Microsoft Excel version 3.0

Extended Properties=Excel 4.0 is used for Microsoft Excel version 4.0

Extended Properties=Excel 5.0 is used for Excel 95 and Excel 97

Extended Properties=Excel 8.0 is used for Excel 98 and above

 

FIG. 1 shows sample data from an Excel spreadsheet column C contains the information that is needed to populate the data dictionary field DOB.

 

FIG.2 shows the entry of the Excel file in the Database Path and Name field. The ADO Connection String contains the information to link to an Excel spreadsheet.

 

If you want you can specify the workbook directly in the Connection String by adding Data Source=path\workbook name before the Extended Properties setting. Here is an example Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=”Excel 8.0;”.

 

As there is no direct way to specify the data types for columns in Excel tables the provider scans a limited number of rows in a column to "guess" the data type for the field. The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.

This impacts where you are intending to use a data default to calculate a field. If the scan doesn’t find a data element to determine the data type it will default to a string. For example, if you are using an expression to calculate the date field DOP (date of participation) and in your Excel workbook this value is blank for all records, then ADO sets the data type to string (202) when it reads the field in. In ProAdmin, a string is not a valid data type for a date field, so processing is aborted. To prevent this from happening, at least one of the records must have a valid date in it so ADO can determine that the correct data type is DATE (even though for a particular record the date may be empty or missing).

 

In most Excel workbooks, the first row is the header row (or field names). If the first row does not contain headers, you can specify HDR=NO in the extended properties of your connection string. If the first row does not contain headers, the OLE DB provider automatically names the fields for you (where F1 would represent the first field, F2 would represent the second field, and so forth).

 

If you are using the Where condition you will need to enclose any field names within brackets. For example, if you need to exclude base pay that is less than $5,000 you would enter [Base Pay] > 5,000 in the Where condition.