If your data is in an Excel spreadsheet you can link to this data by entering one of the connection strings below (depending on your version of Office) into the ADO connection string box. 

ADO only looks at the first 10 rows of an Excel column (after the header row, if it exists) to determine the data type (e.g., date, number, character) of the values in that column. If the first 10 rows are blank, then ADO assumes that column is character. If that column actually contains dates or numbers, then you'll get errors when trying to load the data (i.e., you can't load character data into a number or date field). To get around this problem, you need to create a dummy row (first row after the header row) with an appropriate value that defines the data type of the data in that column. For example, insert a row immediately after the header row, and insert this expression in the first column 2nd row: =LOOKUP(2,1/(A2:A10000<>0),A2:A10000). Now, select and drag this expression to fill every column in this row with a value. In the PersonID column, you should delete this expression and set it to 0 (if the PersonID column is numeric) or blank if it's character (but it doesn't hurt if you enter something more descriptive like ExcludeMe). Now, the first row contains values that ADO can use to determine the correct data type of each column. 

In Excel, number and dates are both stored as numbers, but you can apply Excel formats to change how the data "looks" in a column. ADO ignores the Excel column formats when extracting the data, so that data is read properly (i.e., reads the underlying numbers, not the formatted values). Sometimes your dates and numbers are not actually numbers, but strings that contain dollar signs, commas, slashes, dashes or blanks. In that case, you will have to use Excel's Find & Replace to cleanup the data before ADO can load it properly. The Database Linkage validate option can help you identify which numeric or date columns are considered character, but in Excel if you add 1 to a string you'll get a #VALUE! error (and you'll know it's a string not a number).

This article, Fix Excel Datatype Inconsistencies, describes a more direct was to help you fix these kinds of datatype problems in Excel. 

 

For Office versions 2007 and later

This connection string is for Excel files with the .xlsx file extension, which 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. If you don't include the HDR option, then "HDR=Yes" is assumed (Note, Microsoft keeps changing default behaviors, so you should include the "HDR=Yes" option.)

 

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;”.

There is no direct way to specify the data types for columns in Excel tables, so 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.