Script Needed | Create Hierarchy of Folders in Finder From Excel

Hi All,

About a year ago, I posted this same request and was shocked and pleased to have someone create the basic script (seen below) for me. It has saved me so much time, I can’t begin to thank the person who wrote it. I’m hoping to take the script one step further. Depending on the complexity, I’m willing to pay for an error-free script that does the job.

Resources
Script (zipped): http://www.kylepetrozza.com/CreateShotFolders_scpt.zip
Shot List (Zipped Excel File): http://www.kylepetrozza.com/Shot_List_xls.zip
Screen Shot of Successful Hierarchy: http://www.kylepetrozza.com/Screen_Shot_2014_10_09_at_12_59_49_PM.jpg
Current Script:
[b]set screenBounds to {0, 44, 1920, 1640} – screen rect, change according to your needs.
set aName to “Shot_” – some folder name to rename each created folder
set targetFolder to choose folder with prompt “Select the directory to create folders”
display dialog “How many folders you want to create.” default answer “10”
set n to (text returned of the result) as integer

—** create folder **—
repeat with i from 1 to n
if i < 10 then
set x to aName & “0” & i
else
set x to aName & i
end if
tell application “Finder” to make new folder at targetFolder with properties {name:x}
end repeat[/b]

The script takes information from data in Excel (columns) and creates folders named for that data.

Ideally, I’d love to have a script that does what this basic script does, but for all relevant columns and that also arranges the folders in the hierarchy as seen in the screen shot. If this is possible, the time and stress savings would be enormous.

Self correcting / error checking is important as I’m not the one creating the excel files and errors sometimes crop up (blank cells for instance).

To my novice eyes, this seems like a rather large undertaking, but maybe for seasoned programmers/scripters it isn’t.

Please get in touch if you think you could take this on or know someone who would.

Thanks!

-Kyle

Model: MBP Late 2011
AppleScript: 2.2.4
Browser: Chrome
Operating System: Mac OS X (10.8)

I might be able to give you general directions but creating a complete solution like the one you mention takes time that I don’t have, sadly.

But are you sure the script you posted above is the one you use? There is absolutely no action in there that takes any data from Excel or even communicate with it… looks like a standalone script that only tells the Finder to create folders.

And this line:

set screenBounds to {0, 44, 1920, 1640} -- screen rect, change according to your needs.

is totally useless in this context, and is not used anywhere in the script.

Thanks for your reply, leon.

You’re totally correct, I uploaded the wrong script!

I decided that creating this script would take someone a bit of time that I myself don’t have either and decided to hire it out via eLance.

-Kyle

Hey Kyle,

You can edit your previous post. Please provide the correct url for the script, so someone can play with the problem if they’re so inclined.

Thanks.

Thanks for reminding me!

Here is the script:

[b]tell application “Finder”

set _excel to choose file
set _folder to choose folder

end tell

tell application “Microsoft Excel”
open _excel
set _surname to value of range “C4:C40” as list
close active workbook
end tell

tell application “Finder”
set i to 1
repeat with _foldername in _surname
make new folder at folder _folder with properties {name:{(item i of _surname as text)}}
set i to i + 1
end repeat
end tell[/b]

Hi,

this is a version without using the Finder

set _excel to choose file
set _folder to choose folder

tell application "Microsoft Excel"
	open _excel
	set _surname to value of range "C4:C40"
	close active workbook
end tell

set {TID, text item delimiters} to {text item delimiters, "','"}
set folderNames to _surname as text
set text item delimiters to TID

do shell script "/bin/mkdir -p " & quoted form of (POSIX path of _folder) & "{'" & folderNames & "'}"