Sponsored Content
Top Forums Shell Programming and Scripting Shell script to spool sql query output in single excel file but in two separate work sheet Post 303045650 by RudiC on Monday 6th of April 2020 09:37:18 AM
Old 04-06-2020
I am not aware of Oracle being able to output EXCEL workbooks, i.e. binary files. What it can do, and what you already deploy, is outputting .csv style files, if need be with different field / column separators. But each .csv files is one work sheet only. So - make Oracle output two files, one for each query, and combine them reading them individually into a work book.
 

10 More Discussions You Might Find Interesting

1. Programming

Single sql query to spool to multiple files

Is there anyway to spool my select statement into spool files of max 10000 records each? eg I have a select statement that will return 45000 records. A normal spool command will output the 45000 into just one spool file. How can I make sqlplus do this? 00001 - 10000 records --- spool... (3 Replies)
Discussion started by: Leion
3 Replies

2. 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

3. 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

4. Shell Programming and Scripting

SQL Query in Shell Script output formatting

Hi All, #!/bin/ksh call_sql () { sql=$1 sqlplus -s $sqlparam_sieb <<EOF SET ECHO OFF; SET NEWPAGE NONE; SET SQLBL OFF; SET VERIFY OFF; SET LINESIZE 2000; SET... (2 Replies)
Discussion started by: Kevin Tivoli
2 Replies

5. Shell Programming and Scripting

Shell Script for copying text file to Excel Sheet

Hi, I want to write a program to copy a log file to Excel sheet. Excel sheet has four columns MethodName , Code , Description, Details and Time. I want to pick these info from text file and put it in excel sheet. here is how the text file looks - 04.17.2014 08:06:12,697... (1 Reply)
Discussion started by: hershey
1 Replies

6. 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

7. Shell Programming and Scripting

Append date to sql*plus spool (log) file in shell script

SQL*Plus version : 11.2.0.4 OS : Oracle Linux 6.5 SQL*Plus is a client application to connect to oracle database. The log file for this tool is generated via spool command as shown below. I am trying to append date ( $dateString ) to spool file as shown below. $ cat test2.sh #!/bin/bash... (4 Replies)
Discussion started by: kraljic
4 Replies

8. Shell Programming and Scripting

Shell script appending output of sql query

I am writing the following script to create the file v_out.txt. sqlplus -s /nolog << EOF CONNECT scott/tiger@orcl; whenever sqlerror exit sql.sqlcode; set newpage 0; SET PAGESIZE 0; SET ECHO OFF; SET FEEDBACK OFF; SET HEADING OFF; SET VERIFY OFF; SET LINESIZE 100; set tab off; set... (7 Replies)
Discussion started by: itzkashi
7 Replies

9. UNIX for Beginners Questions & Answers

Not able to write SQL query output in to .csv file with shell script.

I am trying to write SQL query output into a .csv file. But in the output columns are displaying in different lines instead of coming in one line. Main Code shell script: this is my code: #!/bin/bash file="db_detail.txt" . $file rm /batch/corpplan/bin/dan.csv... (6 Replies)
Discussion started by: sandeepgoli53
6 Replies

10. UNIX for Beginners Questions & Answers

Storing UNIX shell command output to a new sheet in excel

I am not finding a way to store output of unix shell script to a new spreadsheet in an existing excel file. Also the shell script that I have written should be called from a macro in excel . The shell script that I have written is as follow : git ls-files | grep '\.java$' | xargs wc -l | awk '... (1 Reply)
Discussion started by: Harshith
1 Replies
CSV(3pm)						User Contributed Perl Documentation						  CSV(3pm)

