Create folder structure based on Excel data

AppleScript
#1

Hello helpful people,

I have been working on this for most of the day but haven’t found an example to fit my needs. (I’m a relative newbie to AppleScript.)

What I need: to create folders and subfolders based on excel data, but the spreadsheets are a little messy.

Code and spreadsheet screenshots
Spreadsheet

For each number in column A, I need a folder containing subfolders for each number in column D.

Here’s what I have:

set numList to getCellList("A", 3, 310)
set myDoc to choose file with prompt "Choose Spreadsheet"
set myFolder to choose folder with prompt "Choose Folder"
repeat with i in numList
	tell application "Microsoft Excel" to tell document (myDoc as string) to tell sheet 1 to set cellValue to (value of cell i)
	if cellValue is not {"", "outfit", "outfits"} then tell application "Finder" to make new folder at myFolder with properties {name:cellValue}
end repeat


on getCellList(aLetter, startNumber, endNumber)
	set myList to {}
	repeat with i from startNumber to endNumber
		set end of myList to (aLetter & i) as string
	end repeat
	return myList
end getCellList

This creates the 13 top-level folders I need as well as 295 untitled folders, which I don’t want. I don’t know where to begin with creating the subfolders.

Ideally, a subfolder would be named the number from column D, but only created if there is a number in column B of the same row.

An ideal example based on cell A3: a new folder named “1” is created, containing subfolders “1392440003” and “1381850004”, but no subfolder named “1381500004” because there is no number in B20.

Icing on the cake would be the text from column F of the corresponding row also being in the subfolder name. For example “1392440003 - Insulated Vest - Women’s”.

#2

How about something like this?

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
use framework "Foundation"

-- classes, constants, and enums used
property NSString : a reference to current application's NSString
property NSFileManager : a reference to current application's NSFileManager

set myDoc to choose file with prompt "Choose spreadsheet"
set myFolder to choose folder with prompt "Choose folder"

tell application "Microsoft Excel"
	
	set wb to (open workbook workbook file name (myDoc as text) with read only)
	
	tell sheet 1 of workbook (myDoc as text)
		
		-- to find the last used row, we can't rely on used range
		-- so start waaaaaaay at the bottom and go up to 
		--   the end of the longest column in the sheet
		set lastRow to first row index of cell 1 of (get end range "F65536" direction toward the top)
		-- adjust to skip the totals at the bottom
		set lastRow to lastRow - 2
		
		-- get values as a nested list of strings
		-- this saves us from having to hit Excel with a tell statement
		--   for every cell
		-- A3 from the sample spreadsheet provided by the OP; adjust as needed
		set dataVals to string value of range ("A3:F" & lastRow)
		
	end tell
	
	tell wb to close
	
end tell

set fileMgr to NSFileManager's defaultManager()
set basePath to NSString's stringWithString:(POSIX path of myFolder)

