0

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
Keith Thompson
  • 254,901
  • 44
  • 429
  • 631
DoCnTex
  • 113
  • 2
  • 5
  • 11
  • Is the path `/tmp/folder/file-$NOWDATE.csv` correct? Do a `pwd` where you have the file and double check. This error occurs when `/path/to/file` does not exist. – jaypal singh Jan 11 '12 at 22:33
  • Also, `sed -i '2d' filename` deletes the second line and **not** first two. For deleting first two lines you need to do `sed -i '1,2d' filename` – jaypal singh Jan 11 '12 at 22:37
  • Jaypal. I agree the comment was wrong I found that I only needed to delete the second line... I had it first deleting the first and second. LOL... Ok I will check it out. – DoCnTex Jan 11 '12 at 22:39
  • the file path does exist with the pwd it did indeed show the correct path. bahhhhh... though I just put a general path to hide the paths it does indeed show the correct path.. Wonder if it is the $NOWDATE that is throwing it off, I did however try it even with the correct file name without the $NOWDATE and still got the error. Could I be missing something that should be installed? – DoCnTex Jan 11 '12 at 22:41
  • If the command works from the command line then it should work from the script too :-). Try placing curly braces around your variable. `uuencode /tmp/folder/file-${NOWDATE}.csv /tmp/folder/file-${NOWDATE}.csv | mailx -s "Accounts No Credit Card Report for '$NOWDATE'" mail@gmail.com` – jaypal singh Jan 11 '12 at 22:49
  • Sorry about my last comment, if you tried running it with the filename then braces won't help. Try running the command stand alone from the command line and let me know what you get. – jaypal singh Jan 11 '12 at 22:59

3 Answers3

3

If the problem is with uuencode...why cant you try mailx -a option which can also attach the files to the mail. Check this link for more info.

Raghuram
  • 3,937
  • 2
  • 19
  • 25
1
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
Keith Thompson
  • 254,901
  • 44
  • 429
  • 631
0

I had the same issue. A bash script executing the query, saving the csv file and mailing it. In my case it it gave the uuencode: fopen-ing /tmp/folder/file-01-11-2011.csv: Unknown system error

When I executed the script using the ksh shell, it worked perfectly fine without any issues. Like this - ksh script.sh This is just another pointer. In case uuencode gives error, then try executing it using ksh; it might work for you.

Ketan.G
  • 1
  • 1