UNIX for Dummies Questions & Answers

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

Code:

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