Rename folders based on specific terms found in EXCEL document

Scripting Forums AppleScript | Mac OS X
#1

I have an excel file with one column that contains specific text.

EXAMPLE

Column A

  1. air
  2. gas
  3. water

Also, i have a directory containing folders that have names as following:

  1. air 001
  2. gas 001
  3. water 001
  4. heat 001
  5. cold 001

PROBLEM

I would like to be able to run a script which has following statement

  • If term “AIR”, “GAS”, “WATER” found in excel spreadsheet

  • Add text to the end of the folder name " SYSTEM"

END RESULT should look like this:

  1. air 001 SYSTEM
  2. gas 001 SYSTEM
  3. water 001 SYSTEM
  4. heat 001
  5. cold 001

Note: Since “HEAT” and “COLD” was not a part of that excel data the folders are unchanged.

Thank you

Model: Mac Pro
Browser: Safari 8536.30.1
Operating System: Mac OS X (10.8)

#2

As I already wrote, Merdosoft products aren’t allowed to enter my home (and my machines) so I wrote a script using Numbers.


tell application "Numbers" to tell document "maybe.numbers" to tell sheet 1 to tell table 1
	set theValues to value of every cell of column 1 whose value is not 0.0
end tell

set sourceFolder to (path to desktop as text) & "fake Æ’"
tell application "System Events" to tell folder sourceFolder
	set folderNames to name of every folder
	repeat with aFolder in folderNames
		if (word 1 of aFolder is in theValues) and last character of aFolder is in characters of "0123456789" then
			set name of folder aFolder to aFolder & " SYSTEM"
		end if
	end repeat
end tell

You may try to replace the three lines dedicated to Numbers by :


tell application "Excel"
	set theValues to value of every cell of column 1 whose value is not 0.0
end tell

or by


tell application "Excel"
	set theValues to value of every cell of column 1
end tell

KOENIG Yvan (VALLAURIS, France) vendredi 16 août 2013 16:05:40

#3

Thank you Yvan for your help.

I created a Numbers document that contains search terms in column one and saved it as “maybe”
but the following error is popping up whenever the script is run
"Can’t get document “maybe.numbers”.

Can you point me in the right direction?

What am I doing wrong.

Much appreciated.

#4

On my machine I ALWAYS use filenames with the dedicated extension.

I guess that you aren’t working this way.

To be sure, open your document entitled maybe then run this short script (no less, no more)


tell application "Numbers"
set dName to name of document 1
end
display dialog "The document is named :" & dName

This way you will know for sure what is your document’s name.

KOENIG Yvan (VALLAURIS, France) lundi 19 août 2013 17:49:30

#5

It’s really boring when an asker describe a problem one way and try to apply the answer to datas wich don’t match the rule.

In your original question, all the folder names were ending with a group of three digits so I understood that your original folder names were matching this structure.

Now, you sent me screenshots showing
(1) that your folder names aren’t ending with digits

(2) the script’s log report is not matching the folder used to take the passed screenshot.

Would be polite to take care of what you do.

Here is an edited script matching your new rule :


tell application "Numbers" to tell document "maybe.numbers" to tell sheet 1 to tell table 1
	set theValues to value of every cell of column 1 whose value is not 0.0
end tell

set sourceFolder to (path to desktop as text) & "test"
set suffix to " SYSTEM"
tell application "System Events" to tell folder sourceFolder
	set folderNames to name of every folder
	repeat with aFolder in folderNames
		if (word 1 of aFolder is in theValues) and aFolder does not end with suffix then
			set name of folder aFolder to aFolder & suffix
		end if
	end repeat
end tell

I choose to define a true document name (not document 1) to have some chance to work starting from correct datas.

KOENIG Yvan (VALLAURIS, France) lundi 19 août 2013 19:06:56