Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

VB CSV to VB CSV

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 41
Location: Amsterdam

PostPosted: Fri Nov 11, 2011 8:03 pm    Post subject: VB CSV to VB CSV
Reply with quote

Dear all,

I'm stuck. I have an input record VB CSV and I am trying to create an output record VB CSV.

This is the input:

Code:
29177901,F,EQ,F_A,CV,T,USD,,,,11/5/2011 2:47:39. AM,SYS.1007,12,,,11/5/2011 2:47:39. AM,29177913, 


Quote:
Field1 29177901
Field2 F
Field3 EQ
Field4 FA
Field5 CV
Field6 T
Field7 USD
Field8
Field9
Field10
Field11 11/5/2011 2:47:39. AM
Field12 SYS.1007
Field13 12
Field14
Field15
Field16 11/5/2011 2:47:39. AM
Field17 29177913
Field18


I am expecting the following:

Quote:
Field1 29177901
Extra1 <empty>
Field14
Field15
Field17 29177913
Field2 F
Field3 EQ
Field18
Field5 CV
Field6 T
Field7 USD
Field8
Field9
Field10
Extra2 2011-11-11-hh.mm.ss
Extra2 2011-11-11-hh.mm.ss
Field12 SYS.1007


But what I actually get is:

Quote:
Field1 29177901
Extra1 ,
Field14 ,
Field15 ,
Field17 11/5/2011 2:47:39. , <<--Incorrect
Field2 , <<--Incorrect
Field3 EQ,
Field18 ,
Field5 CV,
Field6 , <<--Incorrect
Field7 USD {many spaces},
Field8 {many spaces},
Field9 {10 spaces},
Field10 {10 spaces},
Extra2 2011-11-11-15.09.59.000000,
Extra2 2011-11-11-15.09.59.000000,
Field12 SYS.1007


I'm using this:

Code:
 SORT FIELDS=COPY,SKIPREC=14                 
 INREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=20),
              %02=(ENDBEFR=C',',FIXLEN=1),   
              %03=(ENDBEFR=C',',FIXLEN=15), 
              %=(ENDBEFR=C','),             
              %04=(ENDBEFR=C',',FIXLEN=256),
              %05=(ENDBEFR=C',',FIXLEN=1),   
              %06=(ENDBEFR=C',',FIXLEN=256),
              %07=(ENDBEFR=C',',FIXLEN=256),
              %08=(ENDBEFR=C',',FIXLEN=10), 
              %09=(ENDBEFR=C',',FIXLEN=10), 
              %=(ENDBEFR=C','),             
              %10=(ENDBEFR=C',',FIXLEN=256),
              %=(ENDBEFR=C','),             
              %11=(ENDBEFR=C',',FIXLEN=20), 
              %12=(ENDBEFR=C',',FIXLEN=20), 
              %13=(ENDBEFR=C',',FIXLEN=20), 
              %=(ENDBEFR=C','),             
              %14=(ENDBEFR=C',',FIXLEN=256)),
BUILD=(1,4,                           /
       5:%01,JFY=(SHIFT=LEFT),         
       C',',                           
       C',',                           
       %11,JFY=(SHIFT=LEFT,LEAD=C'"', 
                            TRAIL=C'"')
       C',',                           
       %12,JFY=(SHIFT=LEFT,LEAD=C'"', 
                           TRAIL=C'"'),
       C',',                           
       %13,JFY=(SHIFT=LEFT,LEAD=C'"', 
                           TRAIL=C'"'),
       C',',                           
       %02,JFY=(SHIFT=LEFT,LEAD=C'"', 
                           TRAIL=C'"'),
       C',',                           
       %03,JFY=(SHIFT=LEFT,LEAD=C'"', 
                           TRAIL=C'"'),
       C',',                           
       %14,JFY=(SHIFT=LEFT,LEAD=C'"', 
                           TRAIL=C'"'),
       C',',                           
       %04,JFY=(SHIFT=LEFT,LEAD=C'"', 
                           TRAIL=C'"'),
       C',',                           
       %05,JFY=(SHIFT=LEFT,LEAD=C'"', 
                           TRAIL=C'"'),
       C',',                             
       %06,JFY=(SHIFT=LEFT,LEAD=C'"',   
                           TRAIL=C'"'), 
       C',',                             
       %07,JFY=(SHIFT=LEFT,LEAD=C'"',   
                           TRAIL=C'"'), 
       C',',                             
       %08,JFY=(SHIFT=LEFT),           
       C',',                             
       %09,JFY=(SHIFT=LEFT),           
       C',',                             
       DATE1(-),C'-',TIME1(.),C'.000000',
       C',',                             
       DATE1(-),C'-',TIME1(.),C'.000000',
       C',',                             
       %10,SQZ=(SHIFT=LEFT))           
   OUTREC IFTHEN=(WHEN=INIT,                                   
    BUILD=(1,4,5,1500,SQZ=(SHIFT=LEFT,PAIR=QUOTE))),           
    IFTHEN=(WHEN=INIT,FINDREP=(IN=C'"',OUT=C'')),             
    IFTHEN=(WHEN=INIT,FINDREP=(IN=C'6/6/2066',OUT=C'1/1/0001'))


