9 Replies Latest reply on Dec 28, 2011 7:46 PM by mr.pathi

    Search and replace, contents from excel file

    mr.pathi

      Hi

       

      I have the list in the excel file which needs to replace. The script should read the excel then replace to the opened InDesign documents.

       

      The excel(*.xls) file has two columns, first column has old nos. and the second column has the new numbers to replace.

       

      Ex:

      Column 1               Column 2

      (Search)               (Replace)

      -----------------------------------

      4257/2          =>               1/2

      4257/3          =>               1/3

      4257/4          =>               1/4

      ....

       

      3257/2          =>               1/5

      3257/3          =>               1/6

      3257/4          =>               1/7

      ....

       

      4457/4          =>               2/2

      4457/5          =>               2/3

      4457/6          =>               2/4

      4457/7          =>               2/5

      ......

       

      Thanks in Advance,

      Thiru

        • 1. Re: Search and replace, contents from excel file
          கற்பனை (Imagine) Level 3

          Hi Thiru,

           

          read the XLS file line by line, and colums are separated in xls by tab characters, you get the value of each rows colums value use the split() function.

           

          I hope this help to you.

          • 2. Re: Search and replace, contents from excel file
            csm_phil Level 4

            Hi Thiru,

             

            Try the below JS code is easy way to achieve your requirement.

             

            myFind array is finding values.
            myChange array is changing the values.

             

             

             

            var myFind = ['4257/2', '4257/3', '4257/4', '3257/2', '3257/3', '3257/4', '4457/4', '4457/5', '4457/6', '4457/7'];
            var myChange = ['1/2', '1/3', '1/4', '1/5', '1/6', '1/7', '2/2', '2/3', '2/4', '2/5'];

             


            for(var i=0; i<myFind.length; i++){
                 app.findGrepPreferences = app.changeGrepPreferences = NothingEnum.nothing;
                 app.findGrepPreferences.findWhat = myFind[i];
                 app.changeGrepPreferences.changeTo=myChange[i];
                 app.documents.item(0).changeGrep();
                 app.findGrepPreferences = app.changeGrepPreferences = NothingEnum.nothing;
                 }

             

             

            Otherwise image information you have to try to ready xls file and split the values.

             

            thx

            csm_phil

            1 person found this helpful
            • 3. Re: Search and replace, contents from excel file
              mr.pathi Level 1

              Hi CSM,

               

              Thanks for your response. This is ok but the problem is mostly I will have more than 500 numbers to replace, so i have to copy all that numbers from excel to script. It will take time also. So if the script reads the excel it would be very helpful...

               

              Thanks

              Thiru

              • 4. Re: Search and replace, contents from excel file
                mr.pathi Level 1

                Hi Karpanai,

                 

                Can you give me the whole script, please? I am new to script.

                 

                Thanks, Thiru

                • 5. Re: Search and replace, contents from excel file
                  csm_phil Level 4

                  Hi Pathi,

                   

                  I have modified your csv file input request.

                   

                  Please save as the xls to tab delimetted text. refer the below snapshot.

                   

                  Picture 3.png

                   

                  Next run the provided scripts it will change the values.

                   

                   

                  var myFile = File.openDialog("Choose a tab delimitted txt file:");
                  if (!myFile){
                  exit();
                  }     
                  
                  myFile.open('r');
                  while (myFile.eof==false){
                       line=myFile.readln();
                       line = line.split("\t");
                       myFind = line[0];
                       myChange = line[1];
                       app.findGrepPreferences = app.changeGrepPreferences = NothingEnum.nothing;
                       app.findGrepPreferences.findWhat = myFind;
                       app.changeGrepPreferences.changeTo=myChange;
                       app.documents.item(0).changeGrep();
                       app.findGrepPreferences = app.changeGrepPreferences = NothingEnum.nothing;
                       }
                  myFile.close();
                  

                   

                  thx

                  csm_phil

                  • 6. Re: Search and replace, contents from excel file
                    Laubender Adobe Community Professional & MVP

                    @csm_phil:

                    this is a very radical method, because:
                    1. you don't limit your changeGREP() to a specific table column (possibly the searched texts are found in other strings around the document and changed as well!)

                    2. you are in trouble if a certain value you want to change exists more than one time (not likely, but who knows?) and you want to change it in diffrent values per instance. That will fail.

                     

                    If we deal with only ONE column of ONE table, to address issue 1 I would ask the user to select the column of the table and instead of:

                    app.documents.item(0).changeGrep();
                    

                    I would use:

                    app.selection[0].changeGrep();
                    

                     

                    I don't hope issue 2 will apply in this case.

                     

                    @mr. pathi:

                    However, in the case of only one column of one table to change AND if the new excel file contains ALL entries in the exact order (both all old and all new values), you could change the contents of the column by copying from excel to InDesign. No script necessary. The formatting of the old cells will be retained.

                     

                    You cannot do that by placing the new table to InDesign and copy/paste the new column to the old one, because the formatting of the copied column will be transferred as well (paste without formatting is not an option here).

                     

                    Uwe

                    • 7. Re: Search and replace, contents from excel file
                      mr.pathi Level 1

                      Hello Uwe,

                       

                      Thanks, and for your info. this is not a table, the numbers which i mentioned it could be in different places of text. Numbers can be in page 2 and it can be page 15 also.

                       

                      The numbers which are in the excel file that all numbers should be replaced in the all places (where they used) of text.

                       

                      Tanx,

                      Thiru

                      • 8. Re: Search and replace, contents from excel file
                        csm_phil Level 4

                        Hi Pathi,

                         

                        Did you tried my above JS code?

                         

                        thx

                        csm_phil

                        • 9. Re: Search and replace, contents from excel file
                          mr.pathi Level 1

                          Hi CSM,

                           

                          Thanks a lot, its working fine. Your code is working for current active document. But i need to replace for all opened docs. So just i changed below code from your code and working good.

                           

                          app.documents.item(0).changeGrep(); to app.documents.everyItem().changeGrep();

                           

                          Thanks,

                          Thiru