New To Mac-Forums?

Welcome to our community! Join the discussion today by registering your FREE account. If you have any problems with the registration process, please contact us!

Get your questions answered by community gurus Advice and insight from world-class Apple enthusiasts Exclusive access to members-only contests, giveaways and deals

Join today!

 
Start a Discussion
 

Mac-Forums Brief

Subscribe to Mac-Forums Brief to receive special offers from Mac-Forums partners and sponsors

Join the conversation RSS
OS X - Operating System General OS operation information and support

Extracting File Sizes from finder and bring them into Excel


Post Reply New Thread Subscribe

 
Thread Tools
rachinc

 
Member Since: Jan 20, 2012
Posts: 10
rachinc is on a distinguished road

rachinc is offline
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!!
QUOTE Thanks
Dysfunction

 
Dysfunction's Avatar
 
Member Since: Mar 17, 2008
Location: Tucson, AZ
Posts: 6,624
Dysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant future
Mac Specs: 2008 and 2011 15" mbps, late 11 iMac, iPhone 4s, and too many ipods and other stuff

Dysfunction is offline
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!
QUOTE Thanks
rachinc

 
Member Since: Jan 20, 2012
Posts: 10
rachinc is on a distinguished road

rachinc is offline
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!!
QUOTE Thanks
rachinc

 
Member Since: Jan 20, 2012
Posts: 10
rachinc is on a distinguished road

rachinc is offline
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]
QUOTE Thanks
Dysfunction

 
Dysfunction's Avatar
 
Member Since: Mar 17, 2008
Location: Tucson, AZ
Posts: 6,624
Dysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant future
Mac Specs: 2008 and 2011 15" mbps, late 11 iMac, iPhone 4s, and too many ipods and other stuff

Dysfunction is offline
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!
QUOTE Thanks
rachinc

 
Member Since: Jan 20, 2012
Posts: 10
rachinc is on a distinguished road

rachinc is offline
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"
QUOTE Thanks
louishen

 
louishen's Avatar
 
Member Since: Oct 22, 2007
Location: London
Posts: 8,935
louishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant future
Mac Specs: Mac Mini Core i7 2012 | White 2009 MacBook 2 Ghz | 733 Mhz G4 Quicksilver

louishen is offline
OPen terminal

type cd at the command prompt

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

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

Then copy this command into terminal

Quote:
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
QUOTE Thanks
vansmith

 
vansmith's Avatar
 
Member Since: Oct 19, 2008
Location: Toronto
Posts: 17,674
vansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond repute
Mac Specs: 2012 13" MBP (2.5 i5, 8GB)

vansmith is offline
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
Personal Twitter
QUOTE Thanks
rachinc

 
Member Since: Jan 20, 2012
Posts: 10
rachinc is on a distinguished road

rachinc is offline
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?
QUOTE Thanks
louishen

 
louishen's Avatar
 
Member Since: Oct 22, 2007
Location: London
Posts: 8,935
louishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant future
Mac Specs: Mac Mini Core i7 2012 | White 2009 MacBook 2 Ghz | 733 Mhz G4 Quicksilver

louishen is offline
vansmiths works better

Member of the Month September 2008 & August 2012 | Found advice useful? use the rep system
QUOTE Thanks
rachinc

 
Member Since: Jan 20, 2012
Posts: 10
rachinc is on a distinguished road

rachinc is offline
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.
QUOTE Thanks
louishen

 
louishen's Avatar
 
Member Since: Oct 22, 2007
Location: London
Posts: 8,935
louishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant futurelouishen has a brilliant future
Mac Specs: Mac Mini Core i7 2012 | White 2009 MacBook 2 Ghz | 733 Mhz G4 Quicksilver

louishen is offline
The terminal results are simply more accurate than the finder in terms of bytes to KB conversion.

Quote:
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
QUOTE Thanks
Dysfunction

 
Dysfunction's Avatar
 
Member Since: Mar 17, 2008
Location: Tucson, AZ
Posts: 6,624
Dysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant future
Mac Specs: 2008 and 2011 15" mbps, late 11 iMac, iPhone 4s, and too many ipods and other stuff

Dysfunction is offline
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!
QUOTE Thanks
vansmith

 
vansmith's Avatar
 
Member Since: Oct 19, 2008
Location: Toronto
Posts: 17,674
vansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond reputevansmith has a reputation beyond repute
Mac Specs: 2012 13" MBP (2.5 i5, 8GB)

vansmith is offline
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
Personal Twitter
QUOTE Thanks
Dysfunction

 
Dysfunction's Avatar
 
Member Since: Mar 17, 2008
Location: Tucson, AZ
Posts: 6,624
Dysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant futureDysfunction has a brilliant future
Mac Specs: 2008 and 2011 15" mbps, late 11 iMac, iPhone 4s, and too many ipods and other stuff

Dysfunction is offline
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!
QUOTE Thanks

Post Reply New Thread Subscribe


« Stop reopening windows on restart | Cross-user Copy/Paste and Disk access »
Thread Tools

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off
Forum Jump

Similar Threads
Thread
Thread Starter
Forum
Replies
Last Post
How can Finder searches only bring up file names that match the term in Lion? oxband OS X - Operating System 5 06-08-2012 08:40 PM

All times are GMT -4. The time now is 09:17 PM.

Powered by vBulletin
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
X

Welcome to Mac-Forums.com

Create your username to jump into the discussion!

New members like you have made this community the ultimate source for your Mac since 2003!


(4 digit year)

Already a member?