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

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

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