insert header row into .xls

Hello,

I am building an .xls file extracting info from a DB to be eventually emailed. All is good except how do I put in a header row.. like date, name of report etc. before the columns with the actual column name and data?
Thanks for any assistance.. the below is after I have signed into sqlplus

set serveroutput on size 1000000
set timing off
set feedback off

WHENEVER sqlerror EXIT failure
WHENEVER oserror EXIT failure
SET termout OFF
SET pagesize 1000
SET linesize 1600
prompt Report
spool Report

set trims on
set feed off markup html on spool on
COLUMN username heading "USERNAME" format a25;
COLUMN account_status heading "ACCT_STATUS" format a25;
COLUMN lock_date heading "LOCK_DT" format a25;
COLUMN expiry_date heading "EXPIRY_DT" format a25;
COLUMN created heading "CREATED" format a25;
COLUMN profile heading "PROFILE" format a25;
COLUMN sidba heading "SIDBA" format a25;
COLUMN remarks heading "REMARKS" format a25;
--NEW USERS

SELECT s.username, s.account_status, s.lock_date, s.expiry_date, s.created,s.profile, s.sidba, 'NEW' REMARKS
FROM sidba_users s
WHERE
s.created >=(trunc(sysdate)-90)
and
s.account_status= 'OPEN';

Are you truely making an Excel .xls file or are you fudging by creating CSV file but calling it an .xls file?

If the latter, then as you know what order the fields are coming out in, so create a single row with those names. Then

cat header-row data-rows >final_file

Actually creating the .xls.. not .csv...
Thank you for your assistance

What tool do you use to create the XLS?

Hi Porter,

Thank you for your help. Not sure I understand your question.. below is the .ksh. This works fine and produces the email, data in correct column. Just trying now to pretty it up and give the .xls sheet a header.

#!/bin/ksh

REPORT_FILE="QrtlyDBUsers.xls"
sqlplus -s <<eoj >>$LOG 2>&1
${USER}/${PASSWORD}@${SID}
set serveroutput on size 1000000
set timing off
set feedback off

WHENEVER sqlerror EXIT failure
WHENEVER oserror EXIT failure
SET termout OFF
SET pagesize 1000
SET linesize 1600
prompt $REPORT_FILE
spool $REPORT_FILE

set trims on
set feed off markup html on spool on
COLUMN username heading "USERNAME" format a25;
COLUMN account_status heading "ACCT_STATUS" format a25;
COLUMN lock_date heading "LOCK_DT" format a25;
COLUMN expiry_date heading "EXPIRY_DT" format a25;
COLUMN created heading "CREATED" format a25;
COLUMN profile heading "PROFILE" format a25;
COLUMN sidba heading "SIDBA" format a25;
--COLUMN expctd_prof heading "EXPCTD_PROFILE" format a25;
COLUMN remarks heading "REMARKS" format a25;
--NEW USERS
SELECT s.username, s.account_status, s.lock_date, s.expiry_date, s.created,s.profile, s.sidba, 'NEW' REMARKS
FROM sidba_users s
WHERE
s.created >=(trunc(sysdate)-90)
and
s.account_status= 'OPEN';
set markup html off spool off

exit;
eoj

unix2dos $REPORT_FILE | uuencode $REPORT_FILE $REPORT_FILE | mailx -s "Qtrly DB Reports" $Mail_list

Tish

Okay, you are not create an Excel .xls file, you are creating a text file that happens to have a .xls extension.

If you were truely creating an Excel .xls then "unix2dos" would play merry havoc with it.

What does "set ... markup html on ..." do?

I stand corrected, thanks for clarifying. Sure you guessed I am new to Unix.
Your previous suggestion : cat header-row data-rows >final_file
not sure I understand how I can use this.

Tish

Is the only way to put in a header row to actually create another file to concatenate it with?

Create a file with

USERNAME	ACCT_STATUS	LOCK_DT	EXPIRY_DT	CREATED	PROFILE	SIDBA	EXPCTD_PROFILE	REMARKS

in it called "header-row".

mv QrtlyDBUsers.xls data-rows
cat header-row data-rows >QrtlyDBUsers.xls

of course file names are just suggestions.

or

mv QrtlyDBUsers.xls data-rows
cat - data-rows >QrtlyDBUsers.xls <<EOF
USERNAME	ACCT_STATUS	LOCK_DT	EXPIRY_DT	CREATED	PROFILE	SIDBA	EXPCTD_PROFILE	REMARKS
EOF

Hey Porter,

Hmm well I think I have confused the group. The column names (username, etc) are good and they are appearing at the top of the column and data is lining up etc..
what I was hoping to do is add the line of 'Audit Report: 12/07/2007' before the start of the column names Username, Acct_status, etc..

Audit Report: 12/07/2007
USERNAME ACCT_STATUS LOCK_DT EXPIRY_DT CREATED PROFILE SIDBA REMARKS

Thanks for your time.

I would do it the same way

echo "This is my report generated on"  `date` | cat - data-rows >finals.xls

Thanks for your help Porter!