Find and Replace Query extracting from a .CSV or .XLS list

[lemc]

Hello,

I'm wondering if there is a way to find and replace multiple text in Indesign using data from.CSV or .XLS file.

The .CSV file contains 2 columns, column 1 contains the texts it needs to find and column the is the text it needs to replace.

I've tried the findbychange script but I don't know how to extract information from the .CSV file.

Example below column one shows the words to find and column two the corresponding words to replace. Basically I have more than 2000 unique words I needed to replace.

wheel	tire
hello	world
test	again

Thank you very much for the advice.

[Manan Joshi]

Hi lemc,

Your problem can be divided into two parts

• Parsing out the csv
• Using that parsed data to create a find/change text query

For parsing the csv you can look into a library for JS that can do it. Looking through the forum i found a discussion that you can look to get you started on this. Splitting a CSV with a Regular Expression

For find change query you can use the following code snippet, run the code snippet below in a loop that runs for the number of rows you parsed from the csv

app.findTextPreferences = app.changeTextPreferences = NothingEnum.nothing;  
app.findTextPreferences.findWhat = "the value that needs to be searched";  //replace the string in quote with the first value of the parsed row, like row[1][0]
app.changeTextPreferences.changeTo = "the replacement value";  //replace the string in quote with the second value of the parsed row, like row[1][1]
app.activeDocument.changeText();  
app.findTextPreferences = app.changeTextPreferences = NothingEnum.nothing; 

-Manan

[Michel_Lemieux]

Hi lemc,

Personally, I am more partial towards using tab delimited files but to each his own 😉

Here's my APPLESCRIPT answer, the follwing should do the trick:

property textDelim : ";" -- tab
set valueFile to ((path to desktop folder) as text) & "test.csv"
set myValues to read file valueFile using delimiter {return}
set myFindReplacePairs to {}
repeat with thisLine in myValues
  set end of myFindReplacePairs to my getPair(thisLine)
end repeat
on getPair(aLine)
  set {leftOfTab, rightOfTab} to my getTextItem(aLine, textDelim, 0)
  set leftOfTab to (my getTextItem(leftOfTab, ASCII character 10, 0)) as text
  return {findWhat:leftOfTab, replaceWith:rightOfTab}
end getPair
on getTextItem(thisString, thisDelim, thisItem)
  -- ThisString -> String to look in
  -- ThisDelim -> Text element that delimit the string
  -- ThisItem -> Number of the element to return (0 returns ALL)
  copy the text item delimiters to OldDelims
  set the text item delimiters to thisDelim
  set itemsList to every text item of thisString
  if class of thisItem is list then
  set fromItem to item 1 of thisItem
  set toItem to item 2 of thisItem
  set theReturn to (items fromItem thru toItem of itemsList) as text
  else
  if thisItem is not 0 then
  set theReturn to (item thisItem of itemsList) as text
  else
  set theReturn to itemsList -- return every items
  end if
  end if
  set the text item delimiters to OldDelims
  return theReturn
end getTextItem

this script populates the list myFindReplacePairs with records fo your pairs in the form of {findWhat: text, replaceWith: text}.

