unable to read from a space delimited sequential file

[siddesai]

Hello All,

I have a sequential file with columns delimited by a space.

In all there are 4 columns in the source file.

Config settings -
1. Delimiter = white space
2. Final Delimiter = end
3. Column Type = Varchar
4. Column Length = Not specified

1 2 3 4

Code: Select all

51 01   07.04.2010 

Note that column 3 has a <<space>> value so when I read this row in view data DS reads column 4 in column 3. I have also specified null field value for column 3 as a ' ' but that doesn't seem to work either.

I have tested this with one single row and the problem seems to persist.

[siddesai]

For testing purpose, as soon as I add a value to the 3rd column, View data works fine but when I remove that value - same error.

[chvenkat.v]

write unix script to modify delimeter value and call that into before sub-routine.

[paultechm]

Read as a single varchar column, use convert function to replace 'space' into 'comma'(or any other) ,use column import to parse this into multiple columns


-Paul

[siddesai]

Thanks for your replies.

The problem with using single varchar and then convert function is that when a column has a space in it then it will treat it as a delimiter therefore I will again end up with consecutive delimiters and incorrect column mappings

E.g.
59.40
749.50

[chulett]

Then get whomever is producing the file for you to create it properly. Either quote the fields or use a delimiter that does not occur in the data. Or both.

[siddesai]

Unfortunately, I have zero control over that.

Craig, do you think there is any other solution to treat consecutive delimiter as one for my original post? Excel has it so I find it hard to believe DS doesnt have any option.



Thanks again.

[chulett]

I don't see how treating "consecutive delimiters as one" would help here but perhaps I'm missing something, late on a Sunday night. Seems to me that would just slide any following fields down into the wrong column.

Still, bottom line is your file is invalid as a delimited file. I know you may have no control over that but you can certainly present that to whomever produces it and make your concerns known. Can't imagine it would be all that hard for them to produce something... proper.

[Harini]

If you can do it in excel, either you convert it to excel sheet and then read the csv file in Datastage, or ask them to convert it and give it to you.

[jyothisdasms]

Read the whole file in one field without giving any delimitter.
Then use the change function to convert the two consecutive space into a single space.Then you can split the records using a Coulmn import stage giving space as delimitter

Let me know the results !!