NAME
Class::CSV - Class based CSV parser/writer SYNOPSIS
use Class::CSV; my $csv = Class::CSV->parse( filename => 'test.csv', fields => [qw/item qty sub_total/] ); foreach my $line (@{$csv->lines()}) { $line->sub_total('$'. sprintf("%0.2f", $line->sub_total())); print 'Item: '. $line->item(). " ". 'Qty: '. $line->qty(). " ". 'SubTotal: '. $line->sub_total(). " "; } my $cvs_as_string = $csv->string(); $csv->print(); my $csv = Class::CSV->new( fields => [qw/userid username/], line_separator => " "; ); $csv->add_line([2063, 'testuser']); $csv->add_line({ userid => 2064, username => 'testuser2' }); DESCRIPTION
This module can be used to create objects from CSV files, or to create CSV files from objects. Text::CSV_XS is used for parsing and creating CSV file lines, so any limitations in Text::CSV_XS will of course be inherant in this module. EXPORT None by default. METHOD
CONSTRUCTOR parse the parse constructor takes a hash as its paramater, the various options that can be in this hash are detailed below. Required Options o fields - an array ref containing the list of field names to use for each row. there are some reserved words that cannot be used as field names, there is no checking done for this at the moment but it is something to be aware of. the reserved field names are as follows: "string", "set", "get". also field names cannot contain whitespace or any characters that would not be allowed in a method name. Source Options (only one of these is needed) o filename - the path of the CSV file to be opened and parsed. o filehandle - the file handle of the CSV file to be parsed. o objects - an array ref of objects (e.g. Class::DBI objects). for this to work properly the field names provided in fields needs to correspond to the field names of the objects in the array ref. o classdbi_objects - depreciated use objects instead - using classdbi_objects will still work but its advisable to update your code. Optional Options o line_separator - the line seperator to be included at the end of every line. defaulting to " " (unix carriage return). new the new constructor takes a hash as its paramater, the same options detailed in parse apply to new however no Source Options can be used. this constructor creates a blank CSV object of which lines can be added via add_line. ACCESSING lines returns an array ref containing objects of each CSV line (made via Class::Accessor). the field names given upon construction are available as accessors and can be set or get. for more information please see the notes below or the perldoc for Class::Accessor. the lines accessor is also able to be updated/retrieved in the same way as individual lines fields (examples below). Example retrieving the lines: my @lines = @{$csv->lines()}; removing the first line: pop @lines; $csv->lines(@lines); sorting the lines: @lines = sort { $a->userid() <=> $b->userid() } @lines: $csv->lines(@lines); sorting the lines (all-in-one way): $csv->lines([ sort { $a->userid() <=> $b->userid() } @{$csv->lines()} ]); Retrieving a fields value there is two ways to retrieve a fields value (as documented in Class::Accessor). firstly you can call the field name on the object and secondly you can call "get" on the object with the field name as the argument (multiple field names can be specified to retrieve an array of values). examples are below. my $value = $line->test(); OR my $value = $line->get('test'); OR my @values = $line->get(qw/test test2 test3/); Setting a fields value setting a fields value is simmilar to getting a fields value. there are two ways to set a fields value (as documented in Class::Accessor). firstly you can simply call the field name on the object with the value as the argument or secondly you can call "set" on the object with a hash of fields and their values to set (this isn't standard in Class::Accessor, i have overloaded the "set" method to allow this). examples are below. $line->test('123'); OR $line->set( test => '123' ); OR $line->set( test => '123', test2 => '456' ); Retrieving a line as a string to retrieve a line as a string simply call "string" on the object. my $string = $line->string(); new_line returns a new line object, this can be useful for to "splice" a line into lines (see example below). you can pass the values of the line as an ARRAY ref or a HASH ref. Example my $line = $csv->new_line({ userid => 123, domainname => 'splicey.com' }); my @lines = $csv->lines(); splice(@lines, 1, 0, $line); OR splice(@{$csv->lines()}, 1, 0, $csv->new_line({ userid => 123, domainname => 'splicey.com' })); add_line adds a line to the lines stack. this is mainly useful when the new constructor is used but can of course be used with any constructor. it will add a new line to the end of the lines stack. you can pass the values of the line as an ARRAY ref or a HASH ref. examples of how to use this are below. Example $csv->add_line(['house', 100000, 4]); $csv->add_line({ item => 'house', cost => 100000, bedrooms => 4 }); OUTPUT string returns the object as a string (CSV file format). print calls "print" on string (prints the CSV to STDOUT). SEE ALSO
Text::CSV_XS, Class::Accessor AUTHOR
David Radunz, <david@boxen.net> COPYRIGHT AND LICENSE
Copyright 2004 by David Radunz This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. perl v5.10.0 2007-02-08 CSV(3pm)

Featured Tech Videos

All times are GMT -4. The time now is 12:50 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy