Applescript to create folders from excel with constant sub and sub sub folders

Hello,

I am stuck … I need the script to create folders from an excel but also add sub and sub sub folders to each of the folder created in from the excel data. The sub and sub sub folder names are same for all the folders that need to be created

Folder 1
--Sub Folder 1
--Sub Folder 2
 --- Sub Sub Folder 2.1
       ----Sub Sub Sub Folder 2.1.1
       ----Sub Sub Sub Folder 2.1.2
       ----Sub Sub Sub Folder 2.1.3
 --- Sub Sub Folder 2.2
     ---- Sub Sub Sub Folder 2.1.1
--Sub Folder 3
  --- Sub Sub Sub Folder 3.1.1
--Sub Folder 4
--- Sub Sub Sub Folder 4.1.1

This is how far I got to … the script is not creating sub or sub sub folders and error pops up that the folder already exists.

set _excel to choose file

set _folder to choose folder

tell application "Microsoft Excel"

open _excel

set theNames to value of range "A1:c6"

close active workbook

end tell

repeat with aName in theNames

set fullName to item 1 of aName & space & item 2 of aName & space & item 3 of aName

tell application "Finder"

tell application "Finder" to make new folder at _folder with properties {name:fullName}

-- set thisFolder to make new folder at fullName with properties {name:"Copy"}

make new folder at fullName with properties {name:"Copy"}

make new folder at _folder with properties {name:"Layout"}

set subFolder1 to make new folder at loc2 with properties {name:"Links"}

set anotherSubFolder to make new folder at subFolder1 with properties {name:"Final"}

set anotherSubFolder1 to make new folder at subFolder1 with properties {name:"Original"}
set anotherSubFolder4 to make new folder at subFolder1 with properties {name:"Working"}

make new folder at loc2 with properties {name:"Mocks"}
set anotherSubFolder3 to make new folder at subFolder2 with properties {name:"Cover Jpegs"}

make new folder at _folder with properties {name:"Misc"}
make new folder at _folder with properties {name:"PDF"}
make new folder at loc3 with properties {name:"Email for Approval"}

make new folder at loc3 with properties {name:"Press PDF"}

make new folder at loc3 with properties {name:"Proof PDF"}

end tell
end repeat

you have a very confusing question here, that includes lot of extra information that you have to take out for others to understand the problem clearly.

First step would be to divide the problem into sub-problems. Firstly, it looks like you are using excel to get the name for the folders. Test if excel is able to get that,and you can do so by logging the output of array “theNames”. If that’s fine, you can move to “Finder” part.

Create a new script, declare the constant array “theNames” with some values and include only Finder related scripts and test it.

Also, I’m not sure how the folder hierarchy that you mentioned before the script corelates with the Finder folder creation script. It would be better if you would use the real names in that hierarchy instead of Sub Folder 1 and 2.1, etc.

If you do all these, you may notice the problem has become smaller, easy for other users to understand and even the current title mentions “excel” and in fact the problem for which you’re looking a solution doesn’t has anything to do with “excel”.

Thanks

Have you tried getting a POSIX path for the subfolders and doing something like:

try
do shell script "mkdir -p" & space & quoted form of WHATEVER THE POSIX PATH IS
end try
1 Like

In this case I would test to see if the folder exists.


if not (exists item myFolderPath) then make New Folder at _folder with properties {name: folderName}

or

try
set myFolder to alias myFolder
on error
tell application "Finder" to  make New Folder at _folder with properties {name: folderName}

end

I would also suggest putting your folder creation into a hander. Here’s the one I use the most:

--EstablishFolder

set destinationFolder to my EstablishFolder(folderDestination, nameForFolder)

on EstablishFolder(folderPath, myFolderName)
   set saveTID to AppleScript's text item delimiters
   set AppleScript's text item delimiters to {""}
   set folderLocation to {(folderPath as text), myFolderName, ":"} as text
   set AppleScript's text item delimiters to saveTID
   try
      set folderLocation to alias folderLocation
      return folderLocation
   on error errorText number errNum
      repeat 30 times
         
         tell application "Finder"
            try
               make new folder at folderPath
               set NewFolder to the result as alias
               set the name of NewFolder to myFolderName
               return NewFolder as alias
               
            on error errorText number errNum
               if errNum = -48 then
                  return NewFolder as alias
               else
                  set myFolderName to myFolderName & "ƒ"
               end if
            end try
         end tell
      end repeat
   end try
