I am trying to write a script to read cell values from Excel work sheet and write them to another text file. Some of the cells contains both Group name and product name with multiple spaces. (Multiple spaces are delimiters for Group & Product name). In order to split the Group & Product name, i have to replace those multiple spaces with the “return character”. Since i am new to Applescript, i am not sure how to do this. I have only managed to script until this stage. Not sure, how to proceed further. Your help will be appreciated to complete the script.
Cell A1 contains “Premium Group Branded Razor blades”
Cell A2 contains “Cheap Group ABC Razor blades”
tell application "Microsoft Excel"
repeat with i from 1 to 2
set myText to get string value of cell ("A" & i)
set w to words of myText
set tid to AppleScript's text item delimiters
set AppleScript's text item delimiters to space
set myText to w as string
set AppleScript's text item delimiters to tid
end repeat
end tell
Hi JaiM,
The script below should work as long as the word “Group” is always at the end of the Group name.
Hope this helps!
set ptd to path to desktop
set Posix_ptd to quoted form of POSIX path of ptd
set text_file_name to quoted form of "new_text_file.txt" --> change this to suit your naming
set myText_list to {}
tell application "Microsoft Excel"
repeat with i from 1 to 2
set cell_value to get string value of cell ("A" & i)
copy cell_value to end of myText_list
end repeat
end tell
set thelist to {}
repeat with myText in myText_list
set myoffset to offset of " Group " in myText --> assuming Group is always the last word of the first entry
set The_Group to text 1 thru (myoffset + 5) in myText
copy The_Group & return to end of thelist
set myNewText to text (myoffset + 6) thru end of myText
set myNewOffset to offset of (word 1 of myNewText) in myNewText
set The_Product to text myNewOffset thru (count of myNewText) in myNewText
copy The_Product & return & return to end of thelist
end repeat
do shell script "echo " & thelist & " " & "> " & Posix_ptd & text_file_name --> use >> to amend and > to create new
Thanks for the quick reply with the script. Apologies, i realized i didn’t explain the scenario very clearly. Cell values for Group does not always ends with the word Group. It could be something else:
Cell A3 contains “Platinum Designer Carpets Kashmir Carpet Self Design”
Cell A4 contains “Candle Holders Decorated 3” Candle Holders"
Also, the multiple spaces between the Group and Product are not consistent at all. It varies from cell to cell.
Would you be able to advise based on the above scenario.
Hi JaiM,
I’ve changed the code slightly, it now relies on there being more than 1 space between the two items, the only draw back I can see is if an entry has two spaces in the Group name i.e. “Platinum Designer Carpets” it would pick up “Platinum” as being the first entry. Please test it out and let me know how you get on!
Thanks,
Nik
set ptd to path to desktop
set Posix_ptd to quoted form of POSIX path of ptd
set text_file_name to quoted form of "new_text_file.txt" --> change this to suit your naming
set myText_list to {}
tell application "Microsoft Excel"
repeat with i from 1 to 2
set cell_value to get string value of cell ("A" & i)
copy cell_value to end of myText_list
end repeat
end tell
set thelist to {}
repeat with myText in myText_list
set myoffset to offset of " " in myText --> assuming that two spaces or more indicates the delimeter
set The_Group to text 1 thru (myoffset - 1) in myText
copy The_Group & return to end of thelist
set myNewText to text myoffset thru end of myText
set myNewOffset to offset of (word 1 of myNewText) in myNewText
set The_Product to text myNewOffset thru (count of myNewText) in myNewText
copy The_Product & return & return to end of thelist
end repeat
do shell script "echo " & thelist & space & "> " & Posix_ptd & text_file_name --> use >> to amend and > to create new
set theText to my decompose("Platinum Designer Carpets Kashmir Carpet Self Design")
on decompose(txt)
if txt contains (space & space) then
set txt to my remplace(txt, space & space, return)
repeat while txt contains (space & return)
set txt to my remplace(txt, space & return, return)
end repeat
repeat while txt contains (return & return)
set txt to my remplace(txt, return & return, return)
end repeat
end if
return txt
end decompose
on remplace(t, d1, d2)
local l
set AppleScript's text item delimiters to d1
set l to text items of t
set AppleScript's text item delimiters to d2
set t to l as text
set AppleScript's text item delimiters to ""
return t
end remplace
Yvan KOENIG (from FRANCE jeudi 7 mai 2009 16:33:38)
Thanks a million for the suggestion. With the help of your assistance, i have managed to accomplish something. (I am half way thru my first attempt in creating applescript :-))
See my code below:
tell application "Microsoft Excel"
activate
-- Check if workbook is available
if not (exists active workbook) then
set errNum to 1
displayErrMsg(errNum) of me
else
-- Get the path
tell application "Finder"
activate
set barcodeFolder to (choose folder with prompt "Select the Folder which contains Barcode") as string
set barcodeFolder1 to (POSIX path of barcodeFolder) as text
end tell
activate
--get name of workbook
set nameWorkBk to get name of active workbook
set AppleScript's text item delimiters to {"."}
set nameWorkBk to first text item of nameWorkBk as string
set theFilePath to (path to desktop as string) & nameWorkBk & ".xml" as string
set theFileReference to open for access theFilePath with write permission
set eof theFileReference to 0
-- gather start and end row of the data
set start_row to text returned of (display dialog ¬
"Enter Starting Row Number of the SKU Data" & return & "Do not include Title Row" with title ¬
"Enter Start Row" with icon stop ¬
default answer ¬
"23" buttons {"Continue."} ¬
default button 1)
set end_row to text returned of (display dialog ¬
"Enter the Last Row Number of the SKU Data" with title ¬
"Enter End Row" with icon stop ¬
default answer ¬
"40" buttons {"Continue."} ¬
default button 1)
--replace ampersands and hyphens
replace (range ("B" & start_row & ":" & "B" & end_row) of worksheet "Sheet1") what "&" replacement ¬
"&" search order by columns look at part
replace (range ("F" & start_row & ":" & "F" & end_row) of worksheet "Sheet1") what "-" replacement ¬
"" search order by columns look at part
--Check if Item numbers are entered
if string value of cell ("A" & start_row) = "" then
set errNum to 2
displayErrMsg(errNum) of me
end if
--Write info to the file
write "<?xml version=\"1.0\" encoding=\"utf-8\"?>
<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 20001102//EN\" \"http://www.w3.org/TR/2000/CR-SVG-20001102/DTD/svg-20001102.dtd\" [
<!ENTITY ns_graphs \"http://ns.adobe.com/Graphs/1.0/\">
<!ENTITY ns_vars \"http://ns.adobe.com/Variables/1.0/\">
<!ENTITY ns_imrep \"http://ns.adobe.com/ImageReplacement/1.0/\">
<!ENTITY ns_custom \"http://ns.adobe.com/GenericCustomNamespace/1.0/\">
<!ENTITY ns_flows \"http://ns.adobe.com/Flows/1.0/\">
<!ENTITY ns_extend \"http://ns.adobe.com/Extensibility/1.0/\">
]>
<svg>
<variableSets xmlns=\"&ns_vars;\">
<variableSet locked=\"none\" varSetName=\"binding1\">
<variables>
<variable varName=\"Collection\" trait=\"textcontent\" category=\"&ns_flows;\"></variable>
<variable varName=\"Product\" trait=\"textcontent\" category=\"&ns_flows;\"></variable>
</variables>
<v:sampleDataSets xmlns:v=\"http://ns.adobe.com/Variables/1.0/\" xmlns=\"http://ns.adobe.com/GenericCustomNamespace/1.0/\">" & return to theFileReference as «class utf8»
repeat with i from start_row to end_row
--Begin Dataset tag
if string value of cell ("A" & i) ≠"" then
set DataSet to get value of cell ("A" & i)
write "<v:sampleDataSet dataSetName=\"" & DataSet & "\">" & return to theFileReference as «class utf8»
--Get & Set Product Name
set theParagraphs to paragraphs of (get string value of cell ("B" & i))
if (count of items of theParagraphs) > 1 then
set collections to item 1 of theParagraphs
set ProductName to item 2 of theParagraphs
write "<Collection>" & return & "<p>" & collections & "</p>" & return & "</Collection>" & return to theFileReference as «class utf8»
write "<Product>" & return & "<p>" & ProductName & "</p>" & return & "</Product>" & return to theFileReference as «class utf8»
else
set theText to my decompose(get string value of cell ("B" & i))
set collections1 to 1st paragraph of theText
set ProductName1 to 2nd paragraph of theText
write "<Collection>" & return & "<p>" & collections1 & "</p>" & return & "</Collection>" & return to theFileReference as «class utf8»
write "<Product>" & return & "<p>" & ProductName1 & "</p>" & return & "</Product>" & return to theFileReference as «class utf8»
end if
--Get & Set Barcode as text
set BarCode to get string value of cell ("D" & i)
if BarCode ≠"" and BarCode ≠"NA" then
set AppleScript's text item delimiters to {"-"}
set BarCodeList to get every text item of BarCode as list
set AppleScript's text item delimiters to {""}
set BarCodeText to get text items of BarCodeList as string
write "<BarcodeText>" & return & "<p>" & BarCodeText & "</p>" & return & "</BarcodeText>" & return to theFileReference as «class utf8»
end if
--End Tag Sample Data Set
write "</v:sampleDataSet>" & return to the theFileReference as «class utf8»
end if
end repeat
write "</v:sampleDataSets>
</variableSet>
</variableSets>
</svg>" to theFileReference as «class utf8»
close access theFileReference
close active workbook without saving
end if
end tell
on displayErrMsg(i)
tell application "Finder"
activate
if i is 1 then
display dialog "Open Excel Workbook -" & return & "\"Form\"" with title "Open Excel Workbook" with icon stop buttons {"Ok"}
else if i is 2 then
display dialog "Fill column \"A\" with Item Number" with title "Fill Columns" with icon stop buttons {"Ok"}
end if
end tell
end displayErrMsg
on decompose(txt)
if txt contains (space & space) then
set txt to my remplace(txt, space & space, return)
repeat while txt contains (space & return)
set txt to my remplace(txt, space & return, return)
end repeat
repeat while txt contains (return & return)
set txt to my remplace(txt, return & return, return)
end repeat
end if
return txt
end decompose
on remplace(t, d1, d2)
local l
set AppleScript's text item delimiters to d1
set l to text items of t
set AppleScript's text item delimiters to d2
set t to l as text
set AppleScript's text item delimiters to ""
return t
end remplace