CPYTOIMPF removed leading zeros from numeric field

30 pts.
Tags:
AS/400 database
I am having a few problems using the CPYTOIMPF command. I am copying an external defined file on the AS400 to the IFS. It is removing leading zeros from numeric fields which I need the leading zeros in, such as company number. It also is placing trailing blanks in the numeric fields, which I don't want. How can I prevent these things from happening?

Software/Hardware used:
AS/400

Answer Wiki

Thanks. We'll let you know when a new response is added.

If the columns are data type NUMERIC, then removal of leading zeros is the correct behavior. You can’t expect a tool to work incorrectly just for some columns that you’d like to handle differently from others.

You have alternatives:<ol>
<li>Don’t use CPYTOIMPF. Write your own programs.</li><li>Create a view that formats the NUMERIC columns as CHAR columns in whatever format you choose. Use CPYTOIMPF over the view instead of over the table.</li><li>Use the Qshell db2 utility to construct a .CSV or another kind of streamfile. Like the view, your SELECT statement can create whatever kind of output you want.</li>
</ol>

(There may be others.)

However you proceed, be aware that every tool is going to work according to how you define your database. You must adjust accordingly. If you don’t want NUMERIC columns treated as numeric, then define them as a different data type. A view can CAST() columns to different data types, but you have to create the instructions.

Tom

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Rpgldb
    Thank you for responding. In the past I have created my own program to handle creating .csv files, and then I used the CPYTOPCD command to move the file to the IFS. However, this time I wanted to give the CPYTOPCD command a try. It is not that I expected the tool to work differently on numerics, but thought perhaps there was an option that I was missing. Also, this doesn't explain why I have blanks added to the end of the numeric fields. Also, I really don't have any idea what some of the other options will do for me, such as STMFOCODPAG. I do not do any PC language programming, only RPG on the AS/400. The to file will be FTP to a PC server.
    30 pointsBadges:
    report
  • TomLiotta
    There have been added options to CPYTOIMPF in current releases, starting with V5R4. I don't think any were PTFed backwards. For example, you can now specify options for trimming leading/trailing blanks. In any case, the creation of a view to define how you want columns handled may be your best choice. E.g.,
    RIGHT(TRIM('00000' concat CHAR(SEQN)),5)
    ...should take a 5-digit numeric 'sequence' column and convert the significant digits to characters, then add five zeros to the front, trim the result, and take the five rightmost remaining characters. Rather than coding the "5" at the end of the RIGHT() function, LENGTH(SEQN) could be used for a NUMERIC column. Using LENGTH(), such a column specification could be generalized more in order to determine how many zeros should be accounted for at the beginning. The STMFCODPAG() option probably should be *PCASCII, especially when you are creating the streamfile. You have the ability on the AS/400 to do the work in EBCDIC or ASCII, in a bunch of different CCSIDs. Windows can also handle data in most kinds of encoding. But it's not always clear on how to get the two sides to agree across a network connection. FTP, for example, commonly transfer in either text or binary mode. In binary, the bits are copied directly. In text, the bit patterns are translated from whatever encoding is on the source system to the encoding on the target system. But FTP handles every record as a single string. It doesn't recognize when there are different kinds of fields such as packed or binary embedded within the string. Windows network transfers are similar. If you don't take care to encode all of each record properly on the AS/400 side, Windows will apply its own rules. By specifying *PCASCII, you are preparing each of the bytes in the expectation that Windows networking (or FTP or some other transfer methods) will be copying the file. Note that the *PCASCII encoding can also be handled by the AS/400. If you use DSPF to view the streamfile after you create it, it should be perfectly readable. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Minor added comment -- once a view is created, that view becomes the file that you name as input to CPYTOIMPF. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following