Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

Create a directory list in CSV format UNIX
Sometimes you may need to get a sorted list of all files in a direcoty transformed in a database format, so, after getting the directory list you may end cutting and pasting the data to your database. On the other hand, there's an easy way to do that, if you just follow this tip. Open Terminal and type:
ls -lT | \
awk '{print "\""$10"\""";""\""$6" "$7", "$9"\""";""\""$5"\""}' \
| pbcopy
This will generate a CSV (comma-delimited) directory listing on the clipboard in the following format:
"Filename";"Creation Date";"Size"
For example:
"a2p";"Mar 21, 2005";"104608"
"aclocal";"Mar 20, 2005";"12606"
"aclocal-1.6";"Mar 20, 2005";"12606"
"addftinfo";"Mar 20, 2005";"59852"
"afmtodit";"Mar 20, 2005";"165346"
"alias";"Mar 21, 2005";"147"
"appleping";"Mar 21, 2005";"19656"
If you want to generate a file instead of sending the result to the clipboard, just replace the | pbcopy part with > xxx, where xxx is the file name you want to create.

[robg adds: When this hint was submitted, Geeklog killed the backslashes in the above command. After much trial and error, I believe I have them back where they need to be to generate the proper output. If there are errors, however, they are mine, not the original poster's. I also split the command onto three lines for a narrower display, but it should copy and paste.]
    •    
  • Currently 3.40 / 5
  You rated: 3 / 5 (5 votes cast)
 
[15,633 views]  

Create a directory list in CSV format | 11 comments | Create New Account
Click here to return to the 'Create a directory list in CSV format' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
Create a directory list in CSV format
Authored by: davidefrank on Aug 30, '06 09:02:01AM

This breaks if the file name contains spaces - try this instead:

ls -lT | awk '{print "\""substr($0,index($0,$10))"\""";""\""$6" "$7", "$9"\""";""\""$5"\""}'

The nested substr/index functions of the awk command say: "get a substring of the entire line ($0), starting at the offset (index) of the tenth word, and continuing (by default) to the end of the line."



[ Reply to This | # ]
Create a directory list in CSV format
Authored by: JasonInOregon on Aug 30, '06 09:31:38AM

I found that the suggested commands work great, but that the resultant data doesn't paste well in Excel. The output file also generates well, but Excel doesn't always separate the ";" delimiter. I switched the ";" for "," (commas) and now both the clipboard data and the file generation open well in Excel.

This is what I used:
ls -lT | awk '{print "\""substr($0,index($0,$10))"\""",""\""$6" "$7", "$9"\""",""\""$5"\""}' > folderlist.csv

I also tried:
ls -lT | awk '{print "\""substr($0,index($0,$10))"\""",""\""$6" "$7", "$9"\""",""\""$5"\""}' > folderlist.txt

Nothing has gone up in smoke... yet. Someone who is more expert than I am should check this to make sure it doesn't violate some natural law of the universe or something. =)



[ Reply to This | # ]
Create a directory list in CSV format
Authored by: jaysoffian on Aug 30, '06 09:31:47AM

That's not the creation date, it's the last modification time. Unix filesystems track three time related fields:

atime - last time the file was accessed. This is updated any time the file is opened.

mtime - last time the file was modified. This is updated any time the file's contents are changed.

ctime - last time the inode was changed. This one requires a little explanation. The inode is what tracks where on disk the file's contents are located as well as stores meta-information about the file (its permissions, owner, group, etc). So this is updated if you change the file itself, or if you change any of its meta-information.

j.



[ Reply to This | # ]
OS X file creation dates
Authored by: sjk on Aug 30, '06 12:21:49PM
For the curious, File Creation Dates on Mac OS X: Clash of the Cultures (@ blog.plasticsfuture.org) has more explanation and discussion about that topic.

[ Reply to This | # ]
Create a directory list in CSV format
Authored by: jaysoffian on Aug 30, '06 09:37:36AM
And here's a version that doesn't require parsing the output of 'ls -l'

ls | perl -ne 'chomp;@s=stat;printf qq|"%s","%s","%s"\n|, $_, $s[7], scalar localtime $s[9]'
I'm sure someone will followup with the ruby version shortly. :-) j.

[ Reply to This | # ]
Ruby version
Authored by: regeya on Aug 30, '06 12:07:33PM
With no 'ls' at all:

ruby -e 'Dir.foreach(".") {|f| x=File.stat(f) ; puts "#{f}, #{x.ctime}, #{x.size}"'} It could probably be shorter/prettier, and the output isn't exactly like the original, but it'll do. :-D

[ Reply to This | # ]

Another Ruby, this time with ls
Authored by: regeya on Aug 30, '06 01:06:45PM
ls | ruby -lne 'x = File.stat($_); puts "#{$_}, #{x.ctime}, #{x.size}"'

I suppose that one's cleaner than the other, but I still like the other better 'coz it's all Ruby. I'm weird that way. :-}

[ Reply to This | # ]

Another Ruby, this time with ls
Authored by: peragrin on Aug 31, '06 04:00:00AM

oddly enough this one fails with a folder with a custom icon.

where as the all ruby one parses it but leaves the line blank.

either way a quick modification a simple shell script and a way to create an index file for a directory is nice and easy.

---
I thought once I was found but it was only a dream



[ Reply to This | # ]
Create a directory list in CSV format
Authored by: jaysoffian on Aug 30, '06 09:43:43AM
BTW, probably the easiest way to do this is to just use the
/usr/bin/stat
command. It takes a -f (format) option so you can specify any output format you'd like.
man stat
j.

[ Reply to This | # ]
Create a directory list in CSV format
Authored by: ianeiloart on Aug 31, '06 03:03:41AM

Oi! That's not CSV. CSV is "comma separated values". What you have there are semi-colon separated values. Go back to English Language 101!

---
--
Ian Eiloart



[ Reply to This | # ]
Create a directory list in CSV format
Authored by: regeya on Aug 31, '06 01:56:56PM

I understand your exception, but many CSV parsers/emitters allow for different characters.

Just remember that there's no standard on CSV, pretend CSV only stands for CSV, and the stress-induced headaches will cease eventually.



[ Reply to This | # ]