9 Replies Latest reply: Aug 24, 2017 1:39 PM by Antonio Mancini RSS

    Parsing String Fields

    Salamon Musayev

      Hello

       

      I am bringing a field called Textline2 with the following string structure

       

      AT CSICU ON 05/30/15 AT 1141 BY TestUser.

       

      I would like to parse the above string into the following columns

       

      Location          Date                      Time              User

      CSICU            5/50/15                   1141               TestUser

       

      I have tried using the subfield function but my results are parsing as follows

       

      String

      AT CSICU ON 05/30/15 AT 1141 BY TestUser

      .

      Code                                                                                                 My Result

       

         subfield(TextLine2, 'AT', 2 ) AS Location,                                             CSICU ON 05/30/15

         subfield(TextLine2, 'ON', 2 ) AS Date                                                   05/30/15 AT 1141 BY TestUser

          subfield(TextLine2, 'AT', 3 ) AS Time,                                                   1141 BY TestUser



      Please help.



        • Re: Parsing String Fields
          Sunny Talwar

          May be this

           

          Table:

          LOAD Trim(TextBetween(F1, 'AT', 'ON')) as Location,

          Trim(TextBetween(F1, 'ON', 'AT')) as Date,

          Trim(SubField(Mid(F1, Index(F1, 'AT', 2) + 2), 'BY', 1)) as Time,

          Trim(SubField(F1, 'BY', -1)) as User;

          LOAD * INLINE [

              F1

              AT CSICU ON 05/30/15 AT 1141 BY TestUser

          ];

          • Re: Parsing String Fields
            Antonio Mancini

            Hi Salamon,

            maybe this

             

            LOAD
            SubField(Field,' ',2) as Location,
            SubField(Field,' ',4) as Date,
            SubField(Field,' ',6) as Time,
            SubField(Field,' ',-1) as User
            Inline [ Field
            AT CSICU ON 05/30/15 AT 1141 BY TestUser
            ]
            ;

            Regards,

            Antonio

            • Re: Parsing String Fields
              Anand Chouhan

              Try wit subfield function here with combination

               

              LOAD *,
              SubField(Str,' ',1) as Location,
              SubField(Str,' ',2) as 2,
              SubField(Str,' ',3) as 3,
              SubField(Str,' ',4) as Date,
              SubField(Str,' ',5) as 5,
              SubField(Str,' ',6) as Time,
              SubField(Str,' ',7) as 7,
              SubField(Str,' ',8) as 8;
              LOAD Str
              FROM
              C:\Users\Home\Desktop\Qcomm\StrTable.xlsx
              (ooxml, embedded labels, table is Sheet1);
              

               

               

              Img3.PNG

              • Re: Parsing String Fields
                Michalina Kuczynska

                You  can try a combination of subfield & replace functions too:

                 

                test:

                load * Inline [

                Textline

                AT CSICU ON 05/30/15 AT 1141 BY TestUser

                ];

                 

                 

                 

                 

                test1:

                Load

                updated_text,

                subfield(updated_text, '//', 2) as location,

                    subfield(updated_text, '//', 3) as date,

                    subfield(updated_text, '//', 4) as time,

                    subfield(updated_text, '//', 5) as user

                ;

                Load

                replace(replace( replace(Textline, 'AT', '//') , 'ON', '//'), 'BY' , '//') as updated_text

                resident test;

                • Re: Parsing String Fields
                  bruno bertels

                  Hi

                  not sure of my approche and not tested but

                  Try with space character as delimiter :

                   

                  subfield(TextLine2,' ',2) as Location,

                  subfield (TextLine2,' ',3) as time ,

                  subfield(textline2,' ',4) as Date,

                  etc

                  • Re: Parsing String Fields
                    Anand Chouhan

                    Finally as your requirement load this ways

                     

                    LOAD *,
                    Second&' '&Third&' '&Date as LocationFinal,
                    Date &' '& Location&' '&Time &' '&Seventh &' '& Eight as DateFinal,
                    Time &' '& Eight as TimeFinal;
                    
                    
                    LOAD *,
                    SubField(Str,' ',1) as Location,
                    SubField(Str,' ',2) as Second,
                    SubField(Str,' ',3) as Third,
                    SubField(Str,' ',4) as Date,
                    SubField(Str,' ',5) as Fifth,
                    SubField(Str,' ',6) as Time,
                    SubField(Str,' ',7) as Seventh,
                    SubField(Str,' ',8) as Eight;
                    LOAD Str
                    FROM
                    C:\Users\Home\Desktop\Qcomm\StrTable.xlsx
                    (ooxml, embedded labels, table is Sheet1);