I have found that if I make the
Code:
%02=(ENDBEFR=C',',FIXLEN=1),   
a FIXLEN of 2 the "F" is picked up !

Sorry for the long post.... maybe someone can see sthing obvious... I have searched the forums but I've not found anything.

Daniel icon_rolleyes.gif
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Nov 11, 2011 8:42 pm    Post subject: Reply to: VB CSV to VB CSV
Reply with quote

By my count you are jumping over 17th field with just %. Is 17th a typo?
Back to top
View user's profile Send private message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 41
Location: Amsterdam

PostPosted: Fri Nov 11, 2011 8:49 pm    Post subject:
Reply with quote

Bill,

the 17th field I don't want in the output. which is why the %.

Dan.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Nov 11, 2011 8:52 pm    Post subject: Reply to: VB CSV to VB CSV
Reply with quote

OK, so what do you mean when you put Field17 in your example? I assumed the 17th input field as I couldn't make any other sense of it. What now :-)
Back to top
View user's profile Send private message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 41
Location: Amsterdam

PostPosted: Fri Nov 11, 2011 8:54 pm    Post subject:
Reply with quote

Bill, acutally you have a point the fields shoudl be

Code:
%12=(ENDBEFR=C',',FIXLEN=20), 
%=(ENDBEFR=C','),               
%13=(ENDBEFR=C',',FIXLEN=20), 


This explains the incorrect field 17. Thanks.

But I still don't get why Field 2 is empty when it should be F

Dan.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Nov 11, 2011 8:57 pm    Post subject: Reply to: VB CSV to VB CSV
Reply with quote

One at a time, I was thinking. Field6 is also wrong. Both 6 and 2 are single bytes... don't know, yet.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Nov 11, 2011 9:05 pm    Post subject: Reply to: VB CSV to VB CSV
Reply with quote

Only question I have so far, is why left-justify a (two) one byte field(s)? Shouldn't be a problem that I can see, but...

If that is no different, see if you can get the field6 value with the length-of-two technique you stumbled on to.
Back to top
View user's profile Send private message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 41
Location: Amsterdam

PostPosted: Fri Nov 11, 2011 9:06 pm    Post subject:
Reply with quote

I've taken all the LEAD and TRAILS off and it seems to work now !

Code:
BUILD=(1,4,                       
       5:%01,JFY=(SHIFT=LEFT),   
       C',',                     
       C',',                     
       %11,JFY=(SHIFT=LEFT),     
       C',',                     
       %12,JFY=(SHIFT=LEFT),     
       C',',                     
       %13,JFY=(SHIFT=LEFT),     
       C',',                     
       %02,JFY=(SHIFT=LEFT),     
       C',',                     
       %03,JFY=(SHIFT=LEFT),     
       C',',                     
       %14,JFY=(SHIFT=LEFT),      etc etc


The file looks like:

Code:
29177901,,,,29177913,F,EQ,,CV,T,USD,,,,2011-11-11-16.32.03.0000002011-11-11-16.32.03.000000,SYS.1007


Which is correct.

I'm not sure why it's behaing like that though.

Dan.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Nov 11, 2011 9:23 pm    Post subject: Reply to: VB CSV to VB CSV
Reply with quote

OK. I'll leave it to the experts now. I did my bit counting the commas...

Have a good weekend.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Fri Nov 11, 2011 10:09 pm    Post subject:
Reply with quote

Daniel Prosser,
I am still lost in counting positions icon_redface.gif icon_redface.gif

However, try to have a field for which you want to retain space in between and see if it still works. For example, your sample input timestamp looked as below "11/5/2011 2:47:39. AM". If you expect to have space between :39. and AM, you would need to have LEAD and TRAIL.

Thanks,
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Fri Nov 11, 2011 10:13 pm    Post subject: Reply to: VB CSV to VB CSV
Reply with quote

maybe You have less fingers than Bill icon_biggrin.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Nov 11, 2011 10:51 pm    Post subject: Reply to: VB CSV to VB CSV
Reply with quote

Yes, it is a "both socks off" job.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Nov 11, 2011 11:00 pm    Post subject: Reply to: VB CSV to VB CSV
Reply with quote

Got it. The LEAD and TRAIL plus your data all have to fit in the length of the field.

You got "A" in a one-byte field, later tossing away the quotes. When that goes in a one-byte field, you just get ", when it goes in a two-byte field you get "A, then just A when you toss away the (by now only one) quote.

That doesn't make me an expert :-) Just looked at page 298 of the PDF I downloaded a few months ago.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Fri Nov 11, 2011 11:09 pm    Post subject: Reply to: VB CSV to VB CSV
Reply with quote

Quote:
That doesn't make me an expert :-) Just looked at page 298 of the PDF I downloaded a few months ago.


sorry to contradict You... knowing where and how to look for the needed info makes You an expert icon_cool.gif

the best consultant is not the one who knows the right answers, but the one who knows the right questions
Back to top
View user's profile Send private message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 41
Location: Amsterdam

PostPosted: Mon Nov 14, 2011 12:55 pm    Post subject:
Reply with quote

Bill,

that's brilliant. Well done to you and your 298page plus pdf :-)

thanks,

Daniel icon_cool.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:



Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us