DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
celso.pupio
Participant



Joined: 20 Aug 2018
Posts: 3

Points: 33

Post Posted: Mon Aug 20, 2018 11:58 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
I have a sequential file where the delimiter is pipe character ("|"), but it is possible that a specific column has "|" in your content.

Example of a row of the file:
1234567|12345| Specific column|with pipe in the content |123456|2016-12-31 10:20:53.0|2017-01-31 19:10:26.0|||0|tx_tx_txtxtx|Txtxtxtxt|tx_tx_txtxt

I need to manipulate this column to replace the pipe character, before mapping the columns.

A friend is using regular expression to "isolate" the column on Pentaho Spoon software, like this:
1) searches the regex (^[0-9]{1,8}\|[0-9]{1,8}\|) to put double quotes before the column:
1234567|12345| " Specific column|with pipe in the content|123456|2016-12-31 10:20:53.0|2017-01-31 19:10:26.0|||0|tx_tx_txtxtx|Txtxtxtxt|tx_tx_txtxt

2) searches the regex (\|[0-9]{5,7}\|[0-9]{4}\-[0-9]{2}\-[0-9]{2}\ [0-9]{2}\:[0-9]{2}\:[0-9]{2}\.[0-9]\|[0-9]{4}\-[0-9]{2}\-[0-9]{2}\ [0-9]{2}\:[0-9]{2}\:[0-9]{2}\.[0-9]\||\|\|[0-9]{4}\-[0-9]{2}\-[0-9]{2}\ [0-9]{2}\:[0-9]{2}\:[0-9]{2}\.[0-9]\|[0-9]{4}\-[0-9]{2}\-[0-9]{2}\ [0-9]{2}\:[0-9]{2}\:[0-9]{2}\.[0-9]\|) to put double quotes after the column:
1234567|12345| " Specific column|with pipe in the content " |123456|2016-12-31 10:20:53.0|2017-01-31 19:10:26.0|||0|tx_tx_txtxtx|Txtxtxtxt|tx_tx_txtxt

3) and then he manipulates the substring between double quotes, to replace the pipe character.


Is it possible to implement this solution on DataStage?

Thanks for any help
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54407
Location: Sydney, Australia
Points: 295092

Post Posted: Mon Aug 20, 2018 8:52 pm Reply with quote    Back to top    

Welcome aboard.

Yes, the answer is quite simple. In the Sequential File stage that you are using to read the file, enable the Filter Command option. This will contain a command (or command pipeline) to be executed before the stage starts consuming rows, and the rows will then be consumed from stdout of that command.

So you would apply your regular expressions to the file in the Filter Command, and the stage would read the result.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
celso.pupio
Participant



Joined: 20 Aug 2018
Posts: 3

Points: 33

Post Posted: Tue Aug 21, 2018 3:11 pm Reply with quote    Back to top    

Thanks a lot, Ray.

Now I'm trying to implement the command to insert the double quotes after the first regex, but I'm having issues (it's new to me).

After looking for a solution on internet, I tried to use sed 's/(^[0-9]{1,8}\|[0-9]{1,8}\|)/\1"/' but I got an error that I could not solve yet.

Would anyone know where the error is?
Rate this response:  
Not yet rated
celso.pupio
Participant



Joined: 20 Aug 2018
Posts: 3

Points: 33

Post Posted: Tue Aug 21, 2018 3:35 pm Reply with quote    Back to top    

Guys, I got it using "sed -r".

Thanks for all!
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54407
Location: Sydney, Australia
Points: 295092

Post Posted: Tue Aug 21, 2018 9:31 pm Reply with quote    Back to top    

Cool

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours