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
018 2011_01_20 catA Smith Roger 1000 Jones David
018 2011_01_20 catA Wilson Frank 1001 Smalling Richard
018 2011_01_20 catA Johnson Greg 1002 Scott Michael
Thank you for whatever insight you are able to provide.
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.
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
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.
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.
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.
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
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:
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
This is fantastic! It works EXACTLY as I wanted, Great job Stefan!
I have a few questions though:
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.
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?
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.
