Excel 97/2000
Excel is a spreadsheet application that stores and displays data in a proprietary file format.
• Password Protected Sheets: The integration platform cannot connect to password-protected Excel worksheets. If there is an error when trying to connect to a protected worksheet, open the file in Excel application, un-protect and save the file. Reconnect to the worksheet from the integration platform.
• Sheet Selection: If you select the <Default> sheet, the connection automatically reverts to the sheet that was selected the last time the file was saved. If you specify a number, then that number is used as a (0-based) relative sheet number. For example, if a excel file contains Sheet1, Sheet2, and Sheet3 (in that order), then entering a sheet name as 2 selects Sheet3.
On the Target side, if the number does not reference an existing sheet, then a new sheet with that number as its name is created. For example, if you entered 5 for the Target, the spreadsheet will contain sheet names as Sheet1, Sheet2, Sheet3, and 5.
Connector-Specific Notes
When transforming data to or from an Excel file, the following limitations are applicable:
• File open: If the .xls file is open in the Excel application and if you attempt to connect to the same file from DataConnect, the connection fails. The 25519 error is displayed that indicates the file is locked by another process. Close the .xls file.
• Write permissions: The excel file that you are trying to connect to must have write permission.
• Data types: If the format of your data is not recognized, before you connect to the file, set the data type of the column and any formatting that is required.
• Date modified by source file connection: The spreadsheet file is always opened in write mode. Hence, connecting to a source file may change its last modified date to the current date.
• Field length: Excel has a limitation of 32767 characters in a single cell.
– The integration platform does not automatically set the field length to 32767 characters when transforming to a spreadsheet. You may either change the length to 32767 in the schema or you use the default value.
– Field length a measure of a cell's column width and not a measure of the character length. When you view the length that is read in the integration platform, you are viewing the column width set in the Excel application rounded to the nearest whole number.
• Formatting: The integration platform transforms field and record (column and row) oriented data. Title sections (headers/footers), formulas, macros, and displayed formatting are not transformed. Column headings (in a single row) are transformed as field names. Superscript and subscript text can be transformed, since these are format effects applied to normal column and row text.
• Number of fields: Excel 97 and 2000 have a limitation of 256 columns/fields
• Number of records: Excel 97 and 2000 has a limitation of 65536 records. You can use filtering/sampling logic to limit the records and create another Excel workbook for the remaining records.
• Appending data: Append mode is not supported
• Double-Byte character support: You may have to produce double-byte EDIFACT output from a double-byte Excel source. To do this, create and run two maps in sequence:
– Excel to Unicode (Delimited): Set the Unicode Encoding property to UTF-8.
– Unicode (Delimited) to EDIFACT: Set the Encoding property for both source and target to UTF-8.
Excel 97 and Excel 2000 Connector Properties
You can set the following source (S) and target (T) properties.
Data Types
Excel 97 and Excel 2000 supports the following data types:
• Autotext
• Date
• Numeric
• Text
By default, all the data is transformed to text. You can set the first three data types manually.
Special Considerations
Excel 2000 can open and save any version of Excel files from Excel 2.1 to the latest version. If you save the file in the same format you opened it in, the file can still be opened in the version of the program in which it was created. If you save the file to an earlier version, features used in the workbook that are unique to Excel 2000 may be lost. However, if you choose to update to the Excel 2000 format, you are limited to opening the file in Excel 97 and 2000 only.
Note: The integration platform does not recognize the Accounting data type. Before attempting to connect to a source file with this data type, change the Accounting data type to Currency (within your Excel application).
Hybrid File Format
In Excel 2000, you can use a hybrid file format that allows you to save a file in two workbook formats: Excel 97/2000 workbook format and Excel 5.0/7.0 workbook format. This dual file format allows you to open and view the file in Excel 5.0 or 7.0. It also allows you to maintain the advanced features in Excel 2000. This hybrid workbook contains two file formats, consequently, the file size is roughly twice as large as the Excel 2000 format. Be aware that Excel 5.0 and 7.0 do not recognize the Excel 2000 portion of the file. When you open and revise the file in Excel 2000, the changes are automatically updated in the Excel 5.0 and 7.0 portion of the file.
Note: If you edit the hybrid file and save the changes in Excel 5.0 or 7.0, the Excel 97/2000 file part is permanently lost. The file contains only the remaining Excel 5.0 and 7.0 workbook file format data.
Other Compatibility Issues
Microsoft has added several new features in Excel 2000 that do not appear in Excel 97. The integration platform may not read workbooks that contain one or more of the following features:
• Dynamic OLAP PivotTables
• Indented PivotTable reports
• PivotChart reports
• AutoRefresh, column formatting, filtering and sorting on external data ranges
• ScreenTips for hyperlinks