If you want to produce a csv file from a SQL select query in SQL*Plus use the following syntax.
This will produce a csv formatted file. Straight to excel, change csv to xls but bear in mind two potential issues (a) row limit in Excel, and (b) leading zeros in number fields will be lost in excel.
The leading zeros can be resolved if you instead spool the file as a txt as you will be forced to use excels conversion wizard to open the file.
I would have to describe this as a hack rather than anything else as the convert function wouldn't work for me and date() is not supported, i.e. you can't use "SELECT date(timestamp_field) from db"; to extract only the date element; whereas you can use year(timestamp_field) or month(timestamp_field).
I needed to filter records based on the date, to extract rows from the table where the date was equal to today. The date field in the table was a datetime column. My workaround was to use the left function and extract the ten characters of the date which I could then compare to today as in the following.
$query = "SELECT title, contents FROM articles WHERE left(timestamp_field,10) = '$date'";
This worked. I don't think it's the elegant solution, alternatives welcomed.
© Eriginal Ltd 2011, all rights reserved