Adding a blank line in between two O/Ps in tabular format which gets received over email
Hi Guys,
I am stuck in between and seeking help here.
Requirement: A script that will run every morning which will connect to Mysql database and run the query to inform us about the holidays (it will also check if there were any holidays during last 2 business days). So the three queries are required to run.
for now, I have already written the script which sends the output over email in tabular format, however, I am unable to provide newline in between the two tabular outputs. Following is the script so far,
Code:
#!/bin/bash
mysql test -e "select c.CenterID,a.MIC,c.ExchangeName,a.CountryCode,a.EventDate,a.EventDayOfWeek,b.eventName from exch_calendar_event a left join exch_calendar_eventName b on a.EventID=b.ID left join exch_ca lendar_mic c on a.MIC=c.MIC where EventDate=DATE_FORMAT(20170811,'%Y%m%d') order by a.MIC;" | tr "\t" "~" > file.tmp
if [ -s file.tmp ]
then
echo "<table border=1>" > mail.txt
while read -r LINE
do
LINE=$(echo $LINE|sed 's/~/<\/td><td>/g')
echo "<tr><td>$LINE</td></tr>" >> mail.txt
done < file.tmp
echo "</table>" >> mail.txt
fi
mysql test -e "select c.CenterID,a.MIC,c.ExchangeName,a.CountryCode,a.EventDate,a.EventDayOfWeek,b.eventName from exch_calendar_event a left join exch_calendar_eventName b on a.EventID=b.ID left join exch_ca lendar_mic c on a.MIC=c.MIC where EventDate=DATE_FORMAT(20170831,'%Y%m%d') order by a.MIC;" | tr "\t" "~" >> file1.tmp
if [ -s file1.tmp ]
then
echo "<table border=1>" >> mail1.txt
while read -r LINE
do
LINE=$(echo $LINE|sed 's/~/<\/td><td>/g')
echo "<tr><td>$LINE</td></tr>" >> mail1.txt
done < file1.tmp
echo "</table>" >> mail1.txt
fi
cat header1 mail.txt mail1.txt footer | sendmail -t
rm -f file.tmp mail.txt file1.tmp mail1.txt
I am receiving the desired output, however, I need some blanklines to be separated from 2 query outputs.
Thanks
---------- Post updated at 05:11 AM ---------- Previous update was at 04:57 AM ----------
Ok, now it is sorted out. I have used the <br> tag.
You might consider using "plain text" as your mail format instead of HTML. This will make your mails smaller and less dangerous to handle (all the fancy Java-, Javascript- and whatnot-stuff used to install things you might not want to have installed will not work on plain text).
Increase efficiency.
Consider redirection in larger portions.
Code:
if [ -s file.tmp ]
then
echo "<table border=1>" > mail.txt
while read -r LINE
do
LINE=$(echo "$LINE"|sed 's/~/<\/td><td>/g')
echo "<tr><td>$LINE</td></tr>"
done < file.tmp >> mail.txt
echo "</table>" >> mail.txt
fi
Or even better
Code:
if [ -s file.tmp ]
then
{
echo "<table border=1>"
while read -r LINE
do
LINE=$(echo "$LINE"|sed 's/~/<\/td><td>/g')
echo "<tr><td>$LINE</td></tr>"
done < file.tmp
echo "</table>"
} > mail.txt
fi
Maybe you can save the loop and do everything in sed using its built-in loop?
Code:
if [ -s file.tmp ]
then
{
echo "<table border=1>"
sed '
s#~#</td><td>#g
s#^#<tr><td>#; s#$#</td></tr>#
'
echo "</table>"
} < file.tmp > mail.txt
fi
This User Gave Thanks to MadeInGermany For This Post:
Once we start optimizing / improving the script, why not put everything together into one single loop:
Code:
DT=(20170811 20170831)
for i in 0 1
do echo "<table border=1>"
mysql test -e "select c.CenterID,a.MIC,c.ExchangeName,a.CountryCode,a.EventDate,a.EventDayOfWeek,b.eventName from exch_calendar_event a left join exch_calendar_eventName b on a.EventID=b.ID left join exch_calendar_mic c on a.MIC=c.MIC where EventDate=DATE_FORMAT(${DT[$i]},'%Y%m%d') order by a.MIC;" |
while read LINE
do echo "<tr><td>${LINE// /<\/td><td>}</td></tr>"
done
echo "</table><br>"
done | sendmail -t
Yes, looping this will be good, however, in the output I am getting one extra <\/td> after every entry. O/p in the email attached: Below is the code:
Code:
#!/bin/bash
DT=(20170811 20170831)
for i in 0 1
do echo "<table border=1>"
mysql test -e "select c.CenterID,a.MIC,c.ExchangeName,a.CountryCode,a.EventDate,a.EventDayOfWeek,b.eventName from exch_calendar_event a left join exch_calendar_eventName b on a.EventID=b.ID left join exch_calendar_mic c on a.MIC=c.MIC where EventDate=DATE_FORMAT(${DT[$i]},'%Y%m%d') order by a.MIC;" |
while read LINE
do echo "<tr><td>${LINE// /<\/td><td>}</td></tr>"
done
echo "</table><br>"
done >> mail.txt
cat header1 mail.txt footer | sendmail -t
Also, I could not understand the ${LINE// /<\/td><td>} part. Is it replacing the tab with </td><td>.
Please let me know. Once the output is sorted out, I need to optimize the script to run everyday which will fetch the data for today, t-1 and t-2 (provided all are business days).
For eg: If today is Tuesday, then the query will be executed 3 times for today, Monday, friday and send the output (if any) over email. No blank emails required.
Currently, I am just checking the output format with hardcoding the date, however, above is the overall requirement of the script.
Hello Guys,
developing a monitoring shell script to have my queue depth count over mail .
here is the sample input file to the script which has all the queue names 1 : DISPLAY QUEUE(*) WHERE (CURDEPTH GT 0)
ZFC8409: Display Queue details.
QUEUE(QL.GVR.ATA.CACHE.01) ... (10 Replies)
Dears Please support
I have out put in text file and look like below
fixed inquiries - Click on MAX suffix http://server:port/app
User Details http://server:port/app
Audit User Detail Action hhttp://server:port/app
fixed inquiries - Click on MAX suffix http://server:port/app
User Details ... (13 Replies)
Hi ,
i am generating some data by firing sql query with connecting to the database by my solaris box.
The below one should be the header line of my excel ,here its coming in separate row.
TO_CHAR(C. CURR_EMP_NO
---------- ---------------
LST_NM... (6 Replies)
Hi
I want to display the following input data into the tabular format as shown in the output.
Input.txt:
Following jobs are in pending state for more than 10 minutes:
JOB_ID JOB_SUBMIT_ID
MAHAR 784308
PUNJA 109367
Following jobs are running for longer time:
JOB_ID... (1 Reply)
I use the following as part of a script to correct for a faulty hostname file.
# get the domain name
read -r thehostname < /etc/hostname
dom="$(echo $thehostname | cut -d'.' -f2)"
numchar=${#dom}
if
then
echo "It appears as though the hostname is not correctly set."
echo "Hostname has... (5 Replies)
Hello...
I have a file which contain certain number of records.
I want to generate another file from this file which will contain 1st line as a blank line & after every 5 lines one blank line will be inserted. How to achieve this through shell scripting?
Thanks... (5 Replies)
the following range matching works great but i wish to add a blank line after each range result set... which i've tried and researched to no avail
MY INPUT DATA:
CURRENT CODE I'M USING:
sed -n '/*$/,/;/p' $INPUT_FILE
RESULTS I'M GETTING:
RESULT I looking to... (5 Replies)
Hello friends,
I have a C source code containing sql statements. I use the following sed command to print all the sql blocks in the source code....
sed -n "/exec sql/,/;/p" Sample.cpp
The above sed command will print the sql blocks based on the pattern "exec sql" & ";"... (2 Replies)
I followed the egrep example given in the thread "parse text or complex grep ?". It is exactly what I need...except... how do I insert a blank line after the second line? My exact command is:
egrep 'patt1|patt2' filename
the result is:
patt1
patt2
patt1
patt2
and so on. I would... (2 Replies)
08-30-2017