set firstLevel to ""
repeat with oneRow in dataVals
	
	if (oneRow as text) ≠ "" then
		-- item 1 is column A
		-- item 2 is column B
		-- etc...
		tell oneRow
			-- if column A is blank, re-use the last value we had for it
			if item 1 ≠ "" then
				set firstLevel to item 1
			else
				set item 1 to firstLevel
			end if
			
			if item 2 ≠ "" then
				set hierarchyPath to (NSString's pathWithComponents:{item 1, item 4 & " - " & item 6})
				set fullPath to (basePath's stringByAppendingPathComponent:hierarchyPath)
				-- we use createDirectoryAtPath:withIntermediateDirectories:attributes:error:
				--   to create the entire hierarchy in one fell swoop
				set fmResult to (fileMgr's createDirectoryAtPath:fullPath withIntermediateDirectories:true attributes:(missing value) |error|:(missing value))
			end if
		end tell
	end if
	
end repeat

And here’s a sample of what it looks like after running:

42
Screen Shot 2018-12-05 at 20.02.42.png822×676 121 KB

3 Likes
#3

Wow Patrick, this is brilliant! Thank you so much.

Is there some way I can thank you? Venmo, PayPal, Amazon gift card…let me know.

I have a couple questions if you’ve got time.

  • In the first set of folders, how would I modify the script to add “Outfit” to the beginning of each folder name? For example “Outfit 1”, “Outfit 2”, etc.

  • There is an empty folder being created in the first set after the numbered folders. It’s named “outfit”. How should I modify to prevent the creation of that folder? What I’ve tried hasn’t been successful.

Thank you!

#4

Totally not necessary. It was actually kind of fun to figure this out.

Replace this line:

set hierarchyPath to (NSString's pathWithComponents:{item 1, item 4 & " - " & item 6})

with this:

set hierarchyPath to (NSString's pathWithComponents:{"Outfit " & item 1, item 4 & " - " & item 6})

Hmm, I’m not seeing that using the spreadsheet you linked above. Are you trying it with different data that maybe has a rogue value in the first column?

#5

Thanks again, seriously. This is hugely helpful.

Adding Outfit to the name worked flawlessly.

The superfluous folder is being created when I use this spreadsheet:

https://drive.google.com/open?id=1IiNYSmFEgZnHKBIlTf6W6eIzUlpR9Tv_

You can see the data in A311. I can’t find the syntax to fix it myself. I’m brand new but am learning lots thanks to you!

#6

Ah, I see what’s going on.

set lastRow to first row index of cell 1 of (get end range "F65536" direction toward the top)
-- adjust to skip the totals at the bottom
set lastRow to lastRow - 2

To get the last row, we start at cell F65536 and go up until we hit something. This avoids issues with using used range, which is not reliable at getting the last row.

The sample data you originally posted did not have the data that’s in F313 in this spreadsheet, so going up from F65536 led us to F311, from which we subtracted 2 rows for the totals data to get to the bottom of the actual data we want to loop through. The value in F313 here is screwing us up and our data to loop through is including rows 310 and 311.

Changing the above code to:

set lastRow to first row index of cell 1 of (get end range "A65536" direction toward the top)
-- adjust to skip the totals at the bottom
set lastRow to lastRow - 2

will fix the problem, although if you ever have a sheet with data below A311, you’ll have the same issue.

Alternately, you could keep the original code and adjust the last row by subtracting 4 instead of 2.

Alternately alternately, you could search for the text “Total Shots” (assuming it will be present on the same relative location on every sheet you apply this code to) and count up two rows from there. If “Total Shots” won’t work, you could try some other text from that totals line, as long as it is guaranteed to be present only once in the sheet.

set lastRow to (first row index of (find used range what "Total Shots")) - 2

Substitute that line for the two in the original code.

Hmm, that was clear in my head; hope it’s just as clear written out.

#7

Hi,
i saw this post days ago googling for a way to create folder and subfolder from some Excel sheet.
My needs are a little different and i tried to use this line.

– we use createDirectoryAtPath:withIntermediateDirectories:attributes:error:

but copy and paste doesn’t work.i try to find some information in many applescript guide but it was not successful.
can anyone ,please, help me to understand.

My needs is to create intermediate subfolder at column E (for example) and after put the folder from column F G H inside the folder from column E.
Of course from the same row.

Thanks for your help

Sergio

#8

That’s AppleScriptObjC, and to use it in a script, you need this at the beginning (at least):

use scripting additions
use framework "Foundation"

You can then use it like this:

current application's NSFileManager's defaultManager()'s createDirectoryAtPath:fullPath withIntermediateDirectories:true attributes:(missing value) |error|:(missing value)

or:

tell current application's NSFileManager's defaultManager() to createDirectoryAtPath:fullPath withIntermediateDirectories:true attributes:(missing value) |error|:(missing value)
#9

Hi Shane,
thanks for your reply.
I started with the 2 lines at beginning
At the moment i move to another solution:

set d to POSIX path of (choose folder)
tell application "Microsoft Excel"
	set v to value of used range of active sheet
end tell
repeat with i in v
	do shell script "/bin/mkdir -p " & quoted form of (d & item 1 of i & "/" & item 2 of i)

it works( i don’t copy and paste all the lines ,but this is the meanings.

I will try with your advice and i’ll write you if i find a solution.

Thank you so much

Sergio