end EstablishFolder

(I use this the most, but if you’re comfortable using script libraries, Shane’s FileManagerLib has better options)

Well, it was a little difficult figuring out exactly what you were going for, as parts of your code don’t make any sense, but here is my understanding of the folder hierarchy I think you are trying to achieve:

Folder
- Copy
- Layout
-- Links
--- Final
--- Original
--- Working
-- Mocks
--- Cover Jpegs
-Misc
-PDFs
-- Email for Approval
-- Press PDF
-- Proof PDF

Given that, try this code:

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

property NSString : a reference to current application's NSString
property NSFileManager : a reference to current application's NSFileManager

set _excel to choose file

set _folder to choose folder
set _folder to NSString's stringWithString:(POSIX path of _folder)

tell application "Microsoft Excel"
    open _excel
    set theNames to string value of range "A1:C3" -- adjust this to the actual range
    close active workbook
end tell


set fileMgr to NSFileManager's defaultManager()

repeat with aName in theNames

    tell aName
        set fullName to (its item 1) & space & (its item 2) & space & (its item 3)
    end tell

    set baseFolderPath to (_folder's stringByAppendingPathComponent:fullName)

    -- Copy
    set fmResult to (fileMgr's createDirectoryAtPath:(baseFolderPath's stringByAppendingPathComponent:"Copy") withIntermediateDirectories:true attributes:(missing value) |error|:(missing value))

    -- Layout and sub folders
    set fmResult to (fileMgr's createDirectoryAtPath:(baseFolderPath's stringByAppendingPathComponent:"Layout/Links/Final") withIntermediateDirectories:true attributes:(missing value) |error|:(missing value))
    set fmResult to (fileMgr's createDirectoryAtPath:(baseFolderPath's stringByAppendingPathComponent:"Layout/Links/Original") withIntermediateDirectories:true attributes:(missing value) |error|:(missing value))
    set fmResult to (fileMgr's createDirectoryAtPath:(baseFolderPath's stringByAppendingPathComponent:"Layout/Links/Working") withIntermediateDirectories:true attributes:(missing value) |error|:(missing value))
    set fmResult to (fileMgr's createDirectoryAtPath:(baseFolderPath's stringByAppendingPathComponent:"Layout/Mocks/Cover Jpegs") withIntermediateDirectories:true attributes:(missing value) |error|:(missing value))

    -- Misc
    set fmResult to (fileMgr's createDirectoryAtPath:(baseFolderPath's stringByAppendingPathComponent:"Misc") withIntermediateDirectories:true attributes:(missing value) |error|:(missing value))

    -- PDFs and sub folders
    set fmResult to (fileMgr's createDirectoryAtPath:(baseFolderPath's stringByAppendingPathComponent:"PDFs/Email for Approval") withIntermediateDirectories:true attributes:(missing value) |error|:(missing value))
    set fmResult to (fileMgr's createDirectoryAtPath:(baseFolderPath's stringByAppendingPathComponent:"PDFs/Press PDF") withIntermediateDirectories:true attributes:(missing value) |error|:(missing value))
    set fmResult to (fileMgr's createDirectoryAtPath:(baseFolderPath's stringByAppendingPathComponent:"PDFs/Proof PDF") withIntermediateDirectories:true attributes:(missing value) |error|:(missing value))

end repeat

If I’m wrong about the final hierarchy you are trying for, it shouldn’t be too difficult to adjust the code to get what you want.

Anyway, given this Excel data:

26

You will get this result:

Thank you so much! It works!!! Sorry for the confusing post on folder hierarchy.

Hey Folks,

When building folder hierarchies I always prefer to start with a text listing of directories/subdirectories when possible. (Whether I’m using the shell or AppleScript.)

It reads easier and is far easier to edit.

# User Setting 
rootDir=~/'test_directory/Test_Mkdir/';
mkdir -p "$rootDir";
cd "$rootDir";

subDirList='
./Copy/
./Layout/
./Layout/Links/
./Layout/Links/Final/
./Layout/Links/Original/
./Layout/Links/Working/
./Layout/Mocks/
./Layout/Mocks/Cover Jpegs/
./Misc/
./PDFs/
./PDFs/Email for Approval/
./PDFs/Press PDF/
./PDFs/Proof PDF/
';

while read -r dirPath
do
   if [[ $dirPath != '' ]]; then
      mkdir -p "$dirPath";
   fi
done <<< "$subDirList"

The Bash script above is intended as an example of that, rather than a complete solution for Tulasi’s question.

To do the same thing in AppleScript I’d probably do something like this:

----------------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2019/01/30 16:39
# dMod: 2019/01/30 17:01
# Appl: AppleScriptObjC
# Task: Create a Directory with a Given Set of Subdirectories.
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @ASObjC, @Create, @Directory, @Subdirectories
----------------------------------------------------------------
use AppleScript version "2.4" --» Yosemite or later
use framework "Foundation"
use scripting additions
----------------------------------------------------------------

try
   
   set targetDir to POSIX path of ((path to home folder as text) & "test_directory:")
   set newDirName to "Test_Mkdir"
   set rootDirPath to targetDir & newDirName
   set subDirList to "
/Copy/
/Layout/
/Layout/Links/
/Layout/Links/Final/
/Layout/Links/Original/
/Layout/Links/Working/
/Layout/Mocks/
/Layout/Mocks/Cover Jpegs/
/Misc/
/PDFs/
/PDFs/Email for Approval/
/PDFs/Press PDF/
/PDFs/Proof PDF/
"
   
   if itemExists(rootDirPath) = false then
      
      # Create the root directory of the set.
      its createDirectoryAtPathWithIntermediates:rootDirPath
      # Remove any blank lines from subDirList.
      set subDirList to its cngStr:"(?m)(?:^\\h*$|\\Z)\\n" intoString:"" inString:subDirList
      # Prepend rootDirPath to each subdirectory path.
      set subDirList to its cngStr:"(?m)^(?=/)" intoString:rootDirPath inString:subDirList
      set subDirList to paragraphs of subDirList
      
      repeat with newDirPath in subDirList
         (its createDirectoryAtPathWithIntermediates:newDirPath)
      end repeat
      
   else
      error "An item on disk with path: " & linefeed & linefeed & tab & rootDirPath & linefeed & linefeed & " already exists!"
   end if
   
on error e number n
   set e to e & return & return & "Num: " & n
   if n ≠ -128 then
      try
         tell application (path to frontmost application as text) to set ddButton to button returned of ¬
            (display dialog e with title "ERROR!" buttons {"Copy Error Message", "Cancel", "OK"} ¬
               default button "OK" giving up after 30)
         if ddButton = "Copy Error Message" then set the clipboard to e
      end try
   end if
end try

----------------------------------------------------------------
--» HANDLERS
----------------------------------------------------------------
on cngStr:findString intoString:replaceString inString:dataString
   set anNSString to current application's NSString's stringWithString:dataString
   set dataString to (anNSString's stringByReplacingOccurrencesOfString:findString withString:replaceString ¬
      options:(current application's NSRegularExpressionSearch) range:{0, length of dataString}) as text
end cngStr:intoString:inString:
----------------------------------------------------------------
on createDirectoryAtPathWithIntermediates:thePath
   set {theResult, theError} to current application's NSFileManager's defaultManager()'s createDirectoryAtPath:thePath ¬
      withIntermediateDirectories:true attributes:(missing value) |error|:(reference)
   if not (theResult as boolean) then
      set errorMsg to theError's localizedDescription() as text
      error errorMsg
   end if
end createDirectoryAtPathWithIntermediates:
----------------------------------------------------------------
on itemExists(theItem)
   set pathStr to POSIX path of theItem
   return (current application's NSFileManager's defaultManager()'s fileExistsAtPath:pathStr) as boolean
end itemExists
----------------------------------------------------------------

NOTE – I’m once again skipping the step of reading the root folder names out of Excel, since that’s been covered.

-Chris

Excellent advice.

You’re probably working too hard in building the list of subdirectories, in that the string method stringsByAppendingPaths: is designed for just this thing. So:

set rootDirPath to POSIX path of ((path to home folder as text) & "test_directory:Test_Mkdir")
set rootDirPath to current application's NSString's stringWithString:rootDirPath
set thePaths to rootDirPath's stringsByAppendingPaths:(paragraphs of subDirList)

In your sample you’d end up with the original path in there (twice) because of the leading and trailing return, but it’s of little matter here.

1 Like

Hey Shane,

I strip the vertical whitespace in subDirList before converting the path strings to a list, so that’s not an issue.

Oh, that’s nice!

It manages the “/” delimiter in the appended strings whether the user has added them or not – so a safety check for the delimiter isn’t necessary.

Thanks.

-Chris