Skip navigation

How to Make new folders with names from 2 columns from excel ?

880 Views 5 Replies Latest reply: Aug 12, 2013 6:25 AM by project4x RSS
Mitchell8793 Calculating status...
Currently Being Moderated
Mar 24, 2013 4:29 PM

Hello,
I am trying to automate the proccess of making a list of subfolders for peoples names

the names of the folders are comming from and Excel document with the surname in column A and first name in column B
I need the Surname first, then a Space, then the firstname for each folder

 

 

I figure the best way to do this would be with apple script.

 

this is what i have come up with so far..

 

 

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 "A1:A40" as list

          set _firstname to value of range "B1:B40" as list

  close active workbook

end tell

 

tell application "Finder"

          repeat with _foldername in _surname

  make new folder at folder _folder with properties {name:{(item 1 of _surname as text) & " " & (item 1 of _firstname as text)}}

          end repeat

end tell


 

 

This Succefully creates the 1st folder correctly but then it has an error

 

 

error "Finder got an error: The operation can’t be completed because there is already an item with that name." number -48

 

 

In the Excel document the range of values can rang from 1-25 to 1-35 entries so i figured to just get values from 1-40. Maybe there is a way to only get the range of values with the number of populated cells ? 

 

I guess the code doesnt repeat with item 2 of both lists and it tries to make the same folder name.


Any help is apreciated,
Thank you.

MacBook Pro, OS X Mountain Lion (10.8.3)
  • kikashi Level 1 Level 1 (10 points)

    Although you're looping through the list of surnames, you're repeatedly trying to create the same folder, using the first item of both the surname and firstname lists.

     

    Try this:

     

    before your loop,

     

         set i to 1

     

    within your loop, change both occurrences of "item 1 of" to "item i of" and add

     

         set i to i+1

     

    after the make new folder line.

     

    I expect there are better ways, and I've not tried it, but I think that will work.

     

    Good luck.

  • pjdube Level 3 Level 3 (830 points)

    You can use automator for this, although you will have to get some 3rd party actions.

     

    See below workflow. This works when you save the excel as a csv, then run this workflow and it will make those folders with the names.

     

    Screen Shot 2013-03-25 at 10.05.12 PM.JPG

  • pjdube Level 3 Level 3 (830 points)

    Amending your script above, try this:

     

    et _excel to choose file

    set _folder to choose folder

     

    tell application "Microsoft Excel"

      open _excel

              set theNames to value of range "A1:B40"

      close active workbook

    end tell

     

    repeat with aName in theNames

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

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

    end repeat


  • pjdube Level 3 Level 3 (830 points)

    Did you try it out?

  • project4x Level 1 Level 1 (10 points)

    Thank you very much, I just made 126 folders in less than a second with just one row from an Excel-file I row "N":

     

    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 "N2:N127" 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
    

     

    The counter 'i' had to be implemented in the loop as well.

    iPhone, iOS 4, MBP 1,1

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.