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
 

 

Removing Trailing Spaces from a CSV file

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Michaelod
Warnings : 1

New User


Joined: 02 Sep 2008
Posts: 49
Location: Edinburgh

PostPosted: Wed Jan 20, 2010 6:13 pm    Post subject: Removing Trailing Spaces from a CSV file
Reply with quote

Hi,

I wonder if anyone can help.

I have the following data with a fixed length of 30

Code:
1,SGL01      ,GBP     ,ACK   
1 ,SGL 01    ,GBPEUR  ,ACK1   


I want to remove the trailer spaces so that the file looks like:

Code:
1,SGL01,GBP,ACK
1 ,SGL 01,GBPEUR,ACK1


Thanks
Back to top
View user's profile Send private message

PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Wed Jan 20, 2010 6:20 pm    Post subject:
Reply with quote

Only trailing space? No leading spaces?
It has to be a sort product to do that?
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7914
Location: Bellevue, IA

PostPosted: Wed Jan 20, 2010 6:32 pm    Post subject:
Reply with quote

You say "trailing spaces" but your example removes spaces that are not trailing. Perhaps you can more fully explain just what it is you're attempting to do?

Trailing spaces means spaces at the end of the RECORD, not the end of a field. Any spaces before ACE and ACE1 in your example records are not trailing spaces.
Back to top
View user's profile Send private message
Michaelod
Warnings : 1

New User


Joined: 02 Sep 2008
Posts: 49
Location: Edinburgh

PostPosted: Wed Jan 20, 2010 6:45 pm    Post subject:
Reply with quote

I do not want to remove all spaces.........only spaces that are trailing between commas:

Input

Code:
1,SGL01      ,GBP     ,ACK   
1 ,SGL 01    ,GBPEUR  ,ACK1   


Output

Code:
1,SGL01,GBP,ACK
1,SGL 01,GBPEUR,ACK1
Back to top
View user's profile Send private message
Michaelod
Warnings : 1

New User


Joined: 02 Sep 2008
Posts: 49
Location: Edinburgh

PostPosted: Wed Jan 20, 2010 6:58 pm    Post subject:
Reply with quote

The required outout is as the 2nd post. I put a space between the 1 and the , in error in the first post.
Back to top
View user's profile Send private message
Michaelod
Warnings : 1

New User


Joined: 02 Sep 2008
Posts: 49
Location: Edinburgh

PostPosted: Thu Jan 21, 2010 2:53 pm    Post subject:
Reply with quote

Can anyone help?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jan 21, 2010 8:26 pm    Post subject:
Reply with quote

Hello,

Suggest you should be testing your own code by now. . .

Unless you can wait longer. . .
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7914
Location: Bellevue, IA

PostPosted: Thu Jan 21, 2010 8:30 pm    Post subject:
Reply with quote

You posted in the DFSORT forum and the experts in this forum are on the west coast of the U.S. -- I doubt they were paying much attention at 1:23 AM when you last posted. If the problem is so urgent, you could have written code (in, for example, COBOL, REXX, or PL/I) by now to accomplish your goal.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Jan 21, 2010 11:49 pm    Post subject:
Reply with quote

Michaelod,

I was out sick yesterday.

Are the fields in fixed positions or are they in different positions delimited by a comma?

It appears you want to keep embedded blanks. Can only the second field have embedded blanks (as shown in your example) or can all of the fields have embedded blanks.

Please show a better example of input and output with more variations including embedded blanks in any fields that can have them, and fields in different positions if that's allowed.
Back to top
View user's profile Send private message
Michaelod
Warnings : 1

New User


Joined: 02 Sep 2008
Posts: 49
Location: Edinburgh

PostPosted: Fri Jan 22, 2010 3:29 pm    Post subject:
Reply with quote

Hi Frank,

All the fields could potentially have embedded blanks. We are unsure of the exact file layout at the moment hence the reason I have just given an example.

I have tried a few sort jobs but can only seem to remove the trailing blanks immediately prior to the comma.

Here is another example. The commas will always be in the same place in the input. The following file has a fixed length of say 50 bytes:

Code:
1   ,SGL01    ,GBP EUR        ,ACK 1
12  ,SGL02 2  ,GBP EUR        ,ACK2
15  ,SGL0222  ,GBP EUR USD    ,ACK4


Thanks

Should look like:

Code:
1,SGL01,GBP EUR,ACK 1
12,SGL02 2,GBP EUR,ACK2
15,SGL0222,GBP EUR USD,ACK4
Back to top
View user's profile Send private message
Bill O'Boyle

CICS Moderator


Joined: 14 Jan 2008
Posts: 2502
Location: Atlanta, Georgia, USA

PostPosted: Fri Jan 22, 2010 6:33 pm    Post subject: Reply to: Removing Trailing Spaces from a CSV file
Reply with quote

The following is from the Mainframe COBOL forum. With some minor modifications to the "TO-TBL" (in the link) and other logic, this may solve your issue.

Comments are provided in the code -

http://www.ibmmainframes.com/viewtopic.php?p=143786&highlight=#143786

Bill
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Fri Jan 22, 2010 9:23 pm    Post subject:
Reply with quote

Hi,

As always, this may not be the optimal way of doing this, but the below code works icon_smile.gif


