URGENT: Read CSV file and altert details via formatted email (UNIX SHELL SCRIPT)

Dushan De Silva
Dushan De Silva used Ask the Experts™
on
Hi Experts,
I'm having following csv file format and I want to read that file and should send email alters if last column values are difference
------------------csv file------------------
Query,group,IBM,product
Clam,Senehasa,12345,12345
Jane,SSC,23456,23457
Mmettha,SSC,34567,34566
--------------------------------------------
first line of this csv file contains titles of columns.
If there is any count difference between last two columns(IBM and product) then email alert should send to dushan_desilva@yahoo.com
mentioning that particular row with details and it should be in red color as attached image with additional delta field.

Also error handling(while reading csv file and sending emails) should write to a log file.

http://www.experts-exchange.com/OS/Unix/Q_26557443.html

BR Dushan.
delta.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Here's the command to get the last field of each line:
cat file | awk '{FS = ","}; NR > 1 {last = $NF} ; {print last}'

You can later write code to compare those values (if they're not empty)
If you have an anomaly, output your file as HTML and send out the e-mail.
Dushan De SilvaTechnology Architect

Author

Commented:
Hi Dimaj,
Thanks! But I appreciate full solution.
Try the attached script.  You'll need to add some style attributes or font tags, and maybe play around with the mail headers, to get it working how you want, but this will get you started.  The script expects your csv on stdin.

#!/bin/sh
if awk 'BEGIN  {FS = ","; x=1};
        NR > 1 {print "<TR><TD>" $1 "</TD><TD>" $2 "</TD><TD>" $3 "</TD><TD>" $4 "</TD><TD>" $4-$3 "</TD></TR>"; if ($4!=$3) x=0};
        END    {exit(x)}' > tmpfile
then
(echo 'To: dushan_desilva@yahoo.com
MimeVersion: 1.0
ContentType: text/html
ContentTransferEncoding: 7bit

<HTML>
<TABLE>
<TR><TH>Query Label</TH><TH>Query Group</TH><TH>IBM Results</TH><TH>Product Results</TH><TH>Results?</TH></TR>'
cat tmpfile
echo '</TABLE></HTML>
.
') |sendmail -s Alert dushan_desilva@yahoo.com
fi

Open in new window

Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Dushan De SilvaTechnology Architect

Author

Commented:
Hi Superdave,
Did you test your script? How can I use it? It seems never ending loop is running on it.
Try this version; I had time to add the background colors and look up the correct mail headers.  Copy it to a script, say mailalert.sh, chmod it to 777, and run:
mailalert.sh < mycsvfile

Or you could put it in a pipeline.  I also put better debugging things in it, and did a translation thing to map @ and ! to quotes, so you're not allowed to have those in the names now.  (I gave up in frustration last night on putting in color attributes because escaping the quotes is too much of a pain.)  This time I tested it all the way, it does generate and send the email.
#!/bin/sh
if awk 'BEGIN  {FS = ","; x=1};
        NR > 1 {if ($4!=$3) {
                    print "<TR style=!background: yellow!><TD>" $1 "</TD><TD>" $2 "</TD><TD>" $3 "</TD><TD>" $4 "</TD><TD style=!background: red!>" $4-$3 "</TD></TR>";}
                else {
                    print "<TR style=!background: white!><TD>" $1 "</TD><TD>" $2 "</TD><TD>" $3 "</TD><TD>" $4 "</TD><TD style=!background: red!>" $4-$3 "</TD></TR>"; x=0}};
        END    {exit(x)}' |tr @! \"\' > tmpfile
then
(echo 'To: dushan_desilva@yahoo.com
Subject: Alert
Mime-Version: 1.0
Content-Type: text/html
Content-Transfer-Encoding: 7bit

<HTML>
<TABLE>
<TR><TH>Query Label</TH><TH>Query Group</TH><TH>IBM Results</TH><TH>Product Results</TH><TH>Results?</TH></TR>'
cat tmpfile
echo '</TABLE></HTML>
.
') > tmpfile2 && echo "Sending mail" && sendmail -v -X tmp.log dushan_desilva@yahoo.com < tmpfile2
fi

Open in new window

Dushan De SilvaTechnology Architect

Author

Commented:
Hi Superdave,
Thanks a lot for your great effort!
But in my system sendmail command seems not working.
---------------------------------------------------------------------------------
email_csv]$ ./mailalert.sh < mycsvfile.csv
Sending mail
./script5.sh: line 21: sendmail: command not found
email_csv]$ sendmail
-bash: sendmail: command not found
---------------------------------------------------------------------------------

Then I've tried with mail command with giving last second line as below
---------------------------------------------------------------------------------------------------------------
') > tmpfile2 && echo "Sending mail" && mail -s tmp.log dushan_desilva@yahoo.com < tmpfile2
---------------------------------------------------------------------------------------------------------------

It's sending the email, but email body contains the HTML code, not the HTML output as below.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
To: dushan_desilva@yahoo.com
Subject: Alert
Mime-Version: 1.0
Content-Type: text/html
Content-Transfer-Encoding: 7bit

<HTML>
<TABLE>
<TR><TH>Query Label</TH><TH>Query Group</TH><TH>IBM Results</TH><TH>Product Results</TH><TH>Results?</TH></TR>
<TR style='background: white'><TD>Clam</TD><TD>Senehasa</TD><TD>12345</TD><TD>12345</TD><TD style='background: red'>0</TD></TR>
<TR style='background: yellow'><TD>Jane</TD><TD>SSC</TD><TD>23456</TD><TD>23457</TD><TD style='background: red'>1</TD></TR>
<TR style='background: yellow'><TD>Mmettha</TD><TD>SSC</TD><TD>34567</TD><TD>34566</TD><TD style='background: red'>-1</TD></TR>
</TABLE></HTML>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

BR Dushan.
Try looking for sendmail; it might be /usr/sbin/sendmail or something like that, and than you can specify the full path in the script instead of just sendmail.
The mail command is apparently not interpreting the headers as headers.
Dushan De SilvaTechnology Architect

Author

Commented:
yes thanks I found under /usr/lib/sendmail and I changed last second line as below.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
') > tmpfile2 && echo "Sending mail" && /usr/lib/sendmail -v -X tmp.log dushan_desilva@yahoo.com < tmpfile2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

But it's still giving following error and not sending email yet.
--------------------------------------------------------------------------------------------
[email_csv]$ ./mailalert.sh < mycsvfile.csv
Sending mail
WARNING: RunAsUser for MSP ignored, check group ids (egid=500, want=51)
can not chdir(/var/spool/clientmqueue/): Permission denied
Program mode requires special privileges, e.g., root or TrustedUser.
--------------------------------------------------------------------------------------------

BR Dushan.
Maybe you have to run it as root then.  My sendmail is SUID root, but it looks like yours is set up that way but is ignoring it because of some kind of security setup.  See if you can add yourself to group number 51, that looks like it might satisfy it.  Or you could try mailing something using "mail" command while doing a top or ps -ealf command to figure out what commands it is running; that might possibly help you figure out a work-around.
Dushan De SilvaTechnology Architect

Author

Commented:
yes I should run this script not as root, it's a normal user. I don't have root access.. Please advise.
Most Valuable Expert 2011
Top Expert 2016
Commented:
This code was pasted at the other question, recently deleted by the moderators.

Are you able to use PHP?
<?php // RAY_temp_dushan911.php
error_reporting(E_ALL);

// SIMULATED TEST DATA FROM THE POST AT EE
$csv = <<<EOCSV
Query,group,IBM,product
Clam,Senehasa,12345,12345
Jane,SSC,23456,23457
Mmettha,SSC,34567,34566
EOCSV;

// MAKE AN ARRAY OF LINES OF CSV DATA
$arr = explode(PHP_EOL, $csv);
// ACTIVATE THIS TO SEE THE ARRAY
// var_dump($arr);

// REMOVE THE TOP LINE FROM THE CSV
$top = $arr[0];
unset($arr[0]);

// ITERATE OVER THE REMAINING LINES
foreach ($arr as $str)
{
    $sub = explode(',', $str);
    if (trim($sub[2]) != trim($sub[3]))
    {
        echo "<br/>MISMATCH: $str";
    }
}

Open in new window

Distinguished Expert 2017
Commented:
See if you have sendmail in /usr/sbin/sendmail, /usr/lib/sendmail
this is what will be handling the outgoing email.

The mailing part is unverified.
#!/usr/bin/perl
#CSV being provided via PIPE or a redirect
my $test='';
while (<STDIN>) {
chomp();
@data=split(/,/,$_);
next if ($data[2] =~ /^[^0-9]+$/ ); #skip row if entry is not numeric
if (  ( $data[2]-$data[3] ) != 0 ) {
$test .="$data[0]  has an error: IBM: $data[2] versus Products $data[3]\n";
}

}

if (length($test)>0) {
#generate an email.
open (MAIL, "|/usr/bin/sendmail -oi -t") || die "Unable to open mailer:$!\n";

print MAIL  <<EOF
To: <recipient_EMAIL_address>
From: <sender_email_address>
Subject: Error in data

$test

EOF
;
close (MAIL);

}

Open in new window

From Googling that error message, it looks like one of these two things might work:
1.  Add the -Ac flag to the sendmail command.
2.  Make sure /usr/sbin/sendmail belongs to group 51, and is SETGID (and also SETUID);
     in other words its permissions should be like -rwsr-sr-s.
Dushan De SilvaTechnology Architect

Author

Commented:
Hi arnold,
Thanks, it gives following error message.
----------------------------------------------------------------------------------------
[perl]$ perl script2.pl < sample.csv
/home/search/dead.letter... Saved message in /home/search/dead.letter
----------------------------------------------------------------------------------------
[perl]$ cat /home/search/dead.letter
From search Sat Oct 23 12:35:24 2010
Return-Path: <search>
Received: (from search@localhost)
        by abc.pqrsearch.net (8.12.11.20060308/8.12.11/Submit) id o9NCZOdh004788;
        Sat, 23 Oct 2010 12:35:24 GMT
Date: Sat, 23 Oct 2010 12:35:24 GMT
Message-Id: <201010231235.o9NCZOdh004788@abc.bos3.pqrsearch.net>
To: <dushand.com>
From: <search.net>
Subject: Error in data

Journal  has an error: IBM: 23456 versus Products 23457
MRW  has an error: IBM: 34567 versus Products 34566

#!/usr/bin/perl
#CSV being provided via PIPE or a redirect
my $test='';
while (<STDIN>) {
chomp();
@data=split(/,/,$_);
next if ($data[2] =~ /^[^0-9]+$/ ); #skip row if entry is not numeric
if (  ( $data[2]-$data[3] ) != 0 ) {
$test .="$data[0]  has an error: IBM: $data[2] versus Products $data[3]\n";
}

}

if (length($test)>0) {
#generate an email.
open (MAIL, "|/usr/lib/sendmail -oi -t") || die "Unable to open mailer:$!\n";

print MAIL  <<EOF
To: <dushand@gmail.com>
From: <search@fastsearch.net>
Subject: Error in data

$test

EOF
;
close (MAIL);

Open in new window

Dushan De SilvaTechnology Architect

Author

Commented:
Hi Ray_Paseur,
Thanks! But I want to send these data in a email with a table.

BR Dushan.
Distinguished Expert 2017

Commented:
Create a varialbe
$sender='youremailaddress';
$recipient='recipientaddress';

and replace the entry
To: <$recipient>
From: <$sender>

Or you can escape the @ sign in the To and From by adding a backslash before the  @ (\@)
Dushan De SilvaTechnology Architect

Author

Commented:
Hi arnold,
Yeah thanks! But my problem is sending email in formatted HTML manner. (with HTML table.) when I put HTML code on the email body it just showing the same HTML code on the email not the HTML preview things, like HTML table.
http://www.experts-exchange.com/Programming/Languages/Scripting/Python/Q_26564943.html
Distinguished Expert 2017

Commented:
You have to properly define the Content-Type as part of the  message headers as well as properly include the attachment designation.
Dushan De SilvaTechnology Architect

Author

Commented:
Solution found by myself with help from given details

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial