1

I am in the process of creating an automated mail where in the body of the mail data should be displayed in a table format. I did some extent generate a table format in the body of mail, but I am getting in below format

---------------------------------------------
j-id|start-time|End-time|
abc|2016-07-26|02:00:00|2016-07-26|06:00:31|
---------------------------------------------

But it should be:

---------------------------------------------
j-id|start-time|End-time|
abc|2016-07-26 02:00:00|2016-07-26 06:00:31|
---------------------------------------------

here I removed | after 2016-07-26 because 2016-07-26 02:00:00 is start_time and 2016-07-26 06:00:31 is endtime.

I am querying the data from mysql DB so I couldn't change starttime/endtime format then redirecting my output to excel file then converting it to html format and send in mail. Could you help me to get expected format output?

My code is as follows:

awk 'BEGIN{
FS=","
}
 {
printf "<TR>"
for(i=1;i<=NF;i++)
printf "<TD>%s</TD>", $i
print "</TR>"
 }
END{
print "</TABLE></BODY></HTML>"
 }
' status.csv > status.html
(

echo "From:abc "

echo "Subject: testing of html table using"
echo "Content-type: text/html"
echo
awk  'BEGIN{print "<table border="1">"} {print "<tr>";for(i=1;i<=NF;i++)print "<td>" $i"</td>";print  "</tr>"} END{print "</table>"}'  status.html

) | sendmail abc@xyz.comstrong text

status.csv

st,2016-07-28 02:00:00,2016-07-28 08:10:18,06:10:18
evergreen,2016-07-28 08:10:32,2016-07-28 09:13:00,01:02:28
Discount,2016-07-28 09:00:00,2016-07-28 19:11:07,10:11:07
GO,2016-07-28 09:00:00,2016-07-28 15:33:04,06:33:04
BF,2016-07-28 09:30:38,2016-07-28 23:43:32,14:12:54
  • 1
    Please display a few lines of status.csv – Stephen Harris Aug 2 '16 at 22:20
  • You write that you are "querying the data from mysql DB so couldn't change starttime/endtime format". Why can't you change the start and end datetime format? – roaima Aug 2 '16 at 22:27
  • @ stephen dd 8/1/2016 9:00 8/1/2016 9:04 0:04:02 ee 8/1/2016 9:00 8/1/2016 9:03 0:03:44 aa 8/1/2016 9:00 8/1/2016 9:02 0:02:52 bb 8/1/2016 9:00 8/1/2016 9:02 0:02:55 – megha Aug 2 '16 at 22:28
  • @roaima :can I change db format means closing the space between date and time in start_time – megha Aug 2 '16 at 22:31
  • 2
    You should edit the original question and add the data there. Put four spaces in front of each line to make it format cleanly. Also that's not a CSV; there's no commas. – Stephen Harris Aug 2 '16 at 22:31
1

I'm assuming the CSV file you actually have has an additional header row. If it doesn't then you can easily add an additional echo line to add the header.

I note there's an extra column of data in your CSV; it looks like a duration.

If you want to keep the duration then this is pretty easy:

(
  echo "From: abc"
  echo "Subject: testing of html table using"
  echo "Content-type: text/html"
  echo
  echo '<table border="1">'
  sed -e 's!^!<tr><td>!' -e 's!$!</td></tr>!' -e 's!,!</td><td>!g' status.csv
  echo '<table>'
) | sendmail ...

Each line of the CSV is converted into something similar to:

<tr><td>BF</td><td>2016-07-28 09:30:38</td><td>2016-07-28 23:43:32</td><td>14:12:54</td></tr>

If you don't want the last column of output then we can change the sed line to remove it:

sed  -e 's!,[^,]*$!!' -e 's!^!<tr><td>!' -e 's!$!</td></tr>!' -e 's!,!</td><td>!g' status.csv

Now that same line

<tr><td>BF</td><td>2016-07-28 09:30:38</td><td>2016-07-28 23:43:32</td></tr>

In both cases because there's no , between the time and date we keep them in the same <td>...</td> data element.

EDIT TO ADD:

Let's break down the sed into its component parts:

Firstly

s!,[^,]*$!!

This removes everything from the last comma to the end of the line. Essentially removes the "Duration" field

Next

s!^!<tr><td>!

This makes sure that each line starts with <tr><td> - basically the start of a new table row and a new data element

Then

s!$!</td></tr>!

Which makes sure the data element ends and the table row ends at the end of each line.

At this point we have a line that looks like <tr><td>csv,file,data</td><tr> so then we can simple convert all the , into </td><tr>

s!,!</td><td>!g

Small simple steps add up to do a lot of work!

  • Cool .Thanks Stephan for your help.Appreciate Its working – megha Aug 2 '16 at 23:23
  • my header is included in csv.Can I give color or bold to 1st row – megha Aug 5 '16 at 23:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.