About cookies on this site Our websites require some cookies to function properly (required). In addition, other cookies may be used with your consent to analyze site usage, improve the user experience and for advertising. For more information, please review your options. By visiting our website, you agree to our processing of information as described in IBM’sprivacy statement. To provide a smooth navigation, your cookie preferences will be shared across the IBM web domains listed here.
Troubleshooting
Problem
NOTE: This document pertains to products that have reached their end of support date and are no longer provided by, nor supported by IBM.
For information on current products set the following technote: Transferring Data From Excel using Access Client Solutions
For information on current products set the following technote: Transferring Data From Excel using Access Client Solutions
This document discusses problems encountered when using the Data Transfer Excel plug-in to upload a Microsoft® Excel spreadsheet that includes dates.
Resolving The Problem
IBM i Access for Windows Data Transfer includes an Excel plug-in that can be used to transfer data directly from or to a Microsoft® Excel spreadsheet. If the spreadsheet includes dates, there are special considerations.
Note: An option was added in V5R3 iSeries Access to Convert Excel date and time cells to iSeries date or time. This option can be found when clicking the Properties button when going through the wizard. The help text for this option is as follows:
Excel does not store dates as a Date data type but rather uses a numeric value with the date implied by the format option selected. The numeric value is used in the same way as the variant date data type. This data type is actually a timestamp. It is implemented as a floating point value (double) that measures the days from Midnight, December 30, 1899. The fractional part of the value represents a time. For example, .25 is 6AM, .5 is 12 noon, and so on. By convention, Microsoft® Office, Microsoft® Visual Basic, and most other tools use a date of 0 (1899-12-30) to represent a time-only value. A time of 0 (midnight) is used to represent a date-only value.
IBM i Access for Windows data transfer does not currently parse all the possible format options (and user-defined format options) to try to identify a date (or time) column. Date or time cells must be formatted as text, and the character data must be in the same format as specified in the data transfer format options. An enhancement to parse Excel format options is being considered for a future release.
Creating a New File Based on a Spreadsheet
If the data transfer Create new file based on spreadsheet option is used, the data transfer scan of the spreadsheet will identify the date column as zoned 5,0. Note that the data type of the IBM OS/400 or IBM i5/OS field cannot be changed from zoned to date.
To work with the data in a date format, do one of the following:
Uploads to Existing Files
In the case of an existing OS/400 or i5/OS file, data transfer detects that the IBM Power Systems data type is a Date. The cell data, however, must still be formatted as character or a conversion error will occur.
Note: An option was added in V5R3 iSeries Access to Convert Excel date and time cells to iSeries date or time. This option can be found when clicking the Properties button when going through the wizard. The help text for this option is as follows:
Excel does not store dates as a Date data type but rather uses a numeric value with the date implied by the format option selected. The numeric value is used in the same way as the variant date data type. This data type is actually a timestamp. It is implemented as a floating point value (double) that measures the days from Midnight, December 30, 1899. The fractional part of the value represents a time. For example, .25 is 6AM, .5 is 12 noon, and so on. By convention, Microsoft® Office, Microsoft® Visual Basic, and most other tools use a date of 0 (1899-12-30) to represent a time-only value. A time of 0 (midnight) is used to represent a date-only value.
IBM i Access for Windows data transfer does not currently parse all the possible format options (and user-defined format options) to try to identify a date (or time) column. Date or time cells must be formatted as text, and the character data must be in the same format as specified in the data transfer format options. An enhancement to parse Excel format options is being considered for a future release.
Creating a New File Based on a Spreadsheet
If the data transfer Create new file based on spreadsheet option is used, the data transfer scan of the spreadsheet will identify the date column as zoned 5,0. Note that the data type of the IBM OS/400 or IBM i5/OS field cannot be changed from zoned to date.
To work with the data in a date format, do one of the following:
|
|
Convert the data after it has been transferred. The numeric value can be converted to a date value using the function date( F1 + days('1899-12-30')). The table can be remapped by creating a duplicate, altering the numeric date field to a date data type, and running an IBM SQL/400 statement similar to the following to copy the data:
insert into excelout select date( F1 + days('1899-12-30')) from exelin |
|
|
Format the column as character. Another option is to format the column in the spreadsheet as character. The IBM i Access data transfer table scan will identify the column as character of length 8. After the scan completes, select details and change the data type to date as shown below: |
|
Note that the Data transfer format options must match the character formatted date column. For example, if the data transfer format is MDY with a separator of /, the cells must contain a character date value in format of mm/dd/yy. Data transfer will then upload the spreadsheet data successfully.
|
|
|
|
Export the data as CSV(comma-separated variable) file, and run data transfer against the CSV file. |
Uploads to Existing Files
In the case of an existing OS/400 or i5/OS file, data transfer detects that the IBM Power Systems data type is a Date. The cell data, however, must still be formatted as character or a conversion error will occur.
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z000000cwMLAAY","label":"Data Access-\u003EData Transfer"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"6.1.0;7.1.0"}]
Historical Number
26590752
Was this topic helpful?
Document Information
More support for:
IBM i
Component:
Data Access->Data Transfer
Software version:
6.1.0, 7.1.0
Operating system(s):
IBM i
Document number:
640557
Modified date:
10 January 2025
UID
nas8N1017021
Manage My Notification Subscriptions