Hi,
I need the output of sql query which is executed using a shell script to xls file format.Would CSV be acceptable? Excel also opens this natively, and it's extremely easy to make from shell. XLS on the other hand would probably need you to install a bunch of Perl modules.
Yes csv format also be acceptable.
You can even generate csv with the xls extension, which helps ensure it is saved as xls, as I recall. The rules for CSV are simple (but more than just commas):
- Double any double quotes to escape them.
- Wrap Cells containing any comma, carriage return or line feed in double quotes ("). Paranoid people double quote every cell, which is legal, just wasteful.
- Separate cells with commas (,).
- End rows with carriage return and then line feed ( \r\n or ^M^J ). In practice, I have seen it happy enough with just linefeed, but that is the spec.
Some old Access versions did not follow the rules properly, sending me to tab separated txt, also nice as tab is not very popular inside cells. If you follow these rules, you can encapsulate anything.
Can you pls provide me with an example?
Yoda: '... as long as there are no commas, double quotes or linefeeds in your cells.'
---------- Post updated at 04:28 PM ---------- Previous update was at 04:24 PM ----------
1,two,"three,
and more three
and then ""How are you, Sir?""
ok enough",4,5.555\r
2,four,six,8,11.1110\r
(EOF)
I totally agree. As long as there are no other commas in the field value itself, this method will work. Or else you have to use another symbol for distinguishing the fields.
Here is an example:
sqlplus -s user/password@instance << EOF
set echo off head off feed off pagesize 0 trimspool on linesize 1000 colsep ,
spool sqlout.csv
select 'scott', 'tiger', sysdate from dual;
spool off
exit
EOF
$ cat sqlout.csv
scott,tiger,17-SEP-13
I don't understand what you are telling?
Which part don't you understand!
Sorry its working fine. thank you very much 