URGENT: Read CSV file and altert details via 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 according to following criteria,
------------------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 altert should send to dushan_desilva@yahoo.com
mentioning that particular row with details.

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

BR Dushan.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013
Top Expert 2013
Commented:
ERR_ROWS=$(awk -F, '{if ( $3-$4 != 0 ) print}' file.csv 2>>error.log)
[[ ! -z "$ERR_ROWS" ]] && echo "$A" | mailx -s "Differences found in file.csv!" Dushan911@domain.tld 2>error.log
 wmp
 
Most Valuable Expert 2013
Top Expert 2013
Commented:
Sorry, typo in the second line!
[[ ! -z "$ERR_ROWS" ]] && echo "$ERR_ROWS" | mailx -s "Differences found in file.csv!" Dushan911@domain.tld 2>error.log  
 
Dushan De SilvaTechnology Architect

Author

Commented:
Thanks, but it says (and on my system mailx command also not working)
-bash: !": event not found
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Dushan De SilvaTechnology Architect

Author

Commented:
sorry it's working.. but following error shows in error.log file.
 cat error.log
./script2.sh: line 2: mailx: command not found
Dushan De SilvaTechnology Architect

Author

Commented:
and email should be well formatted like in a table with this csv fields and if there is a difference between last two column that should be in red color in the email
Dushan De SilvaTechnology Architect

Author

Commented:
thanks a lot for your quick response!!
email format will looks below.
delta.jpg
Most Valuable Expert 2013
Top Expert 2013

Commented:
Use mail instead of mailx.

And for the formatting - since I don't know which graphical tools you have available, and since I'm here for solving technical problems and don't have the time for esthetic ones - sorry.

I think for the presentation thing you should make up a new question in some suitable zone here.

wmp


Dushan De SilvaTechnology Architect

Author

Commented:
it's working with mail command , but not with mailx..
I would greatly appreciate that if you could provide/guide me how to format this email to as above table.
Most Valuable Expert 2013
Top Expert 2013

Commented:
Sorry, I'm by no means an expert for such things.

Post the question how to graphically present a CSV file in a suitable zone here, maybe Open Office or whatever tool you prefer. Don't forget to append your sample file for reference.

wmp

 
Dushan De SilvaTechnology Architect

Author

Commented:
email source's table code look likes following.
<table class=MsoNormalTable border=0 cellspacing=0 cellpadding=0 width=480
 style='width:5.0in;margin-left:4.65pt;border-collapse:collapse'>
 <tr style='height:15.0pt'>
  <td width=100 nowrap valign=bottom style='width:75.0pt;border:solid windowtext 1.0pt;
  background:#BFBFBF;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal style='line-height:115%'><b><span style='color:#1F497D'>Query
  Label<o:p></o:p></span></b></p>
  </td>
  <td width=100 nowrap valign=bottom style='width:75.0pt;border:solid windowtext 1.0pt;
  border-left:none;background:#BFBFBF;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal style='line-height:115%'><b><span style='color:#1F497D'>Query
  Group<o:p></o:p></span></b></p>
  </td>
  <td width=89 nowrap valign=bottom style='width:67.0pt;border:solid windowtext 1.0pt;
  border-left:none;background:#BFBFBF;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal style='line-height:115%'><b><span style='color:#1F497D'>ICS
  Results<o:p></o:p></span></b></p>
  </td>
  <td width=107 nowrap valign=bottom style='width:80.0pt;border:solid windowtext 1.0pt;
  border-left:none;background:#BFBFBF;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal style='line-height:115%'><b><span style='color:#1F497D'>Product
  Results<o:p></o:p></span></b></p>
  </td>
  <td width=84 nowrap valign=bottom style='width:63.0pt;border:solid windowtext 1.0pt;
  border-left:none;background:#BFBFBF;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal style='line-height:115%'><b><span style='color:#1F497D'>Results
  ¿<o:p></o:p></span></b></p>
  </td>
 </tr>
 <tr style='height:15.0pt'>
  <td width=100 nowrap valign=bottom style='width:75.0pt;border:solid windowtext 1.0pt;
  border-top:none;background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal style='line-height:115%'><span style='color:black'>Core<o:p></o:p></span></p>
  </td>
  <td width=100 nowrap valign=bottom style='width:75.0pt;border-top:none;
  border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
  background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal style='line-height:115%'><span style='color:black'>Scopus<o:p></o:p></span></p>
  </td>
  <td width=89 nowrap valign=bottom style='width:67.0pt;border-top:none;
  border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
  background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal align=right style='text-align:right;line-height:115%'><span
  style='color:black'>12345<o:p></o:p></span></p>
  </td>
  <td width=107 nowrap valign=bottom style='width:80.0pt;border-top:none;
  border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
  background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal align=right style='text-align:right;line-height:115%'><span
  style='color:black'>12345<o:p></o:p></span></p>
  </td>
  <td width=84 nowrap valign=bottom style='width:63.0pt;border-top:none;
  border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
  background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal align=right style='text-align:right;line-height:115%'><span
  style='color:black'>0<o:p></o:p></span></p>
  </td>
 </tr>
 <tr style='height:15.0pt'>
  <td width=100 nowrap valign=bottom style='width:75.0pt;border:solid windowtext 1.0pt;
  border-top:none;background:#DDD9C4;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal style='line-height:115%'><span style='color:black'>Journal<o:p></o:p></span></p>
  </td>
  <td width=100 nowrap valign=bottom style='width:75.0pt;border-top:none;
  border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
  background:#DDD9C4;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal style='line-height:115%'><span style='color:black'>ScienceDirect**<o:p></o:p></span></p>
  </td>
  <td width=89 nowrap valign=bottom style='width:67.0pt;border-top:none;
  border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
  background:#DDD9C4;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal align=right style='text-align:right;line-height:115%'><span
  style='color:black'>23456<o:p></o:p></span></p>
  </td>
  <td width=107 nowrap valign=bottom style='width:80.0pt;border-top:none;
  border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
  background:#DDD9C4;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal align=right style='text-align:right;line-height:115%'><span
  style='color:black'>23457<o:p></o:p></span></p>
  </td>
  <td width=84 nowrap valign=bottom style='width:63.0pt;border-top:none;
  border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
  background:#DA9694;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal align=right style='text-align:right;line-height:115%'><span
  style='color:black'>1<o:p></o:p></span></p>
  </td>
 </tr>
 <tr style='height:15.0pt'>
  <td width=100 nowrap valign=bottom style='width:75.0pt;border:solid windowtext 1.0pt;
  border-top:none;background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal style='line-height:115%'><span style='color:black'>MRW<o:p></o:p></span></p>
  </td>
  <td width=100 nowrap valign=bottom style='width:75.0pt;border-top:none;
  border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
  background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal style='line-height:115%'><span style='color:black'>ScienceDirect**<o:p></o:p></span></p>
  </td>
  <td width=89 nowrap valign=bottom style='width:67.0pt;border-top:none;
  border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
  background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal align=right style='text-align:right;line-height:115%'><span
  style='color:black'>34567<o:p></o:p></span></p>
  </td>
  <td width=107 nowrap valign=bottom style='width:80.0pt;border-top:none;
  border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
  background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal align=right style='text-align:right;line-height:115%'><span
  style='color:black'>34566<o:p></o:p></span></p>
  </td>
  <td width=84 nowrap valign=bottom style='width:63.0pt;border-top:none;
  border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
  background:#DA9694;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
  <p class=MsoNormal align=right style='text-align:right;line-height:115%'><span
  style='color:black'>-1<o:p></o:p></span></p>
  </td>
 </tr>
</table>

Open in new window

Dushan De SilvaTechnology Architect

Author

Commented:
Hi woolmilkporc,
No need to do formatting much, can u please give a code which will give simple HTML table and it will send as email. Then later I might able to get formatting done.

BR Dushan.
Most Valuable Expert 2013
Top Expert 2013
Commented:
Hi,
I just saw your other case here - http://www.experts-exchange.com/OS/Unix/Q_26557987.html
The script you've been given there should work. It expects your data on stdin.
So just create the script as posted in the thread above. Assuming you called it "mailalert.sh" start it using
cat file.csv | mailalert.sh
with file.csv being a file containing the data you posted in your Q.
Good luck!
wmp
Dushan De SilvaTechnology Architect

Author

Commented:
okay woolmilkporc Thanks a lot! I'm following it and trying to get solution :).
#!/usr/bin/perl --
use IO::Socket;
#####################################
#####SET THE FOLLOWING ##############
$your_domain_name = "";
$fname = "";
$path-csv-file = "";
#####################################
open(FH, "< $path-csv-file);
while(<FH>){
        my @line = split(",",$_);
        if($line[2] != $line[3])
        {
               #send the mail....
               send_mail("dushan_desilva\@yahoo.com",$_);
        }
}

sub send_mail()
{
        my ($recipient, $msg) = @_;
      my $remote = IO::Socket::INET->new(
                        Proto    => "tcp",
                        PeerAddr => "67.195.168.230",
                        PeerPort => "25",
                    )
                  or die "cannot connect to smtp port at yahoo mx";
      read_soc($remote);
      $remote->send("Helo $your_domain_name\n\r");
      read_soc($remote);
      $remote->send("mail from: $fname\@$your_domain_name\n\r");
      read_soc($remote);
      $remote->send("rcpt to: " . $recipient . "\n\r");
      read_soc($remote);
      $remote->send("data:\n\r");
      read_soc($remote);
      $remote->send($msg . "\n\r\n\r");
      my $endln = "\n\r.\n\r" ;
      $remote->send($endln);
      read_soc($remote);
      $remote->send("quit\n\r");
      read_soc($remote);
}

sub read_soc
{
      my ($remote) = @_;
      while ( <$remote> )
      {
            #print $_;
            $_ =~ /^\d{3}\s+/ and last;
            $_ =~ /^[\+\-]\w+\s+/ and last;
      }
}
1;
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