Visit Our UNIX and Linux User Community


Redirect the output to different worksheet in excel


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Redirect the output to different worksheet in excel
# 1  
Old 09-18-2013
Redirect the output to different worksheet in excel

Hi,

I need to redirect of a script to different worksheet in a single excel file. I have four queries in that script. Each query output needs to be placed in separate worksheet of a excel file. can someone help me to find this?
# 2  
Old 09-18-2013
If you're using CSVs to import the data into excel then there is no way to create multiple worksheets in a single file with simple redirection.

However, I have a feeling Perl gurus would say "I bet there's a module for that", as usual. Smilie
# 3  
Old 09-18-2013
For this, you really do need an XLS file, not CSV.

In order to make worksheets in an XLS file, you'll need install a perl module like Spreadsheet::WriteExcel

How to use it depends on what you want to do with it, of course. Show the text data you want to put into the spreadsheet please.
# 4  
Old 09-18-2013
First try:

Code:
#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my ($infile, $line, $worksheet, @cols, $row, $col);
my $workbook = Spreadsheet::WriteExcel->new('output.xls');

while($infile = shift)
{
        $row=0;

        $worksheet = $workbook->add_worksheet($infile); # Add a worksheet
        open(FIN, "<$infile") || die("Couldn't open $infile"); # open csv

        while(!eof(FIN)) {
                chomp($line=<FIN>);
                @cols=split(/,/, $line);

                for($col=0; $col <= $#cols; $col++) {
                        $worksheet->write($row, $col, $cols[$col]);
                }
                $row++;
        }

        close(FIN);
}

Code:
$ ./csv.pl a.csv b.csv
$ ls -l output.xls

-rw-r--r-- 1 username users 5632 Sep 18 11:36 output.xls

$

# 5  
Old 09-18-2013
is it possible using the shell script?
# 6  
Old 09-18-2013
Short answer, no.

Long answer, nnnnno. This is quite impractical to do in pure shell. XLS is a complicated, proprietary, binary, microsoft thing which was opaque to most anything for a surprisingly long time. WriteExcel was written because it was needed so badly.

Maybe you could find a utility to do so, but the utility would probably be written in perl, and demand WriteExcel or some other perl module.

Last edited by Corona688; 09-18-2013 at 05:42 PM..
This User Gave Thanks to Corona688 For This Post:
# 7  
Old 09-18-2013
I don't know about the perl scripting. Can you pls explain excatly what i need to do?

Previous Thread | Next Thread
Test Your Knowledge in Computers #861
Difficulty: Medium
Vue.js was created by Evan Ewe.
True or False?

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

Redirect script output to a file and mail the output

Hi Guys, I want to redirect the output of 3 scripts to a file and then mail the output of those three scripts. I used below but it is not working: OFILE=/home/home1/report1 echo "report1 details" > $OFILE =/home/home1/1.sh > $OFILE echo... (7 Replies)
Discussion started by: Vivekit82
7 Replies

3. Shell Programming and Scripting

script to mail monitoring output if required or redirect output to log file

Below script perfectly works, giving below mail output. BUT, I want to make the script mail only if there are any D-Defined/T-Transition/B-Broken State WPARs and also to copy the output generated during monitoring to a temporary log file, which gets cleaned up every week. Need suggestions. ... (4 Replies)
Discussion started by: aix_admin_007
4 Replies

4. Shell Programming and Scripting

Convert xlsx worksheet to separate csv

Hi All, I need a solution to convert my .xlsx file which has lot of worksheet, convert to separate csv file. Is there any shell script to do this? I can’t use excel macro to do this, since it was password locked. I need the csv output as an input to my shell script. (1 Reply)
Discussion started by: ranjancom2000
1 Replies

5. UNIX for Dummies Questions & Answers

redirect find output

I'm trying to get a list of directories with "2012" as one of the sub-directories (ex. ./TRAN/U214OU/IN/2012/03/01). I tried using find like this "find . -name 2012 -type d > list.out" but it won't write to file. What am I doing wrong or is there a better way to do this? (6 Replies)
Discussion started by: knotfinley
6 Replies

6. Shell Programming and Scripting

Qshell output to excel

Hi all, I hope this is the right topic for this issue. I made a list with directories, files, size, ... and i want to export it to a .CSV file. He does creat my .csv file that i'm asking but i can't read anything of it. It is all weird symbolics etc. Even when i make a .txt file. Any... (3 Replies)
Discussion started by: CODIII
3 Replies

7. Shell Programming and Scripting

Rename Worksheet

Hi, What is the syntax to rename excel worksheet present in testing.xls Suppose my excel name is testing.xls The worksheet name is sheet1. I want to change this worksheet name from sheet1 to TAB11. Thanks in advance. (2 Replies)
Discussion started by: debasis.mishra
2 Replies

8. Shell Programming and Scripting

output redirect

Hi i am compiling a source code by make command. i want to redirect the output of make to a file but at the same time i want to see the output in terminal. how to do this ?. please suggest your idea. thanks in advance. Saravana ---------- Post updated at 05:24 PM ----------... (2 Replies)
Discussion started by: tsaravanan
2 Replies

9. Shell Programming and Scripting

Redirect Output

Hi, I would like to list files: ls *.hdf But I would like a copy of the output directed to the screen, but also APPENDED to a text file: test.txt I have tried: ls *.hdf | tee test.txt However, that will just write over everything already existing in test.txt. How can I append the... (1 Reply)
Discussion started by: msb65
1 Replies

10. Shell Programming and Scripting

Redirect output

Hi all, I have a script which call a java program, the logging (to log file) in the program is done using log4j. However, as a safety measure, i still choose to direct standard error to another log file as follow /usr/bin/java -classpath ${classpath} -Xmx128m TestingProgram 2>>... (1 Reply)
Discussion started by: mpang_
1 Replies

Featured Tech Videos