Code:
//XK89JOB2 JOB ,'BAD',CLASS=4,MSGCLASS=P,NOTIFY=XK89       
//STEP2   EXEC PGM=ICETOOL                                 
//TOOLMSG DD SYSOUT=*                                       
//DFSMSG  DD SYSOUT=*                                       
//IN1     DD DSN=XK89.SORTIN,DISP=SHR                       
//IN2     DD DSN=XK89.SORTOUT,DISP=OLD                     
//IN3     DD DSN=XK89.SORTOUT.CLONE,DISP=OLD               
//TOOLIN  DD *                                             
  COPY FROM(IN1) TO(IN2) USING(CTL1)                       
  COPY FROM(IN2) TO(IN3) USING(CTL2)                       
/*                                                         
//CTL1CNTL DD *                                             
  INREC FINDREP=(IN=C'  ',OUT=C'')                         
/*                                                         
//CTL2CNTL DD *                                             
  INREC FINDREP=(IN=C' ,',OUT=C',')                         
/*                                                         


XK89.SORTOUT can be some TEMP file, and
XK89.SORTOUT.CLONE is the output file.

Thanks,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Jan 22, 2010 10:36 pm    Post subject:
Reply with quote

Michaelod,

Here's a DFSORT job that will do what you asked for:

Code:

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB/50)
//SORTOUT DD DSN=...  output file (FB/50)
//SYSIN    DD    *
  OPTION COPY
  INREC IFOUTLEN=50,
  IFTHEN=(WHEN=INIT,
   BUILD=(1:1,4,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=7),
   8:6,9,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=12),
   20:16,14,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",',LENGTH=17),
   37:32,19,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'"',LENGTH=34))),
  IFTHEN=(WHEN=INIT,
     BUILD=(1,70,SQZ=(SHIFT=LEFT,PAIR=QUOTE))),
  IFTHEN=(WHEN=INIT,FINDREP=(IN=C'"',OUT=C''))
/*
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Jan 22, 2010 10:41 pm    Post subject:
Reply with quote

Vasanth,

A better version of your job would be:

Code:

//S2   EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=...  input file (FB/50)
//SORTOUT DD DSN=...  output file (FB/50)
//SYSIN DD *
  OPTION COPY
  INREC IFTHEN=(WHEN=INIT,
    FINDREP=(IN=C'  ',OUT=C'')),
   IFTHEN=(WHEN=INIT,
    FINDREP=(IN=C' ,',OUT=C','))
/*


However, it would not handle the case of multiple embedded blanks correctly. My job would. For example, if the input record was:

Code:

1  3,A  B  C D E,XYZ  ABC DEF GH ,ACK  523         


My job would correctly output:

Code:

1  3,A  B  C D,E,XYZ  ABC DEF,H ,ACK  523   


whereas your job would incorrectly output:

Code:

13,ABC D E,XYZABC DEF GH,ACK523         
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Sun Jan 24, 2010 2:00 am    Post subject:
Reply with quote

Thanks Frank,

Its always a delight to learn from u
I assumed that there were only 1 blank in each field.






P.S i was drunk when i coded it icon_biggrin.gif
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Jan 25, 2010 11:14 pm    Post subject:
Reply with quote

Quote:
I assumed that there were only 1 blank in each field.


Not an unreasonable assumption.

Quote:
P.S i was drunk when i coded it


Perhaps that explains why you used two passes instead of one. icon_lol.gif
Back to top
View user's profile Send private message
Michaelod
Warnings : 1

New User


Joined: 02 Sep 2008
Posts: 49
Location: Edinburgh

PostPosted: Tue Jan 26, 2010 3:24 pm    Post subject:
Reply with quote

Excellent Frank - Thanks for your help.
Back to top
View user's profile Send private message
NeilDev

New User


Joined: 20 Jan 2010
Posts: 5
Location: UK

PostPosted: Sun Jan 31, 2010 10:00 pm    Post subject:
Reply with quote

Hi Frank,

I'm still having a problem with the SQZ function.

My input data is this (I have added quotes between fields in a previous process):
Code:
"OGL_21","12","31-DEC-2007","AABSBEPB1           ","EUR",


My output is this:
Code:
OGL_21,12,31-DEC-2007,AABSBEPB1           ,EUR,


Running the following code:
Code:

OPTION COPY                                     
INREC IFOUTLEN=920,                             
IFTHEN=(WHEN=INIT,                             
     BUILD=(1,920,SQZ=(SHIFT=LEFT,PAIR=QUOTE))),
  IFTHEN=(WHEN=INIT,FINDREP=(IN=C'"',OUT=C'')) 


Notice the quotes are being dropped correctly, but the SQZ is not removing trailing balnks e.g.
Code:
AABSBEPB1           ,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Feb 01, 2010 11:10 pm    Post subject:
Reply with quote

PAIR=QUOTE tells DFSORT NOT to remove blanks between quote pairs.

I suspect you aren't adding the quotes in your "previous process" in the right way to do what you want.

Showing part of what you did that didn't work without explaining what you're trying to do makes it impossible for anyone to help you. I'm locking this topic. Please start a new Topic and show a good example of your input records and what you expect for output with all relevant cases. Explain the "rules" for getting from input to output. Give the RECFM and LRECL of your input file. Give the starting position, length and format of each field, or the delimiters and maximum lengths for each field.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 1 Fri Dec 09, 2016 4:40 am
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm


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