2 Replies Latest reply on Dec 21, 2012 5:16 PM by luisrosario

    Parsing Text

    luisrosario

      Hello, i have a list of adresses wich is driving me crazy.

      in the same line i have the address, the district and the city, with different number of letter or words, the only thing i havo to help me is that the district is between hiffens "-", so i'm trying to separate the field filtering by the hiffens. but some thing is went wrong. the adress i can separate, like this:

      Position (Adress; "-" ; 1 ; 1 )

      How can i refer for the district, wich is between the hiffens? or refer to the city wich is after the second hiffen?

      Is there any way to make a kind of calculation using the Position function, like Position 1 - Position 2 = something (it could be number of characters for the district.

       

      an exemple of my adress list:

       

      Adress - District - City

      AV. CONSTANTINO STOCCO, 999 - VILA GUILHERME - SANTA CRUZ DAS PALMEIRAS

      AV. LEÃO XIII, 9999 - RIBEIRANIA - RIBEIRÃO PRETO

      AV. DR. JOSÉ C. MONTEIRO DA SILVA FILHO, 9999 - NOVA ALIANÇA - RIBEIRÃO PRETO

      AV. JOÃO BATISTA DE OLIVEIRA, 850 - JARDIM SANTA JULIA V - ARARAQUARA

      R. S. JOSÉ, 1111 - CENTRO - RIBEIRÃO PRETO

      AV. PROF. JOÃO FIUSA, 00001 - ALTO DA BOA VISTA - RIBEIRÃO PRETO

       

       

      thanks in advance,

        • 1. Re: Parsing Text
          ch0c0halic

          I'd substitute a return for the " - " to process one line at a time with GetValue().

           

          For example (In a script):

           

          Loop

          1. Start at line 1 of the address list

          Set var ($line_number ) = $line_number + 1

          1. Use a single Set Var() with a let to generate all three variables

          Set var ( $var ) =

          Let ( [

          line = GetValue ( address_list ; 1 ) ;

          separate_values = Substitute ( line ; " - " ; "¶" ) ;

          $Address = GetValue ( separate_values ; 1 ) ;

          $District = GetValue ( separate_values ; 2 ) ;

          $City = GetValue ( separate_values ; 3 ) ;

          $address

          )

           

          1. Assumes if no address then we've already processed the last line

          Exit loop if ( IsEmpty ( $Address )

           

          1. Assuming here you want a new record for each address in the list.

          Create new record

          #fill data

          Set field (Address ; $Address )

          Set field (Address ; $District )

          Set field (Address ; $City )

           

           

          End loop

          1 of 1 people found this helpful
          • 2. Re: Parsing Text
            luisrosario

            Thanks for your help. After a few hours of Google i find this formula that make me finish my work.

             

            at www.dwaynewright.com

             

            Let([

            $start = Position ( text ; "("; 1; 1)+1;

            $end = Position ( text ; ")"; 1; 1);

            $difference = $end - $start];

             

            Middle ( text; $start; $difference))

             

            i made some modifications and all finished well.

             

            The final script with replace field content

             

            for Adress:

            Left( Table::Endereco completo; Position ( Table::Endereco completo ; " - " ; 1 ; 1 )-1)

             

            for District:

            Let([

            $start = Position ( Table::Endereco completo ; "-"; 1; 1)+2;

            $end = Position ( Table::Endereco completo ; " -"; 1; 2);

            $difference = $end - $start];

            Middle ( Table::Endereco completo; $start; $difference))

             

            for City:

            Let([

            $start = Position ( Table::Endereco completo ; "-"; 1; 1);

            $end = Position ( Table::Endereco completo ; "-"; 1; 2)+2;

            $total = Length ( Table::Endereco completo )+2;

            $diffTotal = $total - $end;

            $difference = $end - $start];

            Middle ( Table::Endereco completo; $end; $diffTotal))

             

            Thanks for your help. I'll try your way and i´ll put here the result.