Search code examples
bashshellcsvattachmentmailx

Bash Script using mailx to mail attachment

I have a bash script that runs a query in postgres, it outputs to csv. I want to add to that script to use mailx to email that .csv file to a particular email.

The problem I am having is it will not email the file. I can get the email so I know mailx is setup correctly. I just cannot get it to email it as an attachment. It can also email the output in the body of the email.

So here is the code.

    #!/bin/bash
    NOWDATE=`date +%m-%d-%Y`
    PGPASSWORD=password psql -w -h host -p 5432 -d database -U user -o /tmp/folder/file-$NOWDATE.csv <<EOF
    Query is here

    # remove the first 2 lines of the report as they are headers
    sed -i '2d' /tmp/folder/file-$NOWDATE.csv

    uuencode /tmp/folder/file-$NOWDATE.csv | mailx -s "Accounts No Credit Card Report for '$NOWDATE'" mail@gmail.com

I have tried the mailx part with:

    uuencode /tmp/folder/file-$NOWDATE.csv /tmp/folder/file-$NOWDATE.csv | mailx -s "Accounts No Credit Card Report for '$NOWDATE'" mail@gmail.com

and 

    uuencode /tmp/folder/file-$NOWDATE.csv file-$NOWDATE.csv | mailx -s "Accounts No Credit Card Report for '$NOWDATE'" mail@gmail.com

So the problem I get is it spits out this error when I run the .sh file.

    uuencode: fopen-ing /tmp/folder/file-01-11-2011.csv: Unknown system error
Solution 
  • NOWDATE=`date +%m-%d-%Y`

    It's up to you, but consider using ISO-8601 format, YYYY-MM-DD (%Y-%m-%d). Among other advantages, it sorts nicely.

    # remove the first 2 lines of the report as they are headers
sed -i '2d' /tmp/folder/file-$NOWDATE.csv

    This doesn't remove the first two lines, it just removes the second line. Change '2d' to '1,2d' (but see below).

    Note that this modifies the file in place.

    uuencode /tmp/folder/file-$NOWDATE.csv | mailx [...]

    If uuencode is given only one file name, it reads from standard input and puts the name into its output. Your following text, "I have tried the mailx part with:" ..., indicates that you're probably aware of this -- but you haven't shown us the code that fixes that issue other than in snippets.

    The error message you're getting:

    uuencode: fopen-ing /tmp/folder/file-01-11-2011.csv: Unknown system error

    isn't what you'd normally get if the file doesn't exist. I don't know what would cause an "Unknown system error" like that.

    But here's an alternative that (a) is a bit cleaner IMHO, and (b) doesn't require uuencode to attempt to read the file:

    #!/bin/bash

NOWDATE=`date +%m-%d-%Y` # but %Y-%d-%m is better
DIR=/tmp/folder
FILE=file-$NOWDATE.csv
RECIPIENT=user@example.com

PGPASSWORD=password psql -w -h host -p 5432 -d database -U user -o $DIR/$FILE <<EOF
... Query is here
EOF

tail -n +3 $DIR/$FILE | uuencode $FILE | \
    mailx -s "Accounts No Credit Card Report for '$NOWDATE'" $RECIPIENT