Creating CSV file from AS/400 fixed length physical file

Babs312

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: August 20, 2014 12:06 PM Last updated: September 5, 2014 5:29 AM

Answer Wiki

Last updated: August 20, 2014 2:11 PM GMT

ToddN200099,905 pts.
History
Contributors
- Ordered by most recent
- ToddN200099,905 pts.

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

hunshabbir7 Aug 21, 2014 7:51 AM GMT

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 Aug 21, 2014 8:18 AM GMT

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 Aug 21, 2014 5:01 PM GMT
Edit your numeric fields with the %editc or %editw BIFs, e.g.:
```
%trim(%editc([numeric field name]: 'P'))
```
12,260 pointsBadges:
report
Splat Aug 21, 2014 5:02 PM GMT

CPYTOIMPF can be used whether the file format contains one or many fields.

12,260 pointsBadges:

report
Babs312 Aug 22, 2014 2:33 AM GMT

Hi All,

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

290 pointsBadges:

report
ToddN2000 Sep 3, 2014 7:47 PM GMT

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?

99,905 pointsBadges:

report
Babs312 Sep 4, 2014 9:19 AM GMT

Hi Todd,
It is the requirement from the receiving side. I think they want to make it more readable.

290 pointsBadges:

report
ToddN2000 Sep 4, 2014 11:50 AM GMT
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')    
```
99,905 pointsBadges:
report
Babs312 Sep 5, 2014 5:29 AM GMT

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

290 pointsBadges:

report