i am trying to get an SQL output into an excel attachment and the other output into mail body as excel format. i am getting the excel format in the mail body, but i am not getting the excel attachment. and the output is coming in one cell in the excel. is it possible to get the excel output with Bash script. if so can anyone please help me on this.
Thanks.
Dear member,
I have a few to questions pose in response first:-
Most importantly, What have you tried so far?
There are probably many ways to achieve most tasks, so giving us an idea of your style and thoughts will help us guide you to an answer most suitable to you so you can adjust it to suit your needs in future.
We're all here to learn and getting the relevant information will help us all.
Thanks, in advance,
Robin
Given the vague request, I'm afraid I can give a vague answer: Yes, it's deemed possible if .csv (text) files are acceptable admitting the fact that EXCEL binary data are not usually written by *nix tools.
Hi, Initially i had generated CSV through SQL and then trying to convert CSV to excel. here i am getting the output in one cell or it is throwing an error as excel format corrupted. i am not having any tools to convert. so trying to convert it manually and its format is corrupted once i the file is sent to the mail.and OS is windows and Linux server version RHEL 6.3
Thanks in advance.
Data please. Errors welcome.
Then I would suggest you have something wrong, but sadly I cannot connect to your server to diagnose it today. If you actually show us what you have tried and what errors you get then we might be able to help.
Otherwise, it's like ringing a mechanic to say just "My car doesn't work" and hanging up. We now into a long list of questions trying to guess symptoms to get somewhere near to a cause.
Please can you show us what you have tried and describe & show us the output/errors.
Thanks, in advance,
Robin
att_csv="/authlistener/ProdA/service/attchment.csv"
att_op="/authlistener/ProdA/service/attachment.xml"
from="venkata.maddela@cgi.com"
to="venkata.maddela@cgi.com"
subject="test with attachment"
boundary="ZZ_/afg6432dfgkl.94531q"
body="This is the body of our email"
att_query="SELECT DECODE(SUBSTR(TRAN.iin,5,2),'25','34',mbr.mbr_no) Country_No,
DECODE(SUBSTR(TRAN.iin,5,2),'25','Hungary',mbr.name) Country_Name,
CASE TRAN.DENY_fLAG
WHEN 'N' THEN 'Approved'
ELSE 'Declined'
END Status,
SUM (TRAN.TRAN_AMOUNT) TOTAL_AMOUNT, COUNT(*) TOTALTRANSACTIONS
FROM TRAN
LEFT OUTER JOIN MBR ON substr(TRAN.iin,5,2) = (MBR.mbr_no)
LEFT OUTER JOIN CONDITION ON TRAN.CONDITION_ID = condition.condition_id
WHERE POST_DATE BETWEEN TO_DATE('20170601', 'yyyymmdd')
AND TO_DATE('20170630', 'yyyymmdd')
AND TRAN.post_ts BETWEEN TO_DATE('01 JUN 2017 00:00:00', 'DD MON YYYY HH24:MI:SS') AND TO_DATE('30 JUN 2017 23:59:59', 'DD MON YYYY HH24:MI:SS')
GROUP BY DECODE(SUBSTR(TRAN.iin,5,2),'25','34',mbr.mbr_no),
DECODE(SUBSTR(TRAN.iin,5,2),'25','Hungary',mbr.name),
TRAN.DENY_fLAG
ORDER BY Country_Name;"
CON_STRING="atlas/atlas@atlasprd"
cat > $att_op <<EOF
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author></Author>
<LastAuthor></LastAuthor>
<Created>2017-08-13T11:58:30Z</Created>
<LastSaved>2017-08-13T12:07:08Z</LastSaved>
<Company>CGI-INDIA</Company>
<Version>14.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>4680</WindowHeight>
<WindowWidth>14355</WindowWidth>
<WindowTopX>360</WindowTopX>
<WindowTopY>120</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
EOF
getResultSet()
{
#echo -e "Query :=========>"$QUERY
sqlplus -s $CON_STRING <<EOF
set term off
set echo off
set underline off
set pagesize 0
set sqlprompt "
set lines 1000 pages 1000
set linesize 1000
set colsep ","
set trimspool on
set heading on
set newpage 0
set headsep off
set feedback off
spool tmp.csv
$att_query
spool off
EOF
}
convertATTCSV2HTML()
{
[ "$#" -ne 1 ] && f_Usage && exit -1
file=$1
echo "<table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="10" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">"
head -n 1 $file | \
sed -e 's/^/<Row ss:Index="5"><Cell ss:Index="2"><Data ss:Type="String">/' -e 's/,/<\/Data><\/Cell><Cell><Data ss:Type="String">/g' -e
's/$/<\/Data><\/Cell><\/R ow>/'
tail -n +2 $file | \
sed -e 's/^/<Row ss:Index="5"><Cell ss:Index="2"><Data ss:Type="String">/' -e 's/,/<\/Data><\/Cell><Cell><Data ss:Type="String">/g' -e
's/$/<\/Data><\/Cell><\/R ow>/
'
echo "</table>"
}
#trigger SQL Script
getResultSet
#Converting CSV to HTML Table format
#convertATTCSV2HTML tmp.csv >> $att_op
cat >> $att_op <<EOF
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Selected/>
<TopRowVisible>1</TopRowVisible>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>10</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
EOF
get_mimetype(){
# warning: assumes that the passed file exists
file --mime "$1" | sed 's/.*: //'
}
sendWithAttachment()
{
declare -a attachments
attachments=( "$1" )
# Build headers
{
printf '%s\n' "From: $from
To: $to
Subject: $subject
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary=\"$boundary\"
--${boundary}
Content-Type: text/plain; charset=\"US-ASCII\"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
$body
"
for file in "${attachments[@]}"; do
[ ! -f "$file" ] && echo "Warning: attachment $file not found, skipping" >&2 && continue
mimetype=$(get_mimetype "$file")
printf '%s\n' "--${boundary}
Content-Type: $mimetype
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename=\"$file\"
"
base64 "$file"
echo
done
# print last boundary with closing --
printf '%s\n' "--${boundary}--"
} | sendmail -t -oi # one may also use -f here to set the envelope-from
}
#unix2dos $att_op $att_op
mv tmp.csv tmp.xls
sendWithAttachment tmp.xls
#rm tmp.csv
Placed XML template in the script and the output of the script should merged in XML template and the template should e mailed. but the output is corrupted.
Thanks.
---------- Post updated at 07:38 AM ---------- Previous update was at 07:22 AM ----------
yes given the same path..
Thanks.
I noticed you are renaming a .csv to .xls file in your code and trying to send it as an attachment:-
mv tmp.csv tmp.xls
sendWithAttachment tmp.xls
But that is not gonna work, because XLS is a proprietary binary format.
So I would suggest sending the attachment as CSV. It is supported by excel and it will show each columns in respective cells.
ok. But is there any other way to get the output in excel attachment.
will be grateful if you can provide some idea.
Thank you...
If you have ssconvert you can use it to convert a CSV to XLS
or
Use Perl Spreadsheet module to convert a CSV to XLS
Hi Yodha, As i had mentioned earlier. it should work only based on script not even SQL package should be used. No tools available in that environment and i dont have permissions to install as well.
Thanks.
There are no options available in bash or ksh to convert a CSV to XLS.