Excel XP is a spreadsheet application. The integration platform can read and write Excel version 2.x, 3.x, 4.x, 5.x, Excel 95, Excel 97, Excel 2000 and Excel XP files. With the Excel XP connector, the integration platform can read and write Excel XP data files.

Password Protected Sheets - Microsoft does not expose the password component of the Excel API, therefore the integration platform cannot connect to password-protected Excel worksheets. If you encounter an error when trying to connect to a protected worksheet, open the Excel application, unprotect the worksheet and resave the file. Then reconnect to the worksheet in the integration platform.

Excel XP 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 produced. If you save the file to an earlier version format, features used in the workbook that are unique to Excel XP may be lost. However, if you choose to update to the Excel XP format, you are limited to opening the file in Excel 2000 or Excel XP only.

The Excel XP file format is backward-compatible with Excel 97. However, some features unique to Excel XP may not be displayed the same way in Excel 97. For example, a workbook that contains an Excel XP PivotChart report displays the PivotChart report correctly in Excel XP, but displays a regular chart in Excel 97. Also, Visual Basic® for Applications macros that use commands new to Excel XP may result in compile errors when run in Excel 97.

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 in the Excel application.

In Excel XP, 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 XP. This hybrid workbook contains two file formats, consequently, the file size is roughly twice as large as the Excel XP format. Be aware that Excel 5.0 and 7.0 do not recognize the Excel XP portion of the file. When you open and revise the file in Excel XP, the changes are automatically updated in the Excel 5.0 and 7.0 portion of the file.

Microsoft has added several new features in Excel XP that do not appear in Excel 97. The integration platform may not read workbooks that contain one or more of the following features:

Because of the way that Excel handles file locking, connecting to a source file may change its last modified date to the current date.

The integration platform reads and writes the native Excel file format on Windows. There is no native Excel connector for Linux. To run an integration on Linux, the Excel source file should first be converted to a Linux-compatible format such as ASCII. The following are suggestions for converting Excel files so that your transformation can run in Linux production.

For more detailed information about the Excel XP file format, refer to the Microsoft web site.

Some basic limitations should be considered when transforming data to an Excel spreadsheet.

If you have an .xls file open in Excel and then attempt to connect to the same file in a dataset, the connection fails and you receive a 25519 error that says the file is "locked by another process." Close the .xls file, return to the Dataset window, and choose a Sheet name.

In Windows Explorer, you can right-click a file and change the file's permissions settings. If you set Write permissions to Deny on an .xls file and then try to connect to the file in a Dataset window, you receive a 25519 error. You must remove the Deny Write permissions on the file and reconnect.

It is important to remember that the integration platform transforms field and record (column and row) oriented data. Title sections (headers/footers), formulas, macros and display formatting are not transformed. Column headings (in a single row only) can be transformed as field names. (To transform Column headings, see Property Options, under Header Record Row.) Superscript and subscript text can be transformed, since these are format effects applied to "normal" column and row text.

Excel has a limitation of 255 characters in a single cell. If you try to transform data to Excel that has more than 255 characters in any field, that data is truncated at the 255th character.

The integration platform does not automatically set the field length to 255 characters when transforming to a spreadsheet. You may either change the field size to 255 in the target schema grid, or you may leave the field size at the default value. If you leave the field Size greater than 255 characters, when you open the spreadsheet in your application, the data is truncated.

Field length in Excel is actually a measure of the column width of a cell and not a measure of character length. When you view the size that is read in, you are seeing the column width set in the Excel application rounded to the nearest whole number. If you have truncated data, set the length to a higher value in the schema.

Excel XP has a limitation of 256 fields per worksheet.

Excel has a limitation of 65,536 records. If you try to transform data to Excel that has more than 16,384 records, the data is truncated at the 65,537th record.

Data cannot be appended to an existing Excel spreadsheet. This Excel limitation exists because there are a fixed number of records in every Excel spreadsheet (65,536 records in Excel XP). Additional records cannot be added to the end of a spreadsheet as they can be added to a table.

This connector does not support truncation error trapping. If the target field size is too small for the data written to it, the offending record may be skipped or incomplete data may be written to the target. The transformation does not abort due to a truncation error.

You may need to produce double-byte EDIFACT output from a double-byte Excel source. To do this, create and run two maps in sequence:

You can set the following source (S) and target (T) properties.

Shift-JIS encoding is meaningful only in Japanese operating systems.

UCS-2 is no longer considered a valid encoding name, but you may use UCS2. In the XML file, change UCS-2 to UCS2.

By default, the integration platform transforms all data is transformed to Text. For alternatives, see Property Options, under Auto Type Text Fields.

The first three data types below can be manually set.

The field length.

A field length shorter than the default may truncate data.