space in time column causing problem in formatting html file in script

Question

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 [email protected] 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

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
  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!