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

[கற_பன___Imagine_]

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.

[mr_pathi]

Hi Karpanai,

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

Thanks, Thiru

[csm_phil]

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;
     app.changeGrepPreferences.changeTo=myChange;
     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

[mr_pathi]

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

[csm_phil · Accepted solution]

Hi Pathi,

I have modified your csv file input request.

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

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

[Laubender]

@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

[mr_pathi]

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

[csm_phil]

Hi Pathi,

Did you tried my above JS code?

thx

csm_phil

[mr_pathi]

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