13 Replies Latest reply: Jan 29, 2016 2:55 AM by Kumar Pramod RSS

    Load comma separated values into different columns

    Kumar Pramod

      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

        • Re: Load comma separated values into different columns
          jagan mohan rao appala

          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

            • Re: Load comma separated values into different columns
              Kumar Pramod

              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

              • Re: Load comma separated values into different columns
                Kumar Pramod

                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)),

                    .....

              • Re: Load comma separated values into different columns
                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.

                • Re: Load comma separated values into different columns
                  Steve Dark

                  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