Results 1 to 15 of 15
  1. #1
    Extracting File Sizes from finder and bring them into Excel

    Member Since
    Jan 20, 2012
    Posts
    10
    Extracting File Sizes from finder and bring them into Excel
    On an Apple computer, does anyone know how to extract all the file sizes of all the files in a particular folder and put them in Excel (preferably) or a Word doc? Right now I'm taking the file sizes and manually typing them into excel and it doesn't seem like a good use of my time. Plus it's taking forever. There's got to be a way to have the computer do this for me in Automator. I'm googling it and not finding what I want. I couldn't find the workflow in Automator either. Any suggestions are appreciated!!

  2. #2
    Extracting File Sizes from finder and bring them into Excel
    Dysfunction's Avatar
    Member Since
    Mar 17, 2008
    Location
    Tucson, AZ
    Posts
    6,821
    Specs:
    Way... way too many specs to list.
    This is really easily doable in terminal (that is from a command line), I'm not sure how to do it in Finder.

    So, from a command line (assuming you want human readable file sizes.. that is 17B/64M etc)..

    ls -lh | awk '{print $9","$5}' > <name>.csv

    this will generate a comma separated file you can just open in either. If you want it expressed in bytes, drop the 'h' from the ls command.
    mike
    This machine kills fascists
    Got # ? phear the command line!

  3. #3
    Extracting File Sizes from finder and bring them into Excel

    Member Since
    Jan 20, 2012
    Posts
    10
    Quote Originally Posted by Dysfunction View Post
    This is really easily doable in terminal (that is from a command line), I'm not sure how to do it in Finder.

    So, from a command line (assuming you want human readable file sizes.. that is 17B/64M etc)..

    ls -lh | awk '{print $9","$5}' > <name>.csv

    this will generate a comma separated file you can just open in either. If you want it expressed in bytes, drop the 'h' from the ls command.
    I'm really unfamiliar with terminal and all of the coding above.... I'm not sure how to execute this task with the information you've provided above. Where you wrote "<name>", is that where I type in the "path" of where the folder lives on the computer? I want to be able to take a line of code, point it to a folder, hit enter, and it gives me a list of all the file sizes from that folder (in order of file name).

    Do I have to type in the file path of where the folder is located on the computer before typing in that line of code you gave me? I have no idea how to use that line of code. I copy and pasted it verbatim into Terminal (and obviously it doesnt contain any info about where the folder is) and I hit enter and it told me "-bash: syntax error near unexpected token `<'"

    Can you try explaining how to make this code work for me? I'm not exactly a 'noob' when it comes to computers. I'm pretty advanced. I'm a video editor. But I don't know much about terminal. thanks again for any help you can give to me!!

  4. #4
    Extracting File Sizes from finder and bring them into Excel

    Member Since
    Jan 20, 2012
    Posts
    10
    Quote Originally Posted by Dysfunction View Post
    This is really easily doable in terminal (that is from a command line), I'm not sure how to do it in Finder.

    So, from a command line (assuming you want human readable file sizes.. that is 17B/64M etc)..

    ls -lh | awk '{print $9","$5}' > <name>.csv

    this will generate a comma separated file you can just open in either. If you want it expressed in bytes, drop the 'h' from the ls command.
    I went into Terminal, and went to Shell-> New Command, Typed in your command and got this:

    -ls: $9","$5}': No such file or directory
    -ls: '{print: No such file or directory
    -ls: <Leeza: No such file or directory
    -ls: >: No such file or directory
    -ls: Gibbons>.csv: No such file or directory
    -ls: awk: No such file or directory
    -ls: |: No such file or directory

    [Process completed]

  5. #5
    Extracting File Sizes from finder and bring them into Excel
    Dysfunction's Avatar
    Member Since
    Mar 17, 2008
    Location
    Tucson, AZ
    Posts
    6,821
    Specs:
    Way... way too many specs to list.
    Sorry, using that type of notation is habit.

    So, if I wanted the file name to be homeDirectory.csv

    I'd use

    ls -lh | awk '{print $9","$5}' > homeDirectory.csv

    You will notice that it includes directories, and does NOT expand their full size (generally about 1.5k)

    If you want directories to show their full size..

    du -sch * |awk '{print $2","$1}' > homeDirectory.csv


    Now, for some 'splaining..

    ls is the list command. The awk allows us to (in this case) print only certain columns (the file name and size).

    du lets us examine disk usage.

    if you want to see how these work, try

    ls -lh

    du -sch *
    mike
    This machine kills fascists
    Got # ? phear the command line!

  6. #6
    Extracting File Sizes from finder and bring them into Excel

    Member Since
    Jan 20, 2012
    Posts
    10
    Quote Originally Posted by Dysfunction View Post
    Sorry, using that type of notation is habit.

    So, if I wanted the file name to be homeDirectory.csv

    I'd use

    ls -lh | awk '{print $9","$5}' > homeDirectory.csv

    You will notice that it includes directories, and does NOT expand their full size (generally about 1.5k)

    If you want directories to show their full size..

    du -sch * |awk '{print $2","$1}' > homeDirectory.csv


    Now, for some 'splaining..

    ls is the list command. The awk allows us to (in this case) print only certain columns (the file name and size).

    du lets us examine disk usage.

    if you want to see how these work, try

    ls -lh

    du -sch *
    I'm still very confused. I don't need to know to name the file I'm creating. I need to know how to execute this task, and how to point it to a specific folder and tell it to look through that folder and give me a list of file sizes within the folder. Can you please give me specific instructions?

    Because I went to Terminal -> New Command. I typed in "ls -lh | awk '{print $9","$5}' > homeDirectory.csv", and it said the following:

    -ls: $9","$5}': No such file or directory
    -ls: '{print: No such file or directory
    -ls: >: No such file or directory
    -ls: awk: No such file or directory
    -ls: |: No such file or directory
    -rw-r--r-- 1 rachel staff 145B Jul 10 11:06 homeDirectory.csv

    [Process completed]



    And it didnt create any document for me. Also, that code I gave it doesn't tell terminal where I want terminal to be looking. How do I tell terminal where to look?

    What is the SPECIFIC code I'd have to type in the command window to make it read the contents of a folder on the desktop called "ITEMSHERE"

  7. #7
    Extracting File Sizes from finder and bring them into Excel
    louishen's Avatar
    Member Since
    Oct 22, 2007
    Location
    London
    Posts
    8,968
    Specs:
    Mac Mini Core i7 2012 | White 2009 MacBook 2 Ghz | 733 Mhz G4 Quicksilver
    OPen terminal

    type cd at the command prompt

    Drag the desired folder onto the terminal and that will give you the directory path

    cd /Users/me/Documents/Portfolio\ and\ CV/CV/Images
    HIT return

    Then copy this command into terminal

    ls -lT | \
    awk '{print "\""$10"\""";""\""$6" "$7", "$9"\""";""\""$5"\""}' \
    | pbcopy
    That will copy the Filename, Creation Date and Size as a semicolon separated text file to the clipboard

    paste the text into a text editor and save it with the csv extension

    open the file in excel and make sure semicolon is set as the separater

    the file sizes are in bytes, use a calculation to convert them to KB ( 1024) or MB ( 1048576)
    Member of the Month September 2008 & August 2012 | Found advice useful? use the rep system

  8. #8
    Extracting File Sizes from finder and bring them into Excel
    vansmith's Avatar
    Member Since
    Oct 19, 2008
    Location
    Toronto
    Posts
    19,395
    Specs:
    2012 13" MBP (2.5 i5, 8GB)
    To add a directory, execute the following and replace <directory> with the path that you want:
    Code:
    ls -lh <directory> | awk '{print $9","$5}' > homeDirectory.csv
    If you don't know the path, simply type
    Code:
    ls -lh
    Make sure that there is a space after that and then drag the folder from Finder. Finally, input the rest:
    Code:
    | awk '{print $9","$5}' > homeDirectory.csv
    It looks like the code is being separated and is spanning multiple lines - make sure that command is all on one line before you execute it.
    Important Links: Community Guidelines : Use the reputation system if you've been helped.
    M-F Blog :: Write for the blog
    Writing a Quality Post

  9. #9
    Extracting File Sizes from finder and bring them into Excel

    Member Since
    Jan 20, 2012
    Posts
    10
    Quote Originally Posted by louishen View Post
    OPen terminal

    type cd at the command prompt

    Drag the desired folder onto the terminal and that will give you the directory path



    HIT return

    Then copy this command into terminal



    That will copy the Filename, Creation Date and Size as a semicolon separated text file to the clipboard

    paste the text into a text editor and save it with the csv extension

    open the file in excel and make sure semicolon is set as the separater

    Thanks for these instructions. I did just that. CD, then I dragged the folder into terminal and it typed the path out for me. I hit return and it said:

    Last login: Wed Jul 10 11:20:31 on ttys000
    Editing-West-8:~ rachel$ cd/Volumes/Alzheimer\'s\ Assn/Alz\ Drive\ Folder/0_Organized\ by\ Year/2008/Alz_Champions08/Footage/Leeza\ Gibbons
    -bash: cd/Volumes/Alzheimer's Assn/Alz Drive Folder/0_Organized by Year/2008/Alz_Champions08/Footage/Leeza Gibbons: No such file or directory

    Then I typed in that second code you gave me....

    ls -lT | \
    awk '{print "\""$10"\""";""\""$6" "$7", "$9"\""";""\""$5"\""}' \
    | pbcopy

    I hit return. Then I went into TextEditor and pasted Paste and I got this:

    "";" , ";""
    "Applications";"Jun 26, 2013";"68"
    "Desktop";"Jul 10, 2013";"306"
    "Documents";"Jun 27, 2013";"306"
    "Downloads";"Jul 9, 2013";"510"
    "Library";"Jun 21, 2013";"1530"
    "Movies";"Jun 10, 2013";"102"
    "Music";"Jul 2, 2013";"136"
    "Pictures";"Jul 3, 2013";"170"
    "Public";"Jun 10, 2013";"136"
    "homeDirectory.csv";"Jul 10, 2013";"145"

    This is close to what I want, only this is not the right location, obviously. You can tell that the command got confused about the location when I dragged in the folder and it told me "No such file or directory". I thought maybe its giving me issues because its on a server? So I dragged that folder to the desktop and tried again:

    Editing-West-8:~ rachel$ cd/Users/rachel/Desktop/Leeza\ Gibbons
    -bash: cd/Users/rachel/Desktop/Leeza Gibbons: No such file or directory
    Editing-West-8:~ rachel$

    ....then I typed in that command of yours... did the paste into text edit.... and got the same thing:

    "";" , ";""
    "Applications";"Jun 26, 2013";"68"
    "Desktop";"Jul 10, 2013";"272"
    "Documents";"Jun 27, 2013";"306"
    "Downloads";"Jul 9, 2013";"510"
    "Library";"Jun 21, 2013";"1530"
    "Movies";"Jun 10, 2013";"102"
    "Music";"Jul 2, 2013";"136"
    "Pictures";"Jul 10, 2013";"204"
    "Public";"Jun 10, 2013";"136"
    "homeDirectory.csv";"Jul 10, 2013";"145"

    Why isn't it working for me?

  10. #10
    Extracting File Sizes from finder and bring them into Excel
    louishen's Avatar
    Member Since
    Oct 22, 2007
    Location
    London
    Posts
    8,968
    Specs:
    Mac Mini Core i7 2012 | White 2009 MacBook 2 Ghz | 733 Mhz G4 Quicksilver
    vansmiths works better
    Member of the Month September 2008 & August 2012 | Found advice useful? use the rep system

  11. #11
    Extracting File Sizes from finder and bring them into Excel

    Member Since
    Jan 20, 2012
    Posts
    10
    Quote Originally Posted by vansmith View Post
    To add a directory, execute the following and replace <directory> with the path that you want:
    Code:
    ls -lh <directory> | awk '{print $9","$5}' > homeDirectory.csv
    If you don't know the path, simply type
    Code:
    ls -lh
    Make sure that there is a space after that and then drag the folder from Finder. Finally, input the rest:
    Code:
    | awk '{print $9","$5}' > homeDirectory.csv
    It looks like the code is being separated and is spanning multiple lines - make sure that command is all on one line before you execute it.
    I go to terminal, type in "ls -lh /Users/rachel/Desktop/Leeza\ Gibbons " enter. and it generates a list. The list is the exact same list that the application "FileFinder" made. Terminal's list and FileFinder's list says that "GibbensBroll1.mov" is 285mb. But when I open a finder window (the place where the file is located), it tells me the file is 298.8 mb, NOT 285!!! Had the application "FileFinder" given me correct numbers in the first place, I wouldn't be trying to figure out all this ridiculous coding in terminal. But now I find out that Terminal is giving me the same inaccurate information as "FileFinder", I've come full circle. Why are two separate applications telling me that "GibbensBroll1.mov" is 285mb, but finder displays the size as 298.8 mb? This is really crucial that these numbers match, and they don't.

  12. #12
    Extracting File Sizes from finder and bring them into Excel
    louishen's Avatar
    Member Since
    Oct 22, 2007
    Location
    London
    Posts
    8,968
    Specs:
    Mac Mini Core i7 2012 | White 2009 MacBook 2 Ghz | 733 Mhz G4 Quicksilver
    The terminal results are simply more accurate than the finder in terms of bytes to KB conversion.

    This is really crucial that these numbers match, and they don't.
    That comes across as a bit rude, its not our fault that the Finder and Terminal convert bytes to KB slightly differently
    Member of the Month September 2008 & August 2012 | Found advice useful? use the rep system

  13. #13
    Extracting File Sizes from finder and bring them into Excel
    Dysfunction's Avatar
    Member Since
    Mar 17, 2008
    Location
    Tucson, AZ
    Posts
    6,821
    Specs:
    Way... way too many specs to list.
    Quote Originally Posted by louishen View Post
    vansmiths works better
    What's interesting is that it is the exact same command. Oh well.

    btw, neither is more accurate. One uses base2, and the other one base10. This is why they reflect differently. You can always list it in bytes and do the math.
    mike
    This machine kills fascists
    Got # ? phear the command line!

  14. #14
    Extracting File Sizes from finder and bring them into Excel
    vansmith's Avatar
    Member Since
    Oct 19, 2008
    Location
    Toronto
    Posts
    19,395
    Specs:
    2012 13" MBP (2.5 i5, 8GB)
    Quote Originally Posted by Dysfunction View Post
    What's interesting is that it is the exact same command. Oh well.
    I was using yours - the credit goes to you for that one. I was simply helping to clarify things a little.
    Important Links: Community Guidelines : Use the reputation system if you've been helped.
    M-F Blog :: Write for the blog
    Writing a Quality Post

  15. #15
    Extracting File Sizes from finder and bring them into Excel
    Dysfunction's Avatar
    Member Since
    Mar 17, 2008
    Location
    Tucson, AZ
    Posts
    6,821
    Specs:
    Way... way too many specs to list.
    Quote Originally Posted by vansmith View Post
    I was using yours - the credit goes to you for that one. I was simply helping to clarify things a little.
    My /home drive crashed right after my second post. Nice.

    I'm still kind of irritated.
    mike
    This machine kills fascists
    Got # ? phear the command line!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 06-08-2012, 08:40 PM
  2. Weird file sizes in Finder
    By slapple in forum OS X - Operating System
    Replies: 23
    Last Post: 01-26-2012, 11:12 AM
  3. FCP file sizes
    By graham722 in forum Movies and Video
    Replies: 0
    Last Post: 02-23-2011, 09:51 AM
  4. calculating All Sizes - Finder
    By shahvikram123 in forum OS X - Operating System
    Replies: 0
    Last Post: 07-07-2008, 06:48 AM
  5. garageband file sizes.....
    By christm in forum Music, Audio, and Podcasting
    Replies: 3
    Last Post: 02-05-2007, 05:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •