7 Replies Latest reply on Aug 1, 2010 3:37 AM by grefel

    Indesign Find And Replace Using External XL File

    tavlit

      hi all

      i have an indesign book in english with lots of repetative textual segments.

      the client sent me an excel file with 2 columns

      A1-A500  -  the english source

      B1-B500  -  the destination language

       

      i am looking for a script in java or grep that can do the following

       

      1 - select list of changes  - an xl file that has 2 columns A and B

      2 - put the A1 in the "find" area

      3  - put the B1 translation in the change area

      4  - run until line is empty (A501)

      5 Done

       

      i have the find and replace by list

      but that means i have to copy paste the list from xl to the script

       

      any ideas?

       

      Gil

        • 1. Re: Indesign Find And Replace Using External XL File
          grefel Level 2

          xl means excel? anyway, try to convert it to a csv file.

           

          if you're familar with regular expressions simply prepare you xl file with an external texteditor (e.g. notepad ++ http://notepad-plus-plus.org/) with the findChangeByList.jsx syntax.

           

          should be sth like:

          find: (.+?);(.+?)

          replace: text    {findWhat:"$1"}    {changeTo:"$2"}    {includeFootnotes:true, includeMasterPages:true, ...

           

           

          and the run the file with findchangebylist.jsx

           

          gregor

          • 2. Re: Indesign Find And Replace Using External XL File
            tavlit Level 1

            hi

             

            thanks gregor

            can you explain what it means?

            should be sth like:

            find: (.+?);(.+?)

            replace: text    {findWhat:"$1"}    {changeTo:"$2"}    {includeFootnotes:true, includeMasterPages:true, ...

            what is .+?
            what does $ means
            can you make the script serach by length of characters?
            suppose i have a sentanceL
            "hold the insert toolholder......."
            and i have a list of experssions: "insert toolholder, insert"
            i need to change "insert toolholder" before i translate the "insert"
            or i will have mixed sentences?
            any ideas?
            gil

            • 3. Re: Indesign Find And Replace Using External XL File
              grefel Level 2

              hey gil,

               

              my previous post was a little sloppy. he idea was to create the needed FindChangeList.txt file out of your xl file with the help of regular expressions instead of copy paste.

               

              For this you nedd to:

              1. save your excel file as csv
              2. change your csv file to FindChangeList.txt
              3. use it.


              problem is to convert your csv to the appropriate format: you need regular

              expressions for this. check out

              http://www.regular-expressions.info/tutorial.html

               

              the provided example was to select both columns in the csv file (assumed a ; as separator): (.+?);(.+?)

              .+? is a regular expression and means: select any character (one ore more times) until you find the delimiter (in this case ; ). the brackets are used to memorize the findings.

              in the replace dialog you can use the findings with $1 and $2 respetively: that menas you can put the contents of the first brackets (column) in the findWhat field and the second column in the changeTo field.


              after replacing a lines your FindChangeList is ready for use.

               

              you can search for a given length, but i do not get your example. what exactly is your sentence? what is in the first column what is in the second column of you xls file and what problems can occur?

               

              reagrds,

              gregor

              • 4. Re: Indesign Find And Replace Using External XL File
                tavlit Level 1

                dear gregor

                here is a link for the files

                read the read me first

                 

                thanks

                gil

                http://www.hagitdesigns.com/gregor/

                • 5. Re: Indesign Find And Replace Using External XL File
                  [Jongware] Most Valuable Participant

                  1. Your Excel conversion to FindChangeList format idea seems okay, apart from one thing: both findWhat and changeTo strings should have double quotes around them. As they are now,

                   

                  grep {findWhat:lang1"} {changeTo:"lang2} {incl...(etc.)

                   

                  the opening one after findWhat and the closing one after changeTo are missing.

                   

                  2. Your first line contains 2 definitions. Each separate definition should be on a line of its own. (This might occur elsewhere as well, didn't check.)

                   

                  3. Any double quotes inside strings will cause problems. Precede them with a backslash to 'escape' them and convert them to a literal character. I found this one:

                   

                  "(for IC5/8"V)"

                   

                  -- change to

                   

                  "(for IC5/8\"V)"

                   

                  4. Some of your strings start with double double quotes:

                   

                  changeTo:""(MITM刀具见254页 ...

                   

                  5. To prevent your problem with this order

                   

                  grep {findWhat:Inserts"} ...

                  grep {findWhat:Inserts Ordering Code System"} ...

                   

                  sort your Excel list alphabetically, case preserving, in reverse order.

                   

                  You probably also want wholeWord:true, and caseSensitive:true.

                   

                  Hope this helps!

                  • 6. Re: Indesign Find And Replace Using External XL File
                    grefel Level 2

                    hey jongware,

                     

                    you're too fast. i've got no chance to anser

                     

                    i did not find anything to add!

                     

                    gregor

                    • 7. Re: Indesign Find And Replace Using External XL File
                      grefel Level 2

                      yepp, found something to add:

                       

                      you do not need to export CSV.

                      For Unicode support simply export Unicode Text File Format from Excel. The resuling file is delimited with tabs.

                       

                      gregor