Load comma separated values into different columns

[#1]

hi all,

I have a column with comma separated values like 1,2,3,4,5,6,7,8,9,10.

Can i separate this values into individuals columns with particular column name.

If possible Can anyone explain how can i do while loading the data.

Regards,

kumar

[jagan]

Hi,

Try generic load like below

Test:

LOAD ID, RowNo() AS No, SubField(Value, ',') AS SplitValue

INLINE [

    ID, Value

    1, "1,2,3,4,5"

];

LET vStart = NoOfTables();

Split:

Generic

LOAD

          *

Resident Test;

DROP TABLE Test;

LET vLoop = NoOfTables() - $(vStart);

//Rename first table name

RENAME Table Split.1 to Split;

FOR i = 1 to vLoop

LET vCurrentTable = 'Split.'& ($(i) + 1);

Outer Join(Split)

LOAD

          *

Resident

$(vCurrentTable);

DROP Table $(vCurrentTable);

NEXT

[paul_scotchford]

One simple way is use the import wizard and when it evaluates the file it will show the column names, you can rename them from there. Once the load has completed the script will be there for you to use/change as you like moving forward.

[stevedark]

If it is a fixed number of values, and you know what each one is, you can use SubField

LOAD

  FieldName,

  SubField(FieldName, ', ') as SubField1,

  SubField(FieldName, ', ') as SubField2,

  ...

If your string has a variable number of values, then extra columns will be null.

It will be possible, if required, to CROSSTABLE the resultant file, to turn it into multiple rows, with a single column and multiple rows.

Hope that helps.

Steve

[paul_scotchford]

My apologies, I misread your question.

The suggestions by Steve and Jagan are worth trying.

However, how is this source data originally created ? Is it going to be a regular data feed ?

Can the data provider modify their extraction process ?

Can you pre-process the file using SPSS , which will give you more power for transformation.

cheers

Paul

[#6]

Thanks for the reply jagan,

I have used below script,

LOAD `user_id`,

    name,

    value,

    `parser_version`;

SQL SELECT `user_id`,

    name,

    value,

    `parser_version`

FROM rcdb.accountprofile;

BUDGET_CALC_DATA:

LOAD `user_id`,

    name as budget_calc_profile_name,

    value as budget_calc_profile_value;

SQL SELECT `user_id`,

    name,

    value

FROM accountprofile WHERE name="BUDGET_CALC_DATA" ;

BUDGET_CALC:

LOAD `user_id`,

  SubField(budget_calc_profile_value, ',' ,1) as data,

    SubField(budget_calc_profile_value, ',' ,2) as cardpayments,

    SubField(budget_calc_profile_value, ',' ,3) as Loanpayments,

    SubField(budget_calc_profile_value, ',' ,4) as Groceries,

    SubField(budget_calc_profile_value, ',' ,5) as education,

    SubField(budget_calc_profile_value, ',' ,6) as Out,  

    SubField(budget_calc_profile_value, ',' ,7) as payment,

    SubField(budget_calc_profile_value, ',' ,8) as gas,

    SubField(budget_calc_profile_value, ',' ,9) as Cable,

    SubField(budget_calc_profile_value, ',' ,10) as phone,

    SubField(budget_calc_profile_value, ',' ,11) as expenses,

    SubField(budget_calc_profile_value, ',' ,12) as Entertainment,

    SubField(budget_calc_profile_value, ',' ,13) as Transportation,

    SubField(budget_calc_profile_value, ',' ,14) as Insurance,

    SubField(budget_calc_profile_value, ',' ,15) as Other,

    SubField(budget_calc_profile_value, ',' ,16) as Savings

    Resident BUDGET_CALC_DATA;

Drop table BUDGET_CALC_DATA;

The issue is data has different delimiter , can i use the condition to check the delimiter?

Sample Data:

16,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,   ----- Parser_version = V2

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15|||16  ----- Parser_version = V1

I have above types of data how can i differentiate these types and load data in single table, the delimiter "|||" is not working

[jagan · Accepted solution]

Hi,

Try like this

BUDGET_CALC:

LOAD `user_id`,

  SubField(budget_calc_profile_value, ',' ,1) as data,

    SubField(budget_calc_profile_value, ',' ,2) as cardpayments,

    SubField(budget_calc_profile_value, ',' ,3) as Loanpayments,

    SubField(budget_calc_profile_value, ',' ,4) as Groceries,

    SubField(budget_calc_profile_value, ',' ,5) as education,

    SubField(budget_calc_profile_value, ',' ,6) as Out, 

    SubField(budget_calc_profile_value, ',' ,7) as payment,

    SubField(budget_calc_profile_value, ',' ,8) as gas,

    SubField(budget_calc_profile_value, ',' ,9) as Cable,

    SubField(budget_calc_profile_value, ',' ,10) as phone,

    SubField(budget_calc_profile_value, ',' ,11) as expenses,

    SubField(budget_calc_profile_value, ',' ,12) as Entertainment,

    SubField(budget_calc_profile_value, ',' ,13) as Transportation,

    SubField(budget_calc_profile_value, ',' ,14) as Insurance,

    SubField(budget_calc_profile_value, ',' ,15) as Other,

    SubField(budget_calc_profile_value, ',' ,16) as Savings;

LOAD

user_id,

Replace(Replace(Replace(budget_calc_profile_value, '|||', ','), '||', ','), '|', ',') AS budget_calc_profile_value

    Resident BUDGET_CALC_DATA;

[#8]

How Can I use If condition?

Is below expression is correct?

if (parser_version="V2",(SubField(budget_calc_profile_value, ',' ,1) as Data),

    (SubField(budget_calc_profile_value, ',' ,-1) as Data)),

    .....

[#9]

In V2 the column1 value is in first place but in V1 it is 16 th place.

How can i differentiate and place 16 th place value to 1st column?

[jagan]

HI,

Try like this

If(parser_version='V2', SubField(budget_calc_profile_value, ',' ,1), SubField(budget_calc_profile_value, ',' ,16)) AS Data

If(parser_version='V2', SubField(budget_calc_profile_value, ',' ,1), SubField(budget_calc_profile_value, ',' ,-1)) AS Data

Hope this helps you.

Regards,

Jagan.