set templateFile to read (choose file with prompt "Please select your template file") -- Choose your template html file
set createPath to (choose folder with prompt "Where should I create the product pages?") as Unicode text
set replaceSet to {"{{H1Data}}", "{{ProductSKU}}", "{{ProductName}}", "{{ProductSize}}", "{{ProductDescription}}", "{{ProductRetailPrice}}", "{{ProductYourPrice}}", "{{ProductPhoto}}", "{{ProductPageTitle}}"}
set {master_data, dummy} to {{}, {}}
tell application "Microsoft Excel"
repeat with r_row from 1 to 300 --Cycles through 300 rows
repeat with c_col from 1 to 10 --Gets the data from columns 1 thru 10
copy value of cell c_col of row r_row of active sheet to the end of dummy
end repeat
set end of master_data to dummy
set dummy to {}
end repeat
end tell
repeat with dataSet in master_data
set modStr to templateFile
repeat with i from 1 to 9
set modStr to srchRep(modStr, item i of replaceSet, item i of dataSet)
end repeat
set newFilePath to (createPath & item 10 of dataSet & ".html")
set fileRef to (open for access file newFilePath with write permission)
write modStr to fileRef starting at eof
close access fileRef
end repeat
on srchRep(theStr, fndStr, repStr)
set atid to AppleScript's text item delimiters
set AppleScript's text item delimiters to {fndStr}
set tempList to text items of theStr
set AppleScript's text item delimiters to {repStr}
set theStr to tempList as string
set AppleScript's text item delimiters to atid
return theStr
end srchRep
I also have one more shell version coming… this one using a sed command file rather than mulitple piped sed statements.
If you run it on that same “record” does it break again? Does it break with both sed versions? Can you provide the values for the cells on the record that breaks?
I’ve got a system working, however there are two cells which contain price that do not seem to be getting replaced in my template file - {{ProductRetailPrice}} and {{ProductYourPrice}}
The script is:
set templateFile to read (choose file with prompt "Please select your template file") -- Choose your template html file
set createPath to (choose folder with prompt "Where should I create the product pages?") as Unicode text
set replaceSet to {"{{ProductSKU}}", "{{ProductName}}", "{{ProductPhoto}}", "{{ProductRetailPrice}}", "{{ProductYourPrice}}", "{{ProductPageName}}", "{{ProductPageTitle}}", "{{H1Data}}", "{{ProductDescription}}"}
set {master_data, dummy} to {{}, {}}
tell application "Microsoft Excel"
repeat with r_row from 1 to 300 --Cycles through 300 rows
repeat with c_col from 1 to 9 --Gets the data from columns 1 thru 9
copy value of cell c_col of row r_row of active sheet to the end of dummy
end repeat
set end of master_data to dummy
set dummy to {}
end repeat
end tell
repeat with dataSet in master_data
set modStr to templateFile
repeat with i from 1 to 8
set modStr to srchRep(modStr, item i of replaceSet, item i of dataSet)
end repeat
set newFilePath to (createPath & item 6 of dataSet & ".html")
set fileRef to (open for access file newFilePath with write permission)
write modStr to fileRef starting at eof
close access fileRef
end repeat
on srchRep(theStr, fndStr, repStr)
set atid to AppleScript's text item delimiters
set AppleScript's text item delimiters to {fndStr}
set tempList to text items of theStr
set AppleScript's text item delimiters to {repStr}
set theStr to tempList as string
set AppleScript's text item delimiters to atid
return theStr
end srchRep
<!-- InstanceBeginEditable name="HBX_code" --><!--COPYRIGHT 1997-2005 WEBSIDESTORY,INC. ALL RIGHTS RESERVED. U.S.PATENT No. 6,393,479B1. MORE INFO:http://websidestory.com/privacy-->
James, yes it produces this error on the same row over and over. Here’s the row data. Each line is a cell. The bottom one is the ProductDescription that seems to break it.
[code]ACN001
ClearSkin-A Gel
clearskinacnegel
30.95
21.95
clearskin-clear-skin-complexion-blemish-free
acne_gel.shtml
ClearSkin-A Gel for healthy, clear skin and trouble free complexion
Promotes healthy clear skin and encourages a smooth and trouble free complexion.
ClearSkin-A Gel - a 100% natural, proven and safe herbal gel that promotes healthy clear skin and encourages a smooth and trouble free complexion. Assists the skin’s natural ability to heal and remain blemish free.[/code]
Okay for the sed version… let me guess on the record it chokes on there is a / characeter somewhere in the description? To get around that you would need to change the text delimiter in the script… so in the sed version replace all occurence of / with some character you would never find in the one of the cells… maybe a ~ or ^
In the AppleScript version make the following change
set modStr to srchRep(modStr, item i of replaceSet as string, item i of dataSet as string)
when you have a cell coming in from excel it’s being evaulated as a double not a string which throws off AppleScript text item delimiter parsing.
Hi, don’t mind if I share your script.
I am working on something similar to this but the different is I want the final data in XML not HTML. Also I don’t have a template.
The first part of the script works perfectly. I am able to get data/value from cells that I’ve defined. Secondly, I would need to populate them into a XML file with static tags.
For example, for every cell in first cell in every roll will be tagged as itemName, 2nd cell of every roll will be tagged as price…etc
it will look something like this:
A1 = itemName
B1= price
C1 = unit
…etc