6 Replies Latest reply on Oct 2, 2013 2:25 PM by raybaudi

    Text formated as "labels" to something more useful?

    flybynight

      Not necessarily a FMP question… if the easiest solution is with FMP, that's cool. If there is a way to do this with a text editor like TextWrangler, I'm all ears for that too! Here goes…

       

      We have a client that has a "mailing list" that isn't in a list format. It was formatted to print on Avery labels. What we received is a PDF, that had been created from Word. Our client does not have access to whatever created the list.

       

      What I am able to do is select all of the text in Acrobat and copy-paste it into a text editor, or whatever.

      The problem is that it is not delimited in any nice way to differentiate fields and records. Basically, both fields and records are delimited with a return. So it looks something like this:

       

      Name

      Title

      Address

      City, ST Zip

      Name

      Title

      Address

      Address2

      City, ST Zip

      etc...

       

      Note that MOST of the records have 4 lines, but some have 5, and a few have 3. Some addresses have commas in there, so I can't identify the City line as being the only line with a comma. What I can do is manually go through and force everything to be 4 lines (combine address 1 and 2 when necessary.

       

      From that, does anyone have any tips to do a find-replace the first, second and third returns with a tab, but keep the fourth return, then repeat that pattern? Can I do something like that with a looping script in FMP?

       

      Thanks for any ideas!

      -Shawn

        • 1. Re: Text formated as "labels" to something more useful?
          usbc

          Hi,

          You don't say what operating system they (or you) use or if you have a working relationship with them.. But if it is a Windows machine you might take a look at all the many conversion applications that Avery offers.

          http://www.winsite.com/csv/csv+csv/?titlematchtype=3&authormatchtype=3&license=all&os=all

          • 2. Re: Text formated as "labels" to something more useful?
            flybynight

            I'm on a Mac, but I do have Windows machines available to me.

            I took a quick look at that link. I don't think those will do what I'm looking for. Most of them look like they are for converting FROM csv to something else. I'm basically trying to get TO a useful format like csv, tab-delimited, FMP, Excel, whatever. Anything standard. Right now, I just have a long string of text, with each "field" on the next line, then repeating for every record.

             

            Laters,

            -Shawn

            • 3. Re: Text formated as "labels" to something more useful?
              Mike_Mitchell

              Are the zip codes in a consistent format - like all 5 digits, all ZIP+4? You might could write a script to go over the list and say something like,

               

              If [ Filter ( RightWords (( GetValue ; $count ) ; 1 ) ; "0123456789" ) = RightWords (( GetValue ; $count ) ; 1 ) ]

                 {do stuff here to divide into new record}

              End If

               

              Just a thought.

               

              Mike

              • 4. Re: Text formated as "labels" to something more useful?
                flybynight

                That is a possibility, but it would also grab a lot of addresses that are "PO Box 12345" or something like that.

                 

                Can you script a single find and replace? That is, don't do a "find and replace" for every instance of a string, but only find the first one, replace it and move on?

                If so, then I could get the text to a consistent 4-lines per address, and do something like:

                 

                Loop

                     Find ( [return character])

                     Replace with [tab]

                     Find ( [return character])

                     Replace with [tab]

                     Find ( [return character])

                     Replace with [tab]

                     Find ( [return character])

                     Replace with [~!@#$%^]

                End Loop

                 

                Then after that, run a find & replace for "~!@#$%^" and change it back to a return character.

                 

                (Obviously, totally made-up syntax there. I don't know if this is something that can be done or not. I've been playing with it and I'm not seeing what I want it to do.)

                 

                Thanks,

                -Shawn

                • 5. Re: Text formated as "labels" to something more useful?
                  Mike_Mitchell

                  Yes, you could do that. You would use the PatternCount function. Just look for the first occurrence. Then replace that occurrence with your new character. Lather, rinse, repeat.

                  • 6. Re: Text formated as "labels" to something more useful?
                    raybaudi

                    flybynight ha scritto:

                     

                    if the easiest solution is with FMP, that's cool.

                    I would use a CF like this, after you "force everything to be 4 lines" :

                     

                    /*

                    CustomReplace ( text )

                    */

                     

                     

                    Let([

                    $i = $i + 1 ;

                    pos = Position ( text ; ¶ ; 1 ; 1 ) ;

                    result = If ( pos ; If ( Mod ( $i ; 4 ) ; Replace ( text ; pos ; 1 ; Char ( 9 ) ) ; Replace ( text ; pos ; 1 ; "§" ) ) ; text )

                    ];

                    Case(

                    pos ; CustomReplace ( result ) ;

                    Let ( $i = "" ; Substitute ( result ; "§" ; ¶ ) )

                    )

                    )