0

I tried generating an excel sheet from shell script. This must contain the result of a query.

I did get an excel sheet. But when I open that, the columns were not separated, instead all the fields of the query output appeared in the first column.

For example:

Below pasted is the result of a query. When I paste this directly in the excel without export, these will be placed in the first column of the excel.

In MS-Excel 2007, we will get "paste options", in which we can select "Use text import wizard" to separate the fields into columns in excel.

I need to implement this functionality through shell script.

Could you please help me resolve this issue?

COL1                   COL2 COL3                   
---------------------- ---- ---------------------- 
12                     a    1                      
198                    b    2                      
159                    ast  3                      
434                     rd  4                      
56                          5                      
1031                        6

Thanks, Savitha

Savitha
  • 405
  • 4
  • 15
  • 25

1 Answers1

0

Assuming you can produce a .csv file, your query can just concatenate the fields together with commas (or some other separator if you prefer) between them; something like:

select col1 ||','|| col2 ||','|| col3
from my_table
where ...

If any of the columns might contain a comma then you can enclose those in double quotes so Excel doesn't treat one value as multiple columns:

select '"'|| col1 ||'","'|| col2 ||'","'|| col3 ||'"'
from my_table
where ...

From Oracle's perspective that becomes a single result column, which means your column headers won't appear as you might want. Assuming you want them, you need to produce them separately (before the real query):

select 'COL1,COL2,COL3' from dual;

or

prompt COL1,COL2,COL3

You may also need to tweak some SQL*Plus settings, like set embed on, set linesize, set pagesize, set feedback off ... etc. to get it looking exactly right.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • I could generate the file in .csv format. In csv file, the fields are separted but I tried generating xls in the same format. It didn't work. Can't we implement this feature in xls? – Savitha Nov 16 '12 at 06:57
  • If you really need it as .xls, see if the answers on [this question](http://stackoverflow.com/q/6303472/266304) help you. There is no built-in way to generate .xls files specifically, but the XML route looks promising. You might also be able to post-process the CSV file in your shell script if that's appropriate; Google `csv2xls` (I can't vouch for any of the results!). – Alex Poole Nov 16 '12 at 11:00