*NIX directory listing into MSExcel

Today a co-worker asked me to help him prepare a report of email accounts on a Linux box running postfix. He wanted a list of the inboxes, their sizes, and their dates of modification. He wanted it in a text file that he could import into a spreadsheet program. After some experimenting, we came up with the following:

ls -lt --time-style=long-iso /var/mail/
  | grep -v ^total
  | awk '{ print $5 "," $6 , $7 "," $8 }'
  | sed -re 's/$/\r/'
  > /tmp/mail_list.csv

The -lt argument to ls gives the long listing and sorts by modification date (in descending order), and the --time-style=long-iso argument ensures that the timestamp format is uniform for all files (without it, you might get one timestamp format for files younger than about 6 months and a different format for older files).

The slash at the end of /var/mail/ is also important, as /var/mail is often a symlink to /var/spool/mail/, and ls -l /var/mail lists the symlink rather than the contents of the referenced directory.

ls -l of a directory typically produces a line resembling total NNNN (where NNNN is some integer) at the beginning of the output, so the grep -v ^total filters out any line starting with 'total'.

The awk command pulls out the 5th, 6th, 7th, and 8th columns of the space-separated text and formats the output as CSV (comma-separated variable). A couple of lines of output might look like this:

41951950,2006-09-13 05:29,root
723,2006-06-13 14:37,martinez

The sed filter inserts a carriage return character (\r) in front of each newline, giving the text DOS line termination (saves us the extra step of running unix2dos on the output file).

Finally, the output is redirected to a file which can be downloaded via an SFTP client and then opened in MSExcel by simply double-clicking on the downloaded file.