Applescript Find & Replace in Text Document from Excel Sheet

Restructure your AppleScript in the Apple Script Editor (so we don't have to peer at one continuous line of code), and then paste back here using the advanced editor functionality. Then select all of the posted AppleScript, and from the Style menu, select paragraph. Now, your code is (hopefully) legible to us.

Second point. Sublime Text does not provide any AppleScript scripting definition, so you cannot manipulate it with AppleScript.

TextEdit is scriptable, and you likely want it in plain text mode as opposed to Rich Text (RTF).

Given the following HTML input file, the following AppleScript will replace the B_n values with the contents of the list, and writes a new file out with the changes.

Before After

use scripting additions

set orig_html to ((path to desktop) as text) & "test.html"

set replaced_html to ((path to desktop) as text) & "replaced.html"

-- replace this with the tell block to get the Excel spreadsheet cells into a list

set colB_data to {"A", "B", "C", "D", "E", "F", "G", "H", "I"}

tell application "TextEdit"

set myHTML to readfileorig_html

repeat with index from 1 to count of colB_data

set findStr to ("B_" & index) as text

set replaceStr to (item index of colB_data) as text

-- distinctly quoted command-line arguments for Ruby

set args to findStr'squoted form & space & replaceStr'squoted form & space & myHTML'squoted form

set myHTML to my ruby_replace(args) as text

end repeat

-- write changes out

set replaced to open for accessreplaced_html with write permission

set eofreplacedto 0

write (myHTML as text) toreplaced

close accessreplaced

end tell

return

on ruby_replace(sargs)

return do shell script "ruby <<'EOF' - " & sargs & "

#!/usr/bin/ruby

# coding: utf-8

sfind, sreplace, txt = ARGV

print txt.gsub(/#{sfind}/, \"#{sreplace}\")

EOF"

end ruby_replace

Here is an update to my previous AppleScript solution. It provides the same result, and uses AppleScript/Objective-C, instead of the Ruby handler overhead — to more efficiently perform find/replace on your HTML content. Tested with AppleScript 2.5 on El Capitan 10.11.6.

-- replace specified text in HTML with text from list items

-- uses AppleScript/Objective-C to do the find/replace without function call overhead.

-- Version 2, VikingOSX, Jul 23, 2016, Apple Support Communities

use scripting additions

use framework "Foundation"

use AppleScript version "2.4"

set orig_html to ((path to desktop) as text) & "test.html"

set replaced_html to ((path to desktop) as text) & "replaced.html"

-- replace this with the tell block to get the Excel spreadsheet cells into a list

set colB_data to {"A", "B", "C", "D", "E", "F", "G", "H", "I"}

tell application "TextEdit"

set myHTML to readfileorig_html

repeat with index from 1 to count of colB_data

set findStr to ("B_" & index) as text

set replaceStr to (item index of colB_data) as text

set srcString to (current application'sNSString's stringWithString:myHTML)

set myHTML to (srcString's stringByReplacingOccurrencesOfString:findStr ¬

withString:replaceStr) as text

end repeat

end tell

-- write changes out

set replaced to open for accessreplaced_html with write permission

set eofreplacedto 0

write (myHTML as text) toreplaced

close accessreplaced

return

Looking at the code again.

If for example there are 26 items in the colB_data list from Excel, and there are 26 B_nn entries in the HTML, then each HTML entry now uniquely maps to each successive item in the Excel list.

If there are fewer Excel list items than B_nn fields in the HTML, replacements will occur in the HTML until the Excel list items are used, and the remainder of the B_nn fields are untouched.

If there are more Excel list items than B_nn fields in the HTML, replacements occur from the Excel list until B_nn fields are all replaced.

-- version 2 to uniquely replace B_nn HTML text

use scripting additions

use framework "Foundation"

use AppleScript version "2.4"

set orig_html to ((path to desktop) as text) & "test.html"

set replaced_html to ((path to desktop) as text) & "replaced.html"

-- replace this with the tell block to get the Excel spreadsheet cells into a list

set colB_data to {"A", "B", "C", "D", "E", "F", "G", "H", "I"}

tell application "TextEdit"

set myHTML to readfileorig_html

repeat with index from 1 to count of colB_data

set findStr to ("B_" & index) as text

set replaceStr to (item index of colB_data) as text

set srcString to (current application'sNSString's stringWithString:myHTML)

-- updated

set loc to (srcString's rangeOfString:findStr)

set myHTML to (srcString's stringByReplacingCharactersInRange:loc withString:replaceStr) as text

end repeat

end tell

-- write changes out

set replaced to open for accessreplaced_html with write permission

set eofreplacedto 0

write (myHTML as text) toreplaced

close accessreplaced

return

se

The original specification that you posted, and that I have provided as an updated solution earlier this morning, assumes a sequential, unique run of list item replacements from B_1 to B_nn.

Now, you share that it is not entirely sequential, and that there may occur multiple, out-of-sequence instances of B_nn strings. This eliminates a sequential repeat loop based on indices, and suggests that the application needs to harvest the B_nn HTML text instances into a list, and use their index in that list to select their corresponding index (and replacement string) from the Excel list.

Is this a correct assessment?

If so, then this has just now become more complicated, and time consuming to code and test.

I do not share my personal contact information within the Apple Support Community, partially because it conflicts with Apple's terms of use, and because Google indexes these posts. I do not want my email harvested by spammers.

Hello

You might try something like the following script.

Excel part is currently commented out and values of xx and yy are given manually, for I don't have Excel at hand. Cell name Bn in xx is converted to B_n before used as search string.

set infile to (choose file of type {"public.html"})'s POSIX path set outfile to (choose file name default name "out.html")'s POSIX path (* tell application "Microsoft Excel" tell active sheet's range "B1:B14" set {xx, yy} to every cell's {name, value} end tell end tell *) set xx to {"B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12", "B13", "B14"} set yy to {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N"} set tsv to "" repeat with i from 1 to count xx set tsv to tsv & xx's item i & tab & yy's item i & linefeed end repeat set args to "" repeat with a in {infile, tsv} set args to args & a's quoted form & space end repeat do shell script "/usr/bin/ruby -w <<'EOF' - " & args & " > " & outfile's quoted form & " # # ARGV[0] : input html file # ARGV[1] : TSV text of search and replace strings separated by TAB and terminated by LF # # * Search string is preprocessed so that leading non-numeric part and succeeding numeric part # are separated by U+005F LOW LINE. E.g., AB10 is preprocessed as AB_10. # t = '' File.open(ARGV.shift, 'r') do |f| t = f.read end ARGV.shift.scan(/(.*?)\\t(.*)/).each do |x, y| x.sub!(/^(\\D+?)_*(\\d+)/, '\\1_\\2') # AB10 => AB_10 (AB_10 => AB_10) t.gsub!(/(\\W)#{x}(\\D)/, '\\1%s\\2' % y) end puts t EOF"

Briefly tested under OS X 10.6.8.

Good luck,

H

Minor revision to process UTF-8 html file in Ruby 2.x correctly. Added encoding option r:utf-8 when opening file.

set infile to (choose file of type {"public.html"})'s POSIX path set outfile to (choose file name default name "out.html")'s POSIX path (* tell application "Microsoft Excel" tell active sheet's range "B1:B14" set {xx, yy} to every cell's {name, value} end tell end tell *) set xx to {"B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12", "B13", "B14"} set yy to {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N"} set tsv to "" repeat with i from 1 to count xx set tsv to tsv & xx's item i & tab & yy's item i & linefeed end repeat set args to "" repeat with a in {infile, tsv} set args to args & a's quoted form & space end repeat do shell script "/usr/bin/ruby -w <<'EOF' - " & args & " > " & outfile's quoted form & " # # ARGV[0] : input html file # ARGV[1] : TSV text of search and replace strings separated by TAB and terminated by LF # # * Search string is preprocessed so that leading non-numeric part and succeeding numeric part # are separated by U+005F LOW LINE. E.g., AB10 is preprocessed as AB_10. # t = '' File.open(ARGV.shift, 'r:utf-8') do |f| t = f.read end ARGV.shift.scan(/(.*?)\\t(.*)/).each do |x, y| x.sub!(/^(\\D+?)_*(\\d+)/, '\\1_\\2') # AB10 => AB_10 (AB_10 => AB_10) t.gsub!(/(\\W)#{x}(\\D)/, '\\1%s\\2' % y) end puts t EOF"

Hello

I'm not sure but I'd guess something like the following code will get the name and value of every cell in the specified range.

tell application "Microsoft Excel" tell document 1's worksheet 1's range "B1:B38" set {xx, yy} to every cell's {name, value} end tell end tell

Or -

tell application "Microsoft Excel" tell document 1's worksheet 1's range "B1:B38" tell every cell set xx to name set yy to value end tell end tell end tell

* Both will set xx to list of name of cells and yy to list of value of cells in range "B1:B38", if I'm not mistaken.

Regards,

H