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!!

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.

Dysfunction said:

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.

Click to expand...

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!!

Dysfunction said:

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.

Click to expand...

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]

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 *

Dysfunction said:

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 *

Click to expand...

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"

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

Click to expand...

HIT return

Then copy this command into terminal

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

Click to expand...

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)

To add a directory, execute the following and replace <directory> with the path that you want: If you don't know the path, simply type Make sure that there is a space after that and then drag the folder from Finder. Finally, input the rest:
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.

louishen said:

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

Click to expand...

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?

vansmiths works better

vansmith said:

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.

Click to expand...

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.

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.

Click to expand...

That comes across as a bit rude, its not our fault that the Finder and Terminal convert bytes to KB slightly differently

louishen said:

vansmiths works better

Click to expand...

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.

Dysfunction said:

What's interesting is that it is the exact same command. Oh well.

Click to expand...

I was using yours - the credit goes to you for that one. I was simply helping to clarify things a little.

vansmith said:

I was using yours - the credit goes to you for that one. I was simply helping to clarify things a little.

Click to expand...

My /home drive crashed right after my second post. Nice.

I'm still kind of irritated.