topic Re: Convert string (comma separated) to multiple columns in Connectivity & Data Prep

Convert string (comma separated) to multiple columns

Wed, 11 Nov 2015 16:29:34 GMT

Dont ask me why but I have some values stored as comma separated values(CSV) in a column.

I am basically trying to make a loadscript that simulates a excel CSV import, where each of the delimited values end up in a separate column.

Below is an example of two rows in one column:

6000000, , '6006627, '6005599, '6005203, '6003813, '6105008001, '1050006765

, '6002000, '6002092, '6002012, '6006995, '6007703, '6001080, '6206001175

The output that i am looking for is:

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7
6000000	'6003000	'6005599	'6005203	'6003813	'6105008001	'1050006765
6000000	'6002092	'6002012	'6006995	'6007703	'6001080	'6206001175

Please note that there might be more than 7 delimited values and as you can see the length of each value also varies 😕

Appreciate any help!

Best Regards,

Anders

Re: Convert string (comma separated) to multiple columns

swuehl — Wed, 11 Nov 2015 21:50:31 GMT

Maybe like this:

INPUT:

LOAD * INLINE [

Field

6000000, , '6006627, '6005599, '6005203, '6003813, '6105008001, '1050006765

, '6002000, '6002092, '6002012, '6006995, '6007703, '6001080, '6206001175

] (delimiter is '|';

RESULT:

LOAD *

From_Field (INPUT,Field)

(txt, utf8, explicit labels, delimiter is ',', no quotes);

Re: Convert string (comma separated) to multiple columns

Thu, 12 Nov 2015 13:45:27 GMT

Thank you for the swift reply swuehl!

I found a solution last night before i saw your comment.

Basically i just spammed the following until i made sure i had more columns than delimited string variables (then i deleted the surplus) - it worked like a charm.

select

STRTOK(Field,',',1) as Field_1,

STRTOK(Field,',',2) as Field_2,

STRTOK(Field,',',3) as Field_3,

STRTOK(Field,',',4) as Field_4,

.....

from xx

Re: Convert string (comma separated) to multiple columns

swuehl — Thu, 12 Nov 2015 14:18:29 GMT

Yes, you can do similar using the QV function SubField() in a LOAD statement.

But you would need to know the max amount of possible new fields ahead. Probably you know it then.

Re: Convert string (comma separated) to multiple columns

Thu, 12 Nov 2015 14:24:45 GMT

Yeah, i had some difficulties with the SubField. I'm not sure if it was because the number of delimited variables was unknown Luckily i got it fixed in the sql statement