Client Access has a built-in "Data Transfer from iSeries" function ('Actions', 'Receive File From Host...') which has numerous formats for output, including native excel formats (through MS Excel 8). It's VERY fast, has considerable query capabilities, and easily handles everything that this column notes as a shortcoming of CPYTOIMPF.

Not exactly. If you use this with "DATE" type fields, it doesn't import them correctly. Also "null" dates (01-01-0001 on the iSeries) aren't recognized by Excel at ALL.

I've pasted a small sample directly from an excel spreadsheet that was downloaded from a file on our iSeries. The dates both appear and function as they should. <p><!--mccodelink_begin--> <BR>
<!-- do not remove --> <BR>
<hr width=50 align=left><small><a href='http://www.mcpressonline.com/mc/showcode@@.6b27f966/3' target='_blank'>Code</a></small> <BR>
<!--mccodelink_end-->

I'm always amazed at statements such as <p>"The reason CPYTOIMPF is limited is that it outputs fixed-length field values (it does not trim off trailing blanks)..." <p>CPYTOIMPF outputs fixed-length fields as fixed-length values, and variable-length fields as variable-length values. It would be nice to have an optional feature to trim trailing blanks from fixed-length fields, but you have to remember that the full fixed-length value, complete with trailing blanks is the ACTUAL VALUE OF THE DATA IN THE FIELD. Truncating the data means altering the data. Granted, in very many cases altering the data may produce the most convenient output format. Still, the CPYTOIMPF command should not as a default alter the value of any data -- fixed-length or otherwise. <p>I think the problem stems from the fact that there's so much System-38, AS/400, iSeries legacy data and legacy mindset from when variable-length fields were not an option. Fixed-length fields are taken for granted. The more you work with folks from Oracle, SQL, etc. background, the more you'll see that fixed-length character data has a more limited role in most databases because fixed length actual values have a much more specific purpose. <p>In other words, don't blame the utility for being accurate.

I don't think I'm in disagreement with you. I do want an option and there should be one there (on CPYTOIMPF) and on my own CPYTOCSV command. Today neither has the option. <p>Fixed-length fields stem back to 80-column cards, not System/38 (which supported VARLEN fields). But I get your point. <p>This "blank truncation" feature is similar to the "Blank Transparency" option in RPG IV that we debated before IBM introduced RPG IV. They did not want to modify data, and therefore programs would blow up with decimal data errors. I advocated a Blank Transparency option that would simply pass numeric fields containing X'40' through without a DDS, effectively turning them into zero values.

Try performing a date related calculation on any of the 0001-01-01 dates. =Month(D2) for example returns #VALUE.

I am trying to upload an excel spreadsheet to i-series. The spreadsheet has few date fields. When I transfer this spreadsheet to i-series it does not transfer the correct dates. It shows some funky numbers insted. I think it converts the date into some numeric value that is different then date. Any resolution?

As far as I know, Excel stores dates as a <u>lillian</u> number. That is the number of days from a fixed date. <p>If you know what the date is, it is possible to calculate the field back to a true date by using CEExxxx date routines, or your own calculation. <p>Dave

This is a discussion about <B>Tips and Techniques: Displaying Dates in Excel</b>.<p align='center'><a href=http://www.mcpressonline.com/mc? This e-mail address is being protected from spam bots, you need JavaScript enabled to view it @.6b27ccdc>Click here for the article</a>.</p>

I am trying to upload an excel spreadsheet to i-series. The spreadsheet has few date fields. When I transfer this spreadsheet to i-series it does not transfer the correct dates. It shows some funky numbers insted. I think it converts the date into some numeric value that is different then date. Any resolution?