Send email with attachment in form of excel in unix

Shell Programming and Scripting
1

Hi,

I have a shell script which send email with an attachment in the form of an email. However, the when I open the attachment, all the data comes in one column. How do I format the data in the excel sheet while sending the email?

Thanks

2

I have writed a perl script implement the similar function as you do, it works normally, how do you implement it?

3

Hi,

The following in the code which I am using :

#!/bin/ksh
export ORACLE_HOME=/oracle/product/current
export PATH=$PATH:$ORACLE_HOME/bin

sqlplus -s schema/pswd@db <<EOF
set pagesize 200
set linesize 200
column Name format a25
column New_DeptName format a25
column Old_DeptName format a25
column New_Manager format a25
column Old_Manager format a25

spool $test.xls

SELECT a.first_name || ' ' || a.last_name Name, b.DEPTNAME New_DeptName ,
a.DEPARTMENT_NAME Old_DeptName, Get_New_Manager_Name(b.CS_DEPT_MGR_ID) New_Manager,
Get_Old_Manager_Name (a.manager_id) Old_Manager
FROM table1 a, table2 b,table3 c
WHERE a.emplid = c.emplid
AND a.EMPLID=b.emplid
AND b.EMPL_STATUS = 'A'
AND a.DEPARTMENT_ID <> b.deptid
order by Consultant_Name;
spool off
exit;
EOF

havingRows=`grep -c "no rows selected" $test.xls`;

if [ "$havingRows" -lt 1 ]; then
#mailx -s "this is a test" test@test.com <$test.txt
ux2dos $test.xls | uuencode test.xls | mailx -m -s "This is a test" test@test.com
fi

4

Use spool+oracle can only generate a CSV file,it's not a real excel file

5

Even in .csv file i am facing the problem. Can u please tell me how to resolve it ?

6

If you want to export a 'real' excel file, I think the better way is use perl script.
and remenber include the Spreadsheet:WriteExcel mod.

7

Actually I need to use a .sh file only. And csv file is good enough for me. But even in csv the data is not coming up properly. Can you please suggest and solutions for this ?

8

maybe you need to add more delimitaion on your sql, may like that:
SELECT a.first_name || ' ' || a.last_name Name || ' ' || b.DEPTNAME New_DeptName || ' ' || a.DEPARTMENT_NAME Old_DeptName || ' ' || Get_New_Manager_Name(b.CS_DEPT_MGR_ID) New_Manager || ' ' ||
Get_Old_Manager_Name (a.manager_id) Old_Manager
FROM table1 a, table2 b,table3 c
WHERE a.emplid = c.emplid
AND a.EMPLID=b.emplid
AND b.EMPL_STATUS = 'A'
AND a.DEPARTMENT_ID <> b.deptid
order by Consultant_Name;

9

Thanks for your time! I have modified my script as follows and the excel attached to the email is now coming up in the correct form...

#!/bin/ksh
export ORACLE_HOME=/oracle/product/current
export PATH=$PATH:$ORACLE_HOME/bin

sqlplus -s schema/pswd@db <<EOF
set pagesize 50000
set linesize 200
set feed off markup html on spool on
column Name format a25
column New_DeptName format a25
column Old_DeptName format a25
column New_Manager format a25
column Old_Manager format a25

spool $test.xls

SELECT a.first_name || ' ' || a.last_name Name, b.DEPTNAME New_DeptName ,
a.DEPARTMENT_NAME Old_DeptName, Get_New_Manager_Name(b.CS_DEPT_MGR_ID) New_Manager,
Get_Old_Manager_Name (a.manager_id) Old_Manager
FROM table1 a, table2 b,table3 c
WHERE a.emplid = c.emplid
AND a.EMPLID=b.emplid
AND b.EMPL_STATUS = 'A'
AND a.DEPARTMENT_ID <> b.deptid
order by Consultant_Name;
spool off
set markup html off spool off
exit;
EOF

havingRows=`grep -c "no rows selected" $test.xls`;

if [ "$havingRows" -lt 1 ]; then
#mailx -s "this is a test" test@test.com <$test.xls
ux2dos $test.xls | uuencode test.xls | mailx -m -s "This is a test" test@test.com
fi