Script to generate Excel file or to SQL output data to Excel format/tabular format

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
--------------------------------------------------------------------------------
FST_NM
--------------------------------------------------------------------------------
DOJ_EMp QVD_CD PLC_CD
----------- -------- --------
DESC_TXT
--------------------------------------------------------------------------------
13-09-2014 34653436
John
Sendrie

TO_CHAR(C. CURR_EMP_NO
---------- ---------------
LST_NM
--------------------------------------------------------------------------------
FST_NM
--------------------------------------------------------------------------------
DOJ_EMp QVD_CD PLC_CD
----------- -------- --------
DESC_TXT
--------------------------------------------------------------------------------

Here is the data, which should start from the second row and fill the data for each employee in a associated row but here also data is coming in a separate row, like have n number of rows.

13-09-2014	34653436	John sendrie	23-APR-2007	MCH1	Hospitalised (Continous hospitalised 1 month or more and long Medical)

13-09-2014	4736543	michal thomas	16-DEC-2008	SO	MPCHG1	PCHG1 (Continuous PVr excess - more than 180 days)

Expected foramat of data is tabular/excel format with Fixed header.

can anyone help me to generate the excel file by script.

---------- Post updated at 03:08 PM ---------- Previous update was at 12:42 PM ----------

Below is the script which is actually contain nothing only the query.

Operating system :SUNOS/Solaris
Database : oracle

Current script giving me the correct but inform of data , not inform of record/table/excel.

#!/bin/ksh
sqlplus -s /nolog << EOF 
CONNECT test/test@IP

alter session set nls_date_format= 'DD-MON-YYYY';
SELECT TO_CHAR(C.PVR_ACY_START_DTM_UTC+8/24,'DD-MM-YYYY'),D.CURR_EMP_NO,D.LST_NM,D.FST_NM,D.DOJ_EMP,C.VTP_CD,A.VPA_CD,B.DESC_TXT FROM HFG_ACYS A,VMX_USER.JDS_EMP_PLX_TYPES B,EMP E,EMP1 D
And C.Dtd_asdb_Start_Dtm_Utc+8/24 <= Sysdate + 1
(fdmf_ACY_END_DTM_UTC+8/24,'DD-MM-YYYY');

end;
/
EOF

You can create tab separated files from within sqlplus using the spool functionality e.g.:

sqlplus -s ${YOUR CONNECTION NAME}

set linesize 500
set trimspool on
set verify off
set termout off
set echo off
set feedback off
set heading off
set pagesize 0

DEFINE DELIMITER='${TAB_SPACE}'
spool filename.tsv
<YOUR QUERY GOES HERE>
spool off;
exit;

The tsv file can be opened in excel as normal.

Riverstone was also using the names ajju and dani1234 to avoid infraction limits. The latter two names have been banned.

Mr.Don,

Instead of simply banning ,cant you have courtesy to ask what is the reason for having multiple Id ?

---------- Post updated 09-14-14 at 07:29 AM ---------- Previous update was 09-13-14 at 11:56 PM ----------

Hi Colshine,
tried your steps , but data is coming in flat file with spaces,expecting the data in excel format or with comma seperated values.

The spaces are actually tabs - excel recognises tabs as a valid delimiter and will use them to split the data into columns.

To create a csv file you just need to change the DEFINE DELIMITER parameter to comma and update the filename to csv.

The rest is fine as it is.

Below is the data which iam gettting tab seperated but in excel when opening the data in excel it not able to split into columns and giving me the data in a single coumn.

13-09-2014 128312          nmndfm mnbdbfnd                                                                                                                                                                                                                                                                                        07-DEC-1987 PVR     AL ANNUAL LEAVE

Google instructions on how to import a tab delimited file into excel - you just need to tell excel the type of file and it will do the rest.