Splitting the row

dodda · Post by **dodda** » Wed Aug 12, 2009 10:13 am

Hello

I have a requirement where i will be getting the row such as

1234^;abcdefgh^;bc^;d^;efg^;rghu^;klmn^;bcd

each field delimited by ^;

Is there a way that i can extract each field by using Field function.

I tired using the Field function but it didnt work .Earlier they used the single Field separator (;) but noe they came up with field separator as (^;).

Does the Filed function work?

thanks

chulett · Post by **chulett** » Wed Aug 12, 2009 11:31 am

The Field function works fine but it does require a single character delimiter, just like the way the Column Import stage does. So you have at least two choices that comes to my mind.

1. Pre-process the file to change all occurrences of those two characters to a single character that you are certain does not occur otherwise in the data.

2. Pick one of the two characters as the Field() delimiter and then trim the other from the data. So, if you used "^" as the delimiter, you'd need to trim any leading ";" from the data the Field function retrieved. Of course, this assumes that you would never find a "^" by itself as data.

This has been discussed before, so I'm sure there's other good advice already out there.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Aug 12, 2009 1:12 pm

Why do you want to extract using field() and not define them as seperate fields in the metadata ?

chulett · Post by **chulett** » Wed Aug 12, 2009 1:38 pm

They would except for the fact that multi-character delimiters are not supported.

ray.wurlod · Post by **ray.wurlod** » Wed Aug 12, 2009 4:44 pm

Have you investigated using the Field Delimiter String property in the sequential file stage?

chulett · Post by **chulett** » Wed Aug 12, 2009 5:12 pm

D'oh! I looked at the "Delimiter" property and it had as an option "<other> => A single ASCII character". I didn't see the "Delimiter String" property.

ray.wurlod · Post by **ray.wurlod** » Wed Aug 12, 2009 9:03 pm

Worth logging in today, then, wasn't it? The same thing applies at the record level - to specify DOS-style (two-character) record delimiter, you need to replace Record Delimiter property with Record Delimiter String property.

chulett · Post by **chulett** » Wed Aug 12, 2009 9:16 pm

Right, know about the DOS record delimiter, just forgot about the field delimiter string as an option. Am I missing something with the Column Import stage as well, does it have the same 'delimiter string' option? Not somewhere where I can check tonight.

algfr · Post by **algfr** » Thu Aug 13, 2009 5:56 am

Is that resolved ?

ragasambath · Post by **ragasambath** » Sat Aug 15, 2009 10:36 am

dodda wrote:Hello

I have a requirement where i will be getting the row such as

1234^;abcdefgh^;bc^;d^;efg^;rghu^;klmn^;bcd

each field delimited by ^;

Is there a way that i can extract each field by using Field function.

I tired using the Field function but it didnt work .Earlier they used the single Field separator (;) but noe they came up with field separator as (^;).

Does the Filed function work?

thanks

Hello dodda,

A Simple work around , replace ^; using UNIX tr or sed command with ; character
You can execute these command using in build Before Sub routines ExecSH

Thanks

chulett · Post by **chulett** » Sat Aug 15, 2009 10:42 am

No 'workaround' needed when the stage supports reading a file like this via the Delimiter String property.

DSXchange

Splitting the row

Splitting the row

Re: Splitting the row