CSV to FB conversion



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

CSV to FB conversion

Postby Swapnilkumar » Wed Aug 08, 2012 3:07 pm

Here is good example of CSV/VB file data conversion to FB data.

We needed to take the file into mainframe format and sort that accordingly removing all the QUOTES and PIPES from it. There were 32 input fields in the input layout.
The fields were having size in fixed format. "123"/"988" in the first record was of 10 bytes but the actual data was of 3 bytes such variations were there while data was punched.
So we needed to sort the quotes before "123"/"988" after the field and skip the pipe character and put the appropriate field as proper locations. And after this we needed to convert this data in to the mainframe file.

The data was as shown below.
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
********************************* Top of Data **********************************
"123"¦"6218888"¦"FEeeee"¦"THiMAS"¦"H"¦""¦"MD, NTN"¦"FARMER SURVICAL APCD"   
"988"¦"12120584"¦"TTRBER"¦"Kuehn"¦"H"¦""¦"DD, CLS"¦"Poultry  ARTS"


How we solved it:

We develpoed the following DFSORT card which can be explained as mentioned below.
//STEP01    EXEC  PGM=SORT                                       
//SORTIN    DD DISP=SHR,DSN=INPUT FILE   
//SORTOUT   DD DSN=O/P FILE,DISP=OLD   
//SYSOUT    DD SYSOUT=*                                         
//SYSPRINT  DD SYSOUT=*                                         
//SYSIN     DD *                                                 
 OPTION COPY                                                     
 OUTREC PARSE=(%01=(ENDBEFR=C'"',FIXLEN=1),                     
               %02=(ENDBEFR=C'"¦"',FIXLEN=10),                   
               %03=(ENDBEFR=C'"¦"',FIXLEN=09),                   
               %04=(ENDBEFR=C'"¦"',FIXLEN=35),                   
               %05=(ENDBEFR=C'"¦"',FIXLEN=20),                   
               %06=(ENDBEFR=C'"¦"',FIXLEN=01),                   
.
.
.                   
               %32=(ENDBEFR=C'"',FIXLEN=10)),                       
        BUILD=(%02,%03,%04,%05,%06,...,%32)                                 
/*       


~ PARSE operand can be used with INREC, OUTREC or OUTFIL to define rules that tell DFSORT how to extract the relevant data from each variable input field into a fixed parsed field
~ We define a parsed field for converting a variable field to a fixed parsed field using a %nn name where nn can be 00 to 99. Each %nn parsed field must be defined only once.
~ ENDBEFR=C'"' instructs DFSORT to extract data upto the byte before the next quote.
~ ENDBEFR=C'"|"' instructs DFSORT to extract data upto the byte before the next quote and PIPE character along with quote.
~ FIXLEN sets the length of the parsed field
~ BUILD operand is used to construct the output record.
____________________________________________
Regards,
- Swapnilkumar.
Swapnilkumar
 
Posts: 12
Joined: Tue Aug 07, 2012 10:05 pm
Location: Pune, India
Has thanked: 0 time
Been thanked: 0 time

Re: CSV to FB concersion

Postby BillyBoyo » Wed Aug 08, 2012 3:49 pm

OK, Swapnilkumar, what is this about?

There are much better ways to use PARSE for this task than that you have shown. Why have you posted?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: CSV to FB concersion

Postby dick scherrer » Wed Aug 08, 2012 8:00 pm

Hello,

I suspect this took them some time to work thru and they wanted to share. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: CSV to FB conversion

Postby BillyBoyo » Thu Aug 09, 2012 5:40 am

Hello,

Yes, I suppose. Initially posted in "Forum Rules" :-)

Swapnilkumar,

You could look at STARTAFT as well as ENDBEFR. With both set to quote, the pipe becomes irrelevant.

If you want to "ignore" a parsed field, like your %01, you can just specify %, leaving all the remaining parsed fields still available.

You can also use PARSE with IFTHEN (you don't need it in your example, but you mentioned INREC, OUTREC and OUTFIL).

If you got your solution from scratch, well done.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: CSV to FB conversion

Postby Swapnilkumar » Mon Aug 27, 2012 9:14 am

Billy Boyo, Thank you for the help, and I am sharing my knowledge on this forum :) ;)
____________________________________________
Regards,
- Swapnilkumar.
Swapnilkumar
 
Posts: 12
Joined: Tue Aug 07, 2012 10:05 pm
Location: Pune, India
Has thanked: 0 time
Been thanked: 0 time

Re: CSV to FB conversion

Postby skolusu » Mon Aug 27, 2012 10:12 pm

Swapnilkumar wrote:Billy Boyo, Thank you for the help, and I am sharing my knowledge on this forum :) ;)


Well we appreciate your enthusiasm to share your knowledge, however they are not optimized solutions. So please take time to learn and share optimized solutions. You can get rid off the double quotes with a FINDREP and then parse delimiting just the '¦' .

ex:
//SYSIN    DD *                                     
  OPTION COPY                                       
  INREC FINDREP=(INOUT=(C'"',C''))                 
  OUTREC PARSE=(%01=(ENDBEFR=C'¦',FIXLEN=10),       
                %02=(ENDBEFR=C'¦',FIXLEN=09),       
                %03=(ENDBEFR=C'¦',FIXLEN=35),       
                %04=(ENDBEFR=C'¦',FIXLEN=20),       
                %05=(ENDBEFR=C'¦',FIXLEN=20)),     
   BUILD=(%01,%02,%03,%04,%05)                     
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post