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
dnat
Participant



Joined: 06 Sep 2007
Posts: 193

Points: 1789

Post Posted: Thu Nov 09, 2017 4:25 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi,

I have a .csv format file and it comes in the following way

For ex it has 4 fields

a,345,cd,678
b,"6,878",cd,"9,870"
c,"7,989",fg,"7,880"
d,567,cd,989

The second and the fourth field are numeric fields and if it is more than 3 digit number the it comes with a comma but within double quotes.
When i try view data, i am not able to view the 2nd and 3rd record. The issue is because how the final delimiter string is stored.
If i give Final delimiter as end then 1st and 4th record are viewable. If make Final delimiter as \n then 2nd and 3rd record are viewable. Any idea how to handle it?
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42220
Location: Denver, CO
Points: 216777

Post Posted: Thu Nov 09, 2017 5:16 pm Reply with quote    Back to top    

Not seeing off the top of my head how this is related to the final delimiter. However, I'm curious how did you define the metadata? To me they should all be defined as strings and then the 2nd and 4th fields will need to be converted inside the job post-read...

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
dnat
Participant



Joined: 06 Sep 2007
Posts: 193

Points: 1789

Post Posted: Thu Nov 09, 2017 5:28 pm Reply with quote    Back to top    

Yes, they are all defined as Strings. And i will be converting those fields as required inside the job.

But now the problem is with reading the file itself. While i view data in sequential file stage the 2nd and 3rd record get dropped if i specify the Final delimiter as "end" or "none". If i specify the Final delimiter as "\n" then 1st and 4th record get dropped.

The issue is definitely in the source file as they said that we will be sending the file with whatever formats they already have. But is there a way to handle this?
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 267

Points: 2686

Post Posted: Fri Nov 10, 2017 10:01 am Reply with quote    Back to top    

several things you may be able to do...

- you could not use numeric commas (123456 is written 123456 not 123,456)
- you could use something other than , for the fields (tab, |, #, whatever)
- you could parse the entire line by hand

the first 2 options may mean writing a pre-processor to convert the files. This is probably a simple line or 2 of unix magic commands or could be a short C program or a datastage transformer (which becomes the 3rd option).
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: 54038
Location: Sydney, Australia
Points: 293144

Post Posted: Fri Nov 10, 2017 5:12 pm Reply with quote    Back to top    

Try using a Server Sequential File stage (perhaps in a Server Shared Container). This stage is much more flexible about how it handles multiple formats.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
dnat
Participant



Joined: 06 Sep 2007
Posts: 193

Points: 1789

Post Posted: Mon Nov 13, 2017 12:05 pm Reply with quote    Back to top    

Thanks for all your inputs. I added the "Record delimiter string" to DOS format and was able to read the file as it was.
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