[SATLUG] Need scripting assistance

Tom Weeks tweeksjunk2 at theweeks.org
Sat Dec 29 01:02:07 CST 2007


On Friday 21 December 2007 22:56:11 Brian Lewis wrote:
> > Scenario:
> >
> > A large (perhaps a 500MB) spreadsheet full of contacts. The info
> > contains an account number, first name, last name, email address.
> >
> > How would I go about extracting every email address from the
> > spreadsheet into a text file, on their own line ie.
> >
> > johndoe at foo.com
> > janedoe at foo.com
> > bob at foo.com
> > etc etc
> >
> > Is this even possible?
> >
> > Even better,
> > johndoe at foo.com; janedoe at foo.com; bob at foo.com

My preferred way is to:
-save XLS out to a CSV file (you can do this in MS or OO)
-Then simply:
	$ cat filename.CSV | cut -f2 -d","

 where the 2 in "-f2" is the column number with the email addresses in it.  
This is nice because now you can do all kinds of things with it such as 
format it to put them all onto one big line of text:

	$ cat filename.CSV | cut -f2 -d"," | tr "\n" ";"
(which will give you one long line of ; delimited email addresses that can be 
pasted right into your mail client, or into another command line mail 
tool. :)


> > In increments of about 200.


Ahhh.. If you want to split the CSV file up into 200 email chunks, then 
something like this will "doit":
#!/bin/bash
# Usage is:
# doit <input CSV file>
# 

## User Variables
COUNT=200
FILEOUT=emails
# The field in the CSV that holds the email address
EFIELD=2

#####
FILE=$1
x=0

/bin/rm $FILEOUT-* >/dev/null 2>&1
cat $FILE | cut -f$EFIELD -d"," | while read line ; do
        let "x += 1"
        for i in $(seq $COUNT) ; do
                echo $line >> $FILEOUT-$x
                read line
        done
done

But there's really no need if your reason for doing so is to manually copy & 
paste them into some stupid GUI mail client!  Stop thinking like a Windows 
user! What a waste of time!  Think using the power of the UN*X command line! 
Just use the first method I showed you and pipe them  directly into mail, 
sendmail or other MUA (mail client).  Unless, you really just feel the need 
for all that copy and pasting. ;)

Cheers,

Tweeks


More information about the SATLUG mailing list