Page 1 of 1

CSV to FB conversion

PostPosted: Wed Aug 08, 2012 3:07 pm
by Swapnilkumar
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.

Re: CSV to FB concersion

PostPosted: Wed Aug 08, 2012 3:49 pm
by BillyBoyo
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?

Re: CSV to FB concersion

PostPosted: Wed Aug 08, 2012 8:00 pm
by dick scherrer
Hello,

I suspect this took them some time to work thru and they wanted to share. . .

Re: CSV to FB conversion

PostPosted: Thu Aug 09, 2012 5:40 am
by BillyBoyo
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.

Re: CSV to FB conversion

PostPosted: Mon Aug 27, 2012 9:14 am
by Swapnilkumar
Billy Boyo, Thank you for the help, and I am sharing my knowledge on this forum :) ;)

Re: CSV to FB conversion

PostPosted: Mon Aug 27, 2012 10:12 pm
by skolusu
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)                     
//*