Excel
Excel is a spreadsheet application that stores and displays data in a proprietary file format. With the Excel connector, the integration platform reads and writes Excel versions 2 through XP.
This section covers the Excel v2, v3, v4, v5, 95, and 97 connectors.
• 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.
• The Excel General data type is not supported and should be changed to Text. Excel also uses cell width to determine the field size when it exports data. When the cell is too small to display all the data it contains, some data is "hidden" and can only be seen when you tab into that cell. This hidden data is truncated on exports. For more information, see the limitations section.
• Versions 95/97/2000: If you select the sheet named <Default> in the table list, the connection automatically reverts to the sheet that was selected the last time the file was saved. If you specify a number (and no sheet with that number as a name exists), then that number is used as a (0-based) relative sheet number. For example, if a spreadsheet contains "Sheet1", "Sheet2" and "Sheet3" (in that order) then entering a table/sheet name of "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. (So, for example, using the above spreadsheet, if you entered "5" on the Target, you would get a spreadsheet containing sheets names "Sheet1", "Sheet2", "Sheet3" and "5").
Connector-Specific Notes
Some basic limitations apply when transforming data to or from an Excel spreadsheet.
File Open
If you have an .xls file open in the Excel application and then attempt to connect to the same file in the Dataset window, the connection fails. You receive a 25519 error that says the file is "locked by another process." Close the .xls file and return to the Dataset window. Choose a Sheet name.
Write Permissions
In Windows Explorer, you can right-click a file and change the file 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.
Data Types
The Excel General data type is not supported. Change the data type to Text in the Excel application before using the spreadsheet in a map.
Most custom formats are not supported. If the format of your data is not recognized, befor you connect to the file, set the data type of the column and any formatting that is required.
Date Modified by Source File Connection
Because of the way that Excel handles file locking, connecting to a source file may change its last modified date to the current date.
Field Length
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 length to 255 in the schema or you may leave the default value. If you leave the value greater than 255 characters, when you open the spreadsheet in your application, the data is truncated.
• Spreadsheets store data in such a manner that source connections provide inexact metadata, sometimes causing source truncation errors. If you find a truncation error and the log indicates that it is source truncation, you can adjust the source field size. To do this, in the source schema, find the size column and the row that represents the truncated field. Adjust the field length and run the transformation.
• 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 length that is read in the integration platform, you are seeing the column width set in the Excel application rounded to the nearest whole number. If you have truncated data, you can set the length to a higher number in the schema.
Formatting
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. Superscript and subscript text can be transformed, since these are format effects applied to "normal" column and row text.
Number of Fields
Excel has a limitation of 254 fields. If you transform data to Excel with more than 254 fields, it is truncated at the 254th field. Excel 2000 has a higher limit of 256 fields.
Number of Records
Excel has a limitation of 16,384 records. If you transform data to Excel with more than 16,384 records, it is truncated at the 16,384th record.
Excel 97 (v8) and Excel 2000 have a higher limit of 65,536 records.
Appending Data
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 97 and Excel 2000). Additional records cannot be added to the end of a spreadsheet as they can be added to a table.
Truncation Error Trapping
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.
Double-Byte Character Support
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:
• 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 v2, 3, 4 Property Options
Header Record Row
For Excel source files, this is the record number (row number) of any column headings. For Excel target files, it sets the row number to which column headings are written.
If no column headings are involved in the data transformation, the value should be left to the default setting (0). If the column heading is the first record (row 1), the value should be changed to 1.
Caution! If you set the Header Record Row Property with a value that exceeds the number of records in the source file, the Header Record Row is not written to the target.
Auto Type Text Fields
Automatic styling changes the way Excel data is read or written. For example, when Delimited ASCII is the source connector, all data is Text (except when you change the Delimited ASCII source property AutomaticStyling to
true or when you change the Type from Text to a numeric data type in the Delimited ASCII source schema grid). For details on these options, see
ASCII (Delimited). When that data is transformed to a spreadsheet, you may need the fields that contain numbers to be formatted as Numeric to perform calculations in the spreadsheet.
The default for this setting is true. To transform the number fields as Text in a target Excel file, change the setting to false.
CodePage
This translation table determines which encoding to use for reading and writing data. The default is ANSI, the standard in the US.
Excel v5, v7 (95) Property Options
Header Record Row
For Excel source files, this is the record number (row number) of any column headings. For Excel target files, it sets the row number to which column headings are written.
If there are no column headings involved in the data transformation, the value should be left to the default setting (0). If the column heading is the first record (row 1), the value should be changed to 1.
Caution! If you set the Header Record Row Property with a value that exceeds the number of records in the source file, the Header Record Row is not written to the target.
CodePage
This translation table determines which encoding to use for reading and writing data. The default is ANSI, the standard in the US.
Excel v97 Property Options
Header Record Row
For Excel source files, this is the record number (row number) of any column headings. For Excel target files, it sets the row number to which column headings are written.
If there are no column headings involved in the data transformation, the value should be left to the default setting (0). If the column heading is the first record (row 1), the value should be changed to 1.
Caution! If you set the Header Record Row Property with a value that exceeds the number of records in the source file, the Header Record Row is not written to the target.
Encoding
To specify encoding for reading source and writing target data, select a code page translation table. The default value is OEM. To use ANSI, select ISO8859-1. For Windows CodePage 1252, select CP1252. To use ANSI, select ISO8859-1.
Encoding Notes
• 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.
Special Notes about Excel Dates
Excel v3
To convert Excel v3 date strings into valid date formats for other database applications, use the following target field expression:
DateValue(Fields("FIELDNAME"))
where ("FIELDNAME") is the source field name in the Excel source data file.
Excel v4 and v5
To convert Excel v4 and v5 integer dates into valid date formats for other database applications, write the following expression:
DateAdd("d", Fields("FIELDNAME"), DateValue("12/30/1899"))
where ("FIELDNAME") is the Source field name in the Excel source data file. The remainder of the expression should be written exactly as shown above.
Data Types
By default, all data is transformed to text. For alternatives, see Auto Type Text Fields under Excel v2, 3, 4 Property Options.
Four data types are supported. The first three can be manually set.
• Autotext
• Date
• Numeric
• Text
• The Excel General data type is not supported. Change all General data types to Text in Excel before connecting to the spreadsheet.
Length
The field length.
Note: The integration platform does not automatically set field length to 255 characters when writing data to a spreadsheet. You may either change the length to 255 in the schema or you may leave it with the default value. If you have a length greater than 255 characters, when you open the spreadsheet in your application, the data is truncated.