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
 

 

Update specific columns of a csv/delimited file

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

New User


Joined: 29 Mar 2010
Posts: 4
Location: india

PostPosted: Tue Jun 04, 2013 7:36 pm    Post subject: Update specific columns of a csv/delimited file
Reply with quote

I have a csv file whose one row looks as follows -

12345,"London",5,"12-05-2009",198765,"UPSC","15-04-2010",...


I want to remove the double quotes from the columns 4 and 7 which contains date. Rest all the columns should remain as it is.

So output should look like -

12345,"London",5,12-05-2009,198765,"UPSC",15-04-2010,..
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 04, 2013 7:47 pm    Post subject:
Reply with quote

Check here and run the job to find DFSORT LEVEL and let us know the output
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7240

PostPosted: Wed Jun 05, 2013 1:55 am    Post subject: Reply to: Update specific columns of a csv/delimited file
Reply with quote

Why do you want to do that? If something is reading a delimited file, it should itself know what to do with "s... so why do they have to come off for one type of field?
Back to top
View user's profile Send private message
Ibrahim85

New User


Joined: 29 Mar 2010
Posts: 4
Location: india

PostPosted: Wed Jun 05, 2013 1:49 pm    Post subject:
Reply with quote

The Analyst wants the date to be without quotes so that it can be loaded to informatica as "dates" and not "strings".

As per him if dates are in double quotes then Informatica will look at them as strings rather than dates.
Back to top
View user's profile Send private message
Ibrahim85

New User


Joined: 29 Mar 2010
Posts: 4
Location: india

PostPosted: Wed Jun 05, 2013 1:52 pm    Post subject:
Reply with quote

@ Pandora

The DFSORT version we are using is
5694-A01, Z/OS DFSORT V1R12
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Jun 05, 2013 3:13 pm    Post subject:
Reply with quote

Can't you select the date as CHAR?
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: Wed Jun 05, 2013 7:16 pm    Post subject:
Reply with quote

Hello,

We also would like to see the ICExxx messages - especially the ICE201I message.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Jun 05, 2013 9:59 pm    Post subject:
Reply with quote

Assuming you only have quotes for 2,4,6,7 fields

Try the following.

1. Use INREC IFTHEN WHEN=INIT with FINDREP to replace 2nd field quotes to a special character (ex: @) and use DO=2. The DO will ensure only field2 quotes are replaced to @

2. Use IFTHEN WHEN=INIT with FINDREP to replace 4th field quotes to NULL and use DO=2. The DO will ensure only date quotes are removed.

3. Repeat step 1 as is to save field 6 and replace it with special character.

4. Repeat step 2 as is to remove quotes from field 7 the date field

5. Now use IFTHEN WHEN=INIT with FINDREP to replace the special character back to double quotes and use DO=4.
Back to top
View user's profile Send private message
Ibrahim85

New User


Joined: 29 Mar 2010
Posts: 4
Location: india

PostPosted: Thu Jun 06, 2013 2:42 pm    Post subject:
Reply with quote

@ Dick Sherrer

ICE201I H RECORD TYPE IS V - DATA STARTS IN POSITION 5
ICE150I 1 VLSHRT NOT USED FOR SORT, MERGE, INCLUDE, OMIT OR SUM STATEMENT FIELDS
ICE751I 0 C5-K62149 C6-K90026 C7-K58148 C8-K67572 E9-K60824 C9-BASE E5-K72040
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 Jun 06, 2013 6:54 pm    Post subject:
Reply with quote

Thanks.

Quote:
ICE201I H RECORD TYPE IS V - DATA STARTS IN POSITION 5

The H indicates the specific fix-level for the code being run.

When there are new questions, it will help if you include the ICE messages and message ids.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7240

PostPosted: Thu Jun 06, 2013 10:23 pm    Post subject:
Reply with quote

Ibrahim85 wrote:
The Analyst wants the date to be without quotes so that it can be loaded to informatica as "dates" and not "strings".

As per him if dates are in double quotes then Informatica will look at them as strings rather than dates.


"Someone" is putting quotes around the dates. Why not fix it there, where the delimited file is created?

That aside, have you tried what Kolusu suggested?
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:

Similar Topics
Topic Author Forum Replies Posted
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 -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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