Depending on how your text file is setup (my Excel separates my columns with semi-colon when I save to comma separated values so I created a property (LINE 1) that lets you specify what is separating your values.

LINE 13 is there to strip potential Line Feed character (\n) from your data.

HTH
Michel

[willcampbell7 · Accepted solution]

It's not clear if you want advice on code, or a working script. I've had the same desire on my to-do list for a while. Most of the needed components I have from other scripts both public and private, so this morning I patched together the basics to accomplish your request. I don't have documentation prepared yet, but can tell you the basics -- the CSV or tab-delimited text requires a header row, the names of which can be specified and default to "find" and "change." You'll see when running the script. And the rest should be obvious. This quick solution is only text for now, not yet GREP, which I had always planned to also include (really my biggest desire for such a script, more so than just text changes; all in good time). It sounded like you needed a solution now so I figured whip up what I can and come back later to add polish and other features. Go here to download the script:

http://www.marspremedia.com/software/indesign/find-change-from-spreadsheet

You'll see the download button. Let me know anything that doesn't work right. My e-mail address is on the script UI.

[lemc]

Thanks William, I would like to check the script but the link does not work.

[willcampbell7]

Sorry about that... the link I copied was to my ".test" domain, my internal test server. I have it on my real server now...

https://www.marspremedia.com/software/indesign/find-change-from-spreadsheet

[lemc]

Thanks William, I was able to run the script it did search for the values but it did not change it.

[willcampbell7]

Hmm. I've tested again here and it works. Could you post a small sample of the data? (first few rows). You have added a header row I assume, either "Find" and "Change" or whatever you prefer and have entered into the UI to match. Yes?

[lemc]

Awesome! It worked now. Thank you for all the help!

[grudgemom]

William, how does your script handle words within a word kind of situations?

Like change "cat" to "dog" and you have the word "scatter" in your document. Will it find 'cat' inside 'scatter' and become 'sdogter'? Lol

Same thing for multi-word phrases like Canadian Equity Funds and Equity? I use the findchangebylist script and have to put the larger phrase at the top of the list so that it gets changed first, then the single word at the bottom of the list.

Find: Canadian Equity Funds       Change to: Fonds d’actions canadiennes

Find: Equity    Change to: Actions

If Equity is searched first, I end up with Canadian Actions Funds, then searching for Canadian Equity Funds is not found.

[willcampbell7]

Because the script did not yet have the "Whole word" option, it behaved as you describe and would replace "cat" (for example) within words ("sCATter" for example). I threw together the script quickly this morning from existing pieces of code, Now I'll address this issue. I have just added the "Whole word" option. If enabled this errant behavior will not occur (but remains an option in case the behavior IS desired). It's no different than using the standard Find/Change dialog. Well, other than saving untold times opening it and changing the values to find and replace. In other words, the portion of the script that does the changing is calling standard InDesign built-in functionality that the Find/Change dialog uses.

Also I've added some documentation to the web page, to make clear how the CSV/TXT must be prepared.

As for your other examples, again things behave just as if doing each change, one by one, in the InDesign Find/Change dialog. So yeah, if done out of order, things could go awry, whether doing it the normal way in InDesign or using this (or other) scripts.

However, I intend to add GREP to this, and possibly some features missing from InDesign GREP: look-behinds. This could help with nailing down specific find/change situations, in which a match only matches if specific words/phrases precede or follow the match. My other script "Text Cleanup" (https://www.marspremedia.com/software/indesign/text-cleanup) uses these tricks to limit what ends up matching.

For anyone following this thread, note the link in the "correct" answer is still wrong. I mistakenly copied my test server link, ".test" domain, but after replying to a reply, I can no longer edit that message. Change ".test" in the URL to ".com" and it works. Or use the link below, which is correct. Also the page now has more information (the start of some documentation).

https://www.marspremedia.com/software/indesign/find-change-from-spreadsheet

[grudgemom]

Thanks! Will be keeping an eye on your script to hopefully use in the future. Scripting the findchangebylist works fine for me, but my find list vs change list needs to be proofread and having it in a csv makes that much easier for the proofreaders.

[willcampbell7]

In the time since my last post about this, I've added to the script the option "Keep capitalization."

Same link for further explanation, and to download the latest version:

https://www.marspremedia.com/software/indesign/find-change-from-spreadsheet

Also, I've made a video tutorial for the script:

https://youtu.be/dpWGUUuAAGc

[willcampbell7]

What version of Illustrator? And is it non-English version I wonder?

At any rate, we can dodge the error with a try/catch, which I've done. Go to the webiste and download the updated version 1.7. Let me know if that works.

http://www.marspremedia.com/software/illustrator/find-replace-from-csv

[willcampbell7]

Thanks for letting me know. Looks like that is the next update... check the app name and alert user when script isn't compatible with the app. Your feedback is valuable. Thank you.