10 Replies Latest reply on Aug 26, 2014 9:06 AM by glorifindal

    Parsing a list issue

    glorifindal

      Hi there,

       

      I'm hoping someone here can help with parsing a list that I am importing from Excel ...

       

      I need to loop through it and do something with each of the paired values ... here is the structure:

       

      <Business Area>Retail

      <Business Status>Active - 24/7

      <Business Scope>All ENC

      <Business Approach>Cold

      <Business Description>This could well be some text, with some return seperated data also, making using the GetValue function useless...

      Customer Details

      Customer Address

      Customer Search

      Customer Security

      Contact History

      <Business Name>Offer of the Day

      <Business Status>In Development

      <Business Type>Desktop Application

      <Business Version>4.6

       

      So from the each loop I would need two variables -

      $_label = Business Area

      $_Value = Retail

      And so on.

       

      I can't use the GetValue function - due to the CR seperated values which are part of <Business Description> in this example - and could be part of any of the values really.

       

      This would give me:

      <Business Description>This could well be some text, with some return seperated data also, making using the GetValue function useless...

      Instead of:

      <Business Description>This could well be some text, with some return seperated data also, making using the GetValue function useless...

      Customer Details

      Customer Address

      Customer Search

      Customer Security

      Contact History

       

      I suppose what I am trying to work out is the value between each "<" and ">" for the label - easy enough.

      But working out the value from the ">" until the next "<" is eluding me.

       

      Any help greatly appreciated.

       

      Kindest regards

       

      Glorifindal

        • 1. Re: Parsing a list issue
          glorifindal

          Please excuse me - I forgot to mention that this is FMPA 11

          • 2. Re: Parsing a list issue
            SteveMartino

            Might be easier to offer some help if you are able so post your excel file

            • 3. Re: Parsing a list issue
              glorifindal

              The data structure above is the result of a custom function which captures (on a per record basis) only those fields that are actually populated...

              Some of the import tables have a massive amount of columns - and looping through every field of every record did not make sense.

              This resultant data structure was fine until some clients started including CRs in the data.

               

              So, to actually post the Excel file would not make any real sense, as this data structure presented above is the end result of some time saving scripting.

               

              Hope that makes sense...

               

              Many thanks

              • 4. Re: Parsing a list issue
                keywords

                I suggest you try to construct a calc that temporarily substitutes some other character likely to be unique (eg. the pipe character "|") while you complete your selection, then replaces it with the CR when you complet your processing. You should be able to do all of this within a single Let( ) caclulation.

                • 5. Re: Parsing a list issue
                  glorifindal

                  I think I tried this, however, substituting at the field level is not an option.

                   

                  Substituting within the Variable does not help either...

                   

                  For exmple, If I were to substitute the CRs for Pipes in the above data, I would get :

                   

                  <Business Area>Retail|<Business Status>Active - 24/7|<Business Scope>All ENC|<Business Approach>Cold|<Business Description>This could well be some text, with some return seperated data also, making using the GetValue function useless...|Customer Details|Customer Address|Customer Search|Customer Security|Contact History|<Business Name>Offer of the Day|<Business Status>In Development|<Business Type>Desktop Application|<Business Version>4.6

                   

                  In other words, ALL the pairs would be effected, and I would still be and I would still be left with the original problem ... which is trying to differentiate paired values from line values

                   


                  • 6. Re: Parsing a list issue
                    BruceHerbach

                    It sounds like you already have a script that generates the list from the excel spread sheet.  Can you have it add a unique character set at the end of each pair?  Say "|*|" 

                     

                    So now you would have something that looks like

                    <Business Area>Retail|*|<Business Status>Active - 24/7|*|<Business Scope>All ENC|*|<Business Approach>Cold|*|<Business Description>This could well be some text, with some return seperated data also, making using the GetValue function useless..|*|Customer Details|*|Customer Address|*|Customer Search|*|Customer Security|*|Contact History|*|<Business Name>Offer of the Day|*|<Business Status>In Development|*|<Business Type>Desktop Application|*|<Business Version>4.6|*|

                     

                    Next replace (Subsitute) all of CRs with "" or " ".  Then replace |*| with a CR.  Now you would have a list of pairs you can loop through.

                     

                    You can then use GetValue[loop counter] to get the pair.  in a set variable statement you can turn the pair in to a list by substituting ">" for a CR.  GetValue(1) gets the field and GetValue(2) gets the data.

                     

                    I think this is what Keywords may have had in mind. 

                     

                    Your Customer pairs and Contact pair may be a bit more tricky.  You can look at the first characher or leftwords functions to determine these.

                     

                    HTH

                    • 7. Re: Parsing a list issue
                      keywords

                      "I think this is what Keywords may have had in mind."

                       

                      That was the idea Bruce. Thanks for the amplification. If you are sure that this is the only pair affected you could even particularise the calc for the "business description" pair.

                      • 8. Re: Parsing a list issue
                        glorifindal

                        Many thanks for the input ...

                         

                        I cannot be sure that only one pair will be affected "

                        due to the CR seperated values which are part of <Business Description> in this example - and could be part of any of the values really." - please excuse my rotten english.

                         

                        I'm trying this for the moment ...

                         

                        Let( [

                         

                         

                        a =

                        "<Business Area>Retail

                        <Business Status>Active - 24/7

                        <Business Scope>All ENC

                        <Business Approach>Cold

                        <Business Description>This could well be some text, with some return seperated data also, making using the GetValue function useless...

                        Customer Details

                        Customer Address

                        Customer Search

                        Customer Security

                        Contact History

                        <Business Name>Offer of the Day

                        <Business Status>In Development

                        <Business Type>Desktop Application

                        <Business Version>4.6" ;

                         

                        $_c = PatternCount ( a ; "<" ) ;

                        _c2 = $_c + 1 ;

                        op = "<" ;

                        cl = ">" ;

                        pos1 = Position( a ; op ; 1 ; $_c ) ;

                        pos2 = Position( a ; cl ; 1 ; $_c ) ;

                        pos3 = Position( a ; op ; 1 ; _c2 ) ;

                        $_label = Middle( a ; pos1 + 1 ; pos2 - pos1 - 1 ) ;

                        $_data = Middle( a ; pos2 + 1 ; pos3 - pos2 - 1 )

                        ] ;

                        ""

                        )

                         

                        $_c will be reduced every loop -

                         

                        Seems to work - as long as there are no "<" OR ">" in the data :-)

                         

                        Again, many thanks for your input

                        • 9. Re: Parsing a list issue
                          BruceHerbach

                          HI,

                           

                          Take a look at the attached file.  It uses the initial text I suggested with the  |*| character set as a field delimiter.  Puts everything in a list and sets fields for the Business fields.  I left out the Customer and Contact ones because they looked like field names with out values. 

                           

                          Open the file and click the Parse button.

                           

                          I hope this clears up what we have been trying to suggest. 

                          • 10. Re: Parsing a list issue
                            glorifindal

                            Hi Bruce,

                             

                            (and Keywords)

                             

                            this works fine - thank you very much.

                            Using a slightly different Substitute I like it - many thanks indeed