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

New Here ,
Jan 23, 2019 Jan 23, 2019

Copy link to clipboard

Copied

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.

wheeltire
helloworld
testagain

Thank you very much for the advice.

TOPICS
Scripting

Views

973

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Contributor , Jan 25, 2019 Jan 25, 2019
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 s...

Likes

Translate

Translate
Adobe Community Professional ,
Jan 23, 2019 Jan 23, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 24, 2019 Jan 24, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 25, 2019 Jan 25, 2019

Copy link to clipboard

Copied

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.

William Campbell
Mars Premedia

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 25, 2019 Jan 25, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 25, 2019 Jan 25, 2019

Copy link to clipboard

Copied

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

William Campbell
Mars Premedia

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 25, 2019 Jan 25, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 25, 2019 Jan 25, 2019

Copy link to clipboard

Copied

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?

William Campbell
Mars Premedia

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 25, 2019 Jan 25, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 25, 2019 Jan 25, 2019

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jan 25, 2019 Jan 25, 2019

Copy link to clipboard

Copied

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

William Campbell
Mars Premedia

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 28, 2019 Jan 28, 2019

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Mar 15, 2021 Mar 15, 2021

Copy link to clipboard

Copied

LATEST

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

 

William Campbell
Mars Premedia
InDesign JavaScript reads a spreadsheet of find/change pairs and replaces each instance of matching text in the active InDesign document. A virtually unlimit...

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines