Make folder heirarchy from excel data

Hello helpful people,

I have been trying to piece together some previous examples but have thusfar been unsuccessful.

What I need: to create folder structure based on excel data.

The Data :

Col A: EVENT_ID (15, 20038, etc.)
Col B: EVENT_DATE (2010_01_23, 2010_02_29, etc)
Col C: CATEGORY (type a OR type b)
Col D: PERSON_A LAST_NAME (Rogers, Smith, etc.)
Col E: PERSON_A FIRST_NAME (Jimmy, David, etc.)
Col F: INSTANCE_ID (3907, 3908, 3909)
Col G: PERSON_B LAST_NAME (Rogers, Smith, etc.)
Col H: PERSON_B FIRST_NAME (Jimmy, David, etc.)

The folders :

Category A (col c)
TOP FOLDER NAME = EVENT_DATE (col b) + PERSON_A LAST_NAME + " - " + PERSON_B LAST_NAME
SUB FOLDER NAME = PERSON_A FIRST_NAME + PERSON_A LAST_NAME + " - " + PERSON_B FIRST_NAME + PERSON_B LAST_NAME
SUB FOLDER NAME = PERSON_A FIRST_NAME + PERSON_A LAST_NAME + " - " + PERSON_B FIRST_NAME + PERSON_B LAST_NAME
SUB FOLDER NAME = PERSON_A FIRST_NAME + PERSON_A LAST_NAME + " - " + PERSON_B FIRST_NAME + PERSON_B LAST_NAME

Category B (col c)
(SAME AS ABOVE)

The Catch:

  • each row of data contains multiple instances (col F) within the same EVENT_DATE (or EVENT_ID)
  • when making the TOP FOLDER NAME the values from PERSON_A and PERSON_B should be the row within that group with the greatest INSTANCE_ID
  • e.g. 3 rows of data
  1. 018 2011_01_20 catA Smith Roger 1000 Jones David
  2. 018 2011_01_20 catA Wilson Frank 1001 Smalling Richard
  3. 018 2011_01_20 catA Johnson Greg 1002 Scott Michael

Thank you for whatever insight you are able to provide.

  • Matt

what if i were to make this more simple?

two columns:
column a: topfolder
column b: subfolder

example rows:
COL A COL B
2010_12_14 - Smith vs Jones | Bob Smith vs Richard Jones
2010_12_14 - Apple vs Orange | Red Apple vs Orange Orange
2010_12_19 - Geek vs Nerd | Roger Geek vs Tim Nerd

i would like the script to create the top folder from column a and then place a folder with the column b value inside. if the topfolder exists it should make a folder with column b value.

i have also tried concat’ing the values to “2010_12_14 - Smith vs Jones:Bob Smith vs Richard Jones” and saving out to a text file. i have a create folders from text file script but I seem to have screwed up the looping on this.

Thank you for your help.

sidenote - i am willing to financially compensate for your time and effort.

Hi,

simple solution with two columns


set baseFolder to POSIX path of (choose folder)
tell application "Microsoft Excel"
	set theData to value of used range of active sheet
end tell
repeat with anItem in theData
	set {parentFolder, subFolder} to items 1 thru 2 of anItem
	do shell script "/bin/mkdir -p " & quoted form of (baseFolder & parentFolder & "/" & subFolder)
end repeat

stefan,

you are the man bows head. please step forward to collect your prize.

the script worked perfectly. it looks so simple! much more simple than i was expecting.

matt

Oh my god, I just used this and it’s AMAZING!!! I ran it 3 times just for the hell of it! I cna’t believe how quickly and easily it works, saved me so much time.

This is the power AppleScript is designed for :wink:

Hello all,

New to the forum, and have no experience in AppleScript.

I have been looking for an AppleScript to do something along these lines, but can not find it anywhere.

If an Excel spreadsheet has a variable number of columns (lets say 12 to 15 columns) of data, and not all of the cells had data in them, how could this be accomplished? I would have maybe a hundred rows of data or more.

My goal is to create a directory tree structure.

EXAMPLE:
Directory Folder Structure | Vehicle | Car | Chevy | Camaro | Coupe | 2-door | Black | V8 Engine | Leather
Directory Folder Structure | Vehicle | Car | Chevy | Cobalt | Coupe | 2-door | Red | i4 Engine | Leather
Directory Folder Structure | Vehicle | Boat | ChrisCraft | Streamer | Power | Catamaran | White | Fiberglass | Warranty
Directory Folder Structure | Food | Vegatable | Corn
Directory Folder Structure | Food | Vegatable | Potato

If anyone could help, I would be so grateful. The script that StefanK created seemed to be the closest thing, but it doesn’t work on my sample excel data set.

Thanks

I’m not sure about the folder structure, maybe you mean this


set baseFolder to POSIX path of (choose folder)
tell application "Microsoft Excel"
	set theData to columns of used range of active sheet
end tell

set {TID, text item delimiters} to {text item delimiters, "/"}
repeat with aColumn in theData
	tell application "Microsoft Excel" to set valueList to value of aColumn
	set nameList to {}
	repeat with aValue in valueList
		if contents of item 1 of aValue is not "" then
			set end of nameList to item 1 of aValue
		end if
	end repeat
	set subFolders to nameList as text
	
	do shell script "/bin/mkdir -p " & quoted form of (baseFolder & subFolders)
end repeat
set text item delimiters to TID

Hi StefanK,

Thank you so much for trying to help. Unfortunately, when I run it, it does not generate the structure I need. I am sure it is a failure of my ability to explain. I created all the source and example files in this dropbox link:

https://www.dropbox.com/sh/fuk35dfmqriu6ci/AADA68qJyLH3DCc89e22_-kja?dl=0

Hopefully you can access those files and see exactly what I am trying to do with the sample data set.

This should do it


set baseFolder to POSIX path of (choose folder)
tell application "Microsoft Excel"
	set theData to value of rows of used range of active sheet
end tell

set {TID, text item delimiters} to {text item delimiters, "/"}
repeat with aRow in theData
	set valueList to item 1 of aRow
	set nameList to {}
	repeat with aValue in valueList
		if contents of aValue is not "" then
			set end of nameList to contents of aValue
		end if
	end repeat
	set subFolders to nameList as text
	do shell script "/bin/mkdir -p " & quoted form of (baseFolder & subFolders)
end repeat
set text item delimiters to TID


Hi Stefan,

This is fantastic! It works EXACTLY as I wanted, Great job Stefan!

I have a few questions though:

  1. Is it possible to create an AppleScript that provides the same functionality, but the data source would be a “text-only” file instead of a Microsoft Excel file? I do NOT have an immediate need for it, but wanted to know if it were possible.

  2. I also need the exact same functionality for users on PC computers: I believe the tool for that job is VB Script. Do you have experience in VB Script?

Thank you,
David

Of course, it could be a tab delimited or CSV file

not at all, I’m afraid

That is good news that it can be accomplished. If you know a VB Script virtuoso, such as you are on AppleScript, please do forward their contact information!

By the way, I was fortunate enough to visit your beautiful country with my children in 2009 - we stayed in Zermatt for 4 days: loved Switzerland.

Thanks again.