Creating CSV file from AS/400 fixed length physical file

290 pts.
Tags:
AS/400
CSV file
Physical File
We are having a requirement that we need to create an interface file with different sections as follows. File Header, Data Header, Data Details and Footer. Each record is of variable length. But the information should follow the same format such as File Header should be first record then followed by data header and data details. Data Header and Data details will be repeated for many transactions. Last record will be footer record. The output should be in CSV format. As the information has variable length records, I created a flat file and using data structure I defied the comma as delimiter and FTP the file. But the output has blanks in the field when the value is not completely filled the field length. Is there any way that I can remove the blanks in the field or there is a better way to create the output file. Sample of the CSV file is as follows. 
"840","01","000000012","000000001","2","KOM-DK029620-4000                       ","   ","F  ","SEAL           "," ","     ","DQ590
"840","10","000000012","000000002","2","KOM-DK029620-4000                       ","1","1","1","000000051","000000127","000000076",
"840","01","000000012","000000003","2","KOM-DK029621-1010                       ","   ","F  ","SEAL           "," ","     ","DQ590
Basic requirement is to remove the blanks for Character and leading zeros for numeric fields.


Software/Hardware used:
OS V6R1
Asked: Last updated:
Related Questions
1

Answer Wiki

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

Look into using the BIF’s 

C* TRIM LEADING BLANKS                                         
C                   EVAL      RESULT = %TRIML(FLD1)            
C                                                              
C* TRIM LEADING CHAR                                           
C                   EVAL      RESULT = %TRIML(FLD1:'?')        
C                                                              
C* TRIM TRAILING BLANKS                                        
C                   EVAL      RESULT = %TRIMR(FLD1)            
C                                                              
C* TRIM LEADING CHAR (IN THIS CASE 0)                          
C* THEN TRIM TRAILING BLANKS FOR CONCATANATION                 
C* (SHORTENS FIELD TO ONLY POSITIONS WITH DATA)                
C                   EVAL      RESULT = %TRIMR(%TRIML(FLD1:'0'))

Discuss This Question: 9  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.
  • hunshabbir7
    I come across this problem about a week ago. I needed to put Report Heading on first line, date time on second line and columns heading on third line. I just made a flat file write three records from Datastructures in RPG. Then in CL I write two CPYTOIMPF commands first for this flat file and second for the Physical File. Remember to change parameter MBROPT(*REPLACE) for flat file and MBROPT(*Add) for physical file. The output will be a single file with all information
    2,900 pointsBadges:
    report
  • Babs312

    Thanks for all your replies.  Regarding using BIF,  I have fields with numeric and non numeric, hence concat not possible.  I don't have multiple physical files to use CPYTOIMPF.  As it is a fixed length file there is no way I can use CPYTOIMPF.

    Any other suggestions.

    290 pointsBadges:
    report
  • Splat
    Edit your numeric fields with the %editc or %editw BIFs, e.g.:

    %trim(%editc([numeric field name]: 'P'))

    12,895 pointsBadges:
    report
  • Splat
    CPYTOIMPF can be used whether the file format contains one or many fields.
    12,895 pointsBadges:
    report
  • Babs312

    Hi All,

    Thanks for all your suggestions.  I need to research more as I didn't get the required answer.

    290 pointsBadges:
    report
  • ToddN2000
    What is your interface looking for in the format? If the blanks are stripped out, is the data left or right justified? What if the field is like a mini data structure containing flags? You may want to keep the blanks in the file. Are they causing a problem withe the interface or is this just to make the file more readable?
    133,740 pointsBadges:
    report
  • Babs312
    Hi Todd,
    It is the requirement from the receiving side.  I think they want to make it more readable.
    290 pointsBadges:
    report
  • ToddN2000
    Your best bet may be to define a flat record format for each record type. The using the BIF mention in this post concatenate them together. This would eliminate any spacing.

    Something like this.

    EVAL      MSG# = %TRIMR(MSG(37)) + ' ' +
                 %EDITC(DTITEM:'4')
    133,740 pointsBadges:
    report
  • Babs312

    Hi Todd,
    Thanks for the information.  It will work.

    290 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.

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

Following

Share this item with your network: