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


But it should be:

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{
printf "<TR>"
printf "<TD>%s</TD>", $i
print "</TR>"
print "</TABLE></BODY></HTML>"
' status.csv > status.html

echo "From:abc "

echo "Subject: testing of html table using"
echo "Content-type: text/html"
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 [email protected] text


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 Aug 2, 2016 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, 2016 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, 2016 at 22:28
  • @roaima :can I change db format means closing the space between date and time in start_time
    – megha
    Aug 2, 2016 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. Aug 2, 2016 at 22:31

1 Answer 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 '<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.


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



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



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



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>


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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .