Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Spooling file to excel

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Spooling file to excel
# 1  
Old 09-14-2010
Spooling file to excel
Hi ,

Im spooling file from oracle to csv using shell script. Below is the code im using.. The o/p is not coming in right format.. Please help me out in this..
O/p is coming as header till batch id ,im not able to see date_stored,type,type1..

Please any one can give me some suggestion



Code:
set head on;
set feed off;
set trimspool on;
set linesize 32767;
set pagesize 10;
set echo off;;
SET VERIFY OFF
set termout on;
SET RECSEP OFF;
SPOOL /home/wdsadmin/cronjobs/DISCL/reports.csv

SELECT  NODE_ID
||','||FR_ID
||','||ENTITY_ID
||','||DOC_NAME
||','||REP_NO
||','||CLASSIFICATION
||','||STATUS
||','||volNO
||','||CREATED_DATE
||','||STATUS
||','||COUNTRY
||','||BATCH_ID
||','||DATE_STORED
||','||TYPE
||','||TYPE1
||','||DOC_DATE
  FROM   xyz
 WHERE   batch_id IN ( SELECT   MAX (batch_id) FROM xyz);

Last edited by pludi; 09-14-2010 at 01:05 PM..
# 2  
Old 09-15-2010
Quote:
Originally Posted by jkumsi
...
O/p is coming as header till batch id ,im not able to see date_stored,type,type1..
...
Code:
set head on;
set feed off;
set trimspool on;
set linesize 32767;
set pagesize 10;
set echo off;;
SET VERIFY OFF
set termout on;
SET RECSEP OFF;
SPOOL /home/wdsadmin/cronjobs/DISCL/reports.csv

SELECT  NODE_ID
||','||FR_ID
||','||ENTITY_ID
||','||DOC_NAME
||','||REP_NO
||','||CLASSIFICATION
||','||STATUS
||','||volNO
||','||CREATED_DATE
||','||STATUS
||','||COUNTRY
||','||BATCH_ID
||','||DATE_STORED
||','||TYPE
||','||TYPE1
||','||DOC_DATE
  FROM   xyz
 WHERE   batch_id IN ( SELECT   MAX (batch_id) FROM xyz);

Well, the query appears to be okay.
- Are those columns actually present in the table? Do you encounter any "ORA-" error? Did the query run successfully?
- Do the columns after BATCH_ID store NULL values? In that case you'll simply see commas after BATCH_ID.
- Do the values of BATCH_ID have newlines in them? In that case the remaining values will be pushed to the next line, which you construe as absence of data.

tyler_durden
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Spooling File to My Desktop From Back-end using shell script

Hello all, I am trying to spool a SQL query output file from back-end to desktop in a certain path but it didn't work,the query writes the output in a file in the same directory at the back-end. note : i use the same query in sql developper and file was created in the desired path fine code... (1 Reply)
Discussion started by: bmaksoud
1 Replies

2. UNIX for Beginners Questions & Answers

Script not spooling in the designated file.

Hi, I need to write a script that will run a simple select count(*) query and sends the result of that query to me via email. Here's what I already have. #!/bin/ksh ###################################################################### # File Name : counts.sh # Created : 2019/27/19... (1 Reply)
Discussion started by: clef
1 Replies

3. Shell Programming and Scripting

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... (6 Replies)
Discussion started by: dani1234
6 Replies

4. UNIX for Dummies Questions & Answers

Spooling data from the database in .csv file with boundary

Hi Guys, Another questions to the genius over here. I have spool the dataf from the database into a .csv file. But can it be possible to have all the rows and column with the boundaries..for example the .csv file which i have is as below: 20140327 BU 9A 3 20140327 SPACE 9A 3 20140327... (8 Replies)
Discussion started by: Pramod_009
8 Replies

5. Shell Programming and Scripting

Perl script to Merge contents of 2 different excel files in a single excel file

All, I have an excel sheet Excel1.xls that has some entries. I have one more excel sheet Excel2.xls that has entries only in those cells which are blank in Excel1.xls These may be in different workbooks. They are totally independent made by 2 different users. I have placed them in a... (1 Reply)
Discussion started by: Anamika08
1 Replies

6. Shell Programming and Scripting

Writing excel file using perl : Excel file formatting changed

I am trying to create a program where user can input data in certain excel cells using user interface on internet....the programming is on perl and server is unix But when i parse data into excel the formatting of sheets is turned to default and all macro coding removed. What to do...Please... (7 Replies)
Discussion started by: mud_born
7 Replies

7. Shell Programming and Scripting

Script not spooling in result file

Hi everyone and nice to meet you :) I'm having some issues with a script I'm writing. It's probably most chaotic, I'm no ksh guru, but the idea is to extract an ID with that query, spool it into a file, and read that file making the ID a variable. This has to be done for every row extracted by... (10 Replies)
Discussion started by: Arkadia
10 Replies

8. UNIX for Dummies Questions & Answers

Creating a Tar file while files are spooling

Hi I have done a search for this but couldn't find much on it. I am creating a tar file with the command below tar cvf /export/home/user/backup/*Will this is being created I have a job spooling to 5 texts files in the following directory /export/home/user/backup/STATS/ The tar files... (1 Reply)
Discussion started by: sgarvan
1 Replies

9. UNIX for Dummies Questions & Answers

Option in sql script to include column headers when spooling file to .csv format

Can anyone help me how to include COLUMN HEADER when spooling file to .CSV format through SQL statement. Thanks, Akbar (4 Replies)
Discussion started by: s1a2m3
4 Replies

10. UNIX for Dummies Questions & Answers

Spooling a log file with timestamp

Hi From shell script i am invoking sqlplus to connect to oracle database and then i spool a csv file as with output. What i want to do is to change the file name with timestamp on it so after spooling finish shell script change file name with time stamp. can someone help me to do that . Thanks... (2 Replies)
Discussion started by: ukadmin
2 Replies
Login or Register to Ask a Question