Visit Our UNIX and Linux User Community


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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script to generate Excel file or to SQL output data to Excel format/tabular format
# 1  
Old 09-13-2014
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.

Code:
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.

Code:
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.

Code:
#!/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
# 2  
Old 09-13-2014
Oracle

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


Code:
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.
# 3  
Old 09-13-2014
Riverstone was also using the names ajju and dani1234 to avoid infraction limits. The latter two names have been banned.
# 4  
Old 09-14-2014
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.
# 5  
Old 09-14-2014
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.
# 6  
Old 09-14-2014
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.


HTML Code:
13-09-2014 128312          nmndfm mnbdbfnd                                                                                                                                                                                                                                                                                        07-DEC-1987 PVR     AL ANNUAL LEAVE
# 7  
Old 09-14-2014
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.

Previous Thread | Next Thread
Test Your Knowledge in Computers #52
Difficulty: Easy
A magnetic disc array is an example of offline storage.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

How to export Result to Excel Tabular format from UNIX?

Hi I am working on a script in which I am firing a query on database through Unix and getting the result set. I want to export that in an excel file. I am able to do so nut the result are exported horizontally one below the other. Can anyone plss help me out in exporting the Result in Tabular... (4 Replies)
Discussion started by: Saritau3
4 Replies

2. Shell Programming and Scripting

a shell script to generate an excel sheet from a text file..

hi, i have a text file that looks like this! i want to generate an excel sheet out of it, removing all the junk data except the addresses that look like . Arrow Electrical Services Rotating Machinery, Electrical Contracting & Mining Specialists Onsite maintenance, breakdown... (8 Replies)
Discussion started by: vemkiran
8 Replies

3. Shell Programming and Scripting

Problem in formatting output of SQL query in excel sheet in shell script

Hi Guys.. Need your help to format the output of my shell script. I am using spool command to take out put in csv file. below is my code. (for example) col USERNAME for a15 col EMAIL for a30 col FULL_NAME for a20 col LAST_LOGIN for a40 col DATE_CREATED for a40 SPOOL 120.csv... (3 Replies)
Discussion started by: Agupte
3 Replies

4. Shell Programming and Scripting

script to format rows to column and export to excel

i need to write script to copy the txt file to excel. (data can be 2000+), data may not be in order ex: my name: abc age: 20 add: xyz DOB: 17-mar-2010 add1: adf add2: guioth my name: cat age: 35 DOB: 11-oct-2005 city: yeshjl add: opq DOB: 17-mar-2010 add1: atg add2: gth add3:ert ... (2 Replies)
Discussion started by: pjain
2 Replies

5. Shell Programming and Scripting

Convert array data to excel format

I need your help in changing the script I have data has below text file :> cat my_emp Employee array(0) Name : Albert No : 1234 Address: stationstraat City: Utrecht Employee array (1) Name : Kouwen No : 1256 Address: stationstraat City: Amsterdam Employee array (2) Name : Peter... (2 Replies)
Discussion started by: LinuxLearner
2 Replies

6. Shell Programming and Scripting

Help to get the Output of PL/SQL procedure In a Excel or Text File

Hi, Could anyone please guide me to get the output of the PL/SQL procedure in a Excel file or Text File... Thanks (1 Reply)
Discussion started by: funonnet
1 Replies

7. Shell Programming and Scripting

Retaining the Unix CSV format in Excel format while exporting

Hi All, I have created a Unix Shell script whch creates a *.csv file and export it to Excel. The problem i am facing is that Users wants one of the AMOUNT field in comma separted values. Example : if the Amount has the value as 3000000 User wants to be in 3,000,000 format. This Amount format... (2 Replies)
Discussion started by: rawat_me01
2 Replies

8. UNIX for Dummies Questions & Answers

Save Excel file as .txt in UNIX format

I have some files created in Excel that have to be saved as .txt files in order to load them into our accounting system. I can save the files as .txt files through Excel, but I then have to open them in TextPad and do a save as to change the Format from PC to UNIX. Is there a way to skip this step... (2 Replies)
Discussion started by: jroyalty
2 Replies

9. HP-UX

Conver Excel file to another format ( text)

I can convert a excel file to another format , for example text type ( TXT) whit anoter caracter from delimiter a rows ??? I can change the default delimiter " | " ( or space ) of cell to another caracter ( for example @@ or ## ) without change the other space ??? (6 Replies)
Discussion started by: ZINGARO
6 Replies

10. Shell Programming and Scripting

Issues in the Format in excel file generated from unix machine

Hi, I have generated a report that contains many columns and since I need ir in excel format.. I just renamed te file to excel as follows: cp vijay.txt vijay.xls I have just attached this spreadsheet in the mail and I am getting it to my mail id. But, in the output excel, the columns that... (10 Replies)
Discussion started by: Vijay06
10 Replies

Featured Tech Videos