2 Replies Latest reply: Nov 18, 2014 3:24 AM by Henric Cronström RSS

    Load data with delimiter?

      I am now stuck with a data load problem. I am trying to load a column of data that is separated using a comma. Is it possible?

      Here is the sample data -


      NameSports
      JoeBaseball, Football
      BillBowling, Football, Baseball
      FredTennis, Golf
      AlexGolf, Football

       

       

      So I want to load sports of different people and be able to visualize for example how many people like baseball?


      thanks for any help.



        • Re: Load data with delimiter?

          So I did find a solution for this, during the load statement -

          LOAD
              Name,
                  if(index(Sports,',')=0,Null(),SubField(Sports,',',1))as Sports_1,
              if(index(Sports,',')=0,Null(),SubField(Sports,',',2))as Sports_2,
              if(index(Sports,',')=0,Null(),SubField(Sports,',',3))as Sports_3,
              if(index(Sports,',')=0,Null(),SubField(Sports,',',4))as Sports_4
          FROM 'lib://Scratch/11111sports.xlsx'
          (ooxml, embedded labels, table is Sheet1);
          
          • Re: Load data with delimiter?
            Henric Cronström

            It is much easier (and cleaner) if you omit the third parameter of SubField():

             

            Load

                 Name,

                 Trim(SubField(Sports,',')) as Sport

            From ...

             

            HIC