DataObject.CreateFromExcelFile

Prototypes

static DataObject CreateFromExcelFile( <String sName,> String sPathName, String sSheetName <, boolean bUseFirstRowAsVarNames, boolean bReadMixedTypesAsStrings> )

Return Value

The return value is a reference to the newly created object of the DataObject class.

Parameters

String sPathName
The filename of, and optionally the path to, the Microsoft Excel workbook file to use to populate the DataObject. See Remarks for more information.

String sSheetName
The name of the worksheet within the Microsoft Excel workbook file to use to populate the DataObject.

String sName
The name to assign to the DataObject.

boolean bUseFirstRowAsVarNames
If bUseFirstRowAsVarNames is true, the values in the first row of the worksheet are interpreted as variable names. If bUseFirstRowAsVarNames is false, the variables in the DataObject are assigned the names F1, F2, F3, etc. Calling this method without specifying bUseFirstRowAsVarNames is equivalent to calling it with bUseFirstRowAsVarNames equal to true. See Remarks for additional details.

boolean bReadMixedTypesAsStrings
If bReadMixedTypesAsStrings is true, IMLPlus converts all values in a column to text if the column contains a mixture of character and numeric values. If bReadMixedTypesAsStrings is false, IMLPlus does not convert numeric values to text if the column contains mostly numeric values. Calling this method without specifying bReadMixedTypesAsStrings is equivalent to calling it with bReadMixedTypesAsStrings equal to true. See Remarks for additional details.

Remarks

This method creates an object of the DataObject class and populates it with data from the specified Microsoft Excel worksheet. If you do not specify the parameter sName, the filename (excluding the extension) is used as the name of the DataObject.

IMLPlus performs the following steps to locate the file specified by the sPathName parameter:

1. IMLPlus first tries to open the file using the sPathName string literally.
2. If sPathName is either a filename or a relative pathname, IMLPlus searches for the file on the Data File Search Path.
3. If IMLPlus cannot open the file using the previous steps, it reports an error.

You can configure the Data File Search Path using the Directories tab of the Options dialog box.

If the parameter bUseFirstRowAsVarNames is true, IMLPlus interprets the values in the first row of the worksheet as variable names. IMLPlus applies the following transformations to convert a value in the Excel worksheet to a valid SAS variable name:

1. If the value is longer than 32 characters, it is truncated to 32 characters.
2. If the first character is not valid for a SAS variable name, the character is replaced with '_'. A SAS variable name must begin with one of the following characters: 'A'-'Z', 'a'-'z', or '_'.
3. If any of the remaining characters are not valid for a SAS variable name, they are replaced with '_'. A SAS variable name can only contain the following characters: 'A'-'Z', 'a'-'z', '0'-'9', or '_'.
4. If the resulting name duplicates an existing name, a number is appended to the name to make it unique. If appending the number causes the length of the name to exceed 32 characters, the name is truncated to make room for the number.

For each column in the Excel worksheet, IMLPlus must decide whether to treat the column as a numeric variable or as a character variable. To make this determination, IMLPlus examines the first eight values in the column. Based on the data types of those values and on the setting of the parameter bReadMixedTypesAsStrings, IMLPlus assigns a data type to the variable as shown in the following table:

Notes

• If IMLPlus issues an error message when importing a particular Microsoft Excel workbook file, open the file in Excel itself and then re-save the workbook in Microsoft Excel Workbook format using a different filename. Some Excel files created by other applications cannot be imported by IMLPlus.
• If a column in the Excel worksheet has a date, time, or currency format applied to its values, IMLPlus assigns a corresponding format and informat to the variable. In all other cases, IMLPlus does not assign a format and informat to the variable.
• If bReadMixedTypesAsStrings is true and IMLPlus encounters a numeric value in a character variable, it converts the value to text.
• If bReadMixedTypesAsStrings is false and IMLPlus encounters a numeric value in a character variable, it converts the value to text. If bReadMixedTypesAsStrings is false and IMLPlus encounters a character value in a numeric variable, it replaces the value with a SAS numeric missing value.
• To read a password-protected Excel file using this method, open the file using Excel itself before running the IMLPlus program.
• All numeric values are converted to double-precision, floating-point numbers.
• IMLPlus uses "Microsoft Access Database Engine" to import Excel files.

Example

declare DataObject dobj;

/* Change the next line to point to your Excel file. */

pathname = "C:\My Excel Files\SomeExcelFile.xls";

sheetname = "Sheet1";

dobj = DataObject.CreateFromExcelFile( pathname, sheetname );

DataTable.Create( dobj ).ActivateWindow();