How to get flat table from SQLPlus using spool
Post date: Aug 29, 2014 8:23:15 AM
I want to get the flat comma-separated file from terminal that runs SQLPlus. Here is how I manage to do it.
- logon to any machine (14001, 25001, ...) you want and open SQLPlus
- Congratulations. Now your data is clean and ready for further analysis.
further resources:
- sed techniques can be found here: http://superuser.com/questions/241018/how-to-replace-multiple-spaces-by-one-tab
- useful suggestion on using spool and display setting: http://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus
sqlplus <user_id>_ro@<db_name>
- Set output display parameters so that the output would look nice
SQL> set colsep , -- separate columns with a comma
SQL> set trimspool on -- remove trailing blanks
SQL> set pagesize 0 -- No header rows
SQL> set echo off
SQL> set feedback off
SQL> set headsep off -- this may or may not be useful...depends on your headings.
SQL> set linesize X -- X should be the sum of the column widths
SQL> set numw X -- X should be the length you want for numbers (avoid scientific notation on IDs)
- Start recording any output into an output text file using command 'spool':
SQL> spool '/tmp/kittipat_out_data.dat';
- Now you can run any query
- When done, you will need to turn the spool off using:
SQL> spool off;
- and you will find your output ready for you in the specified directory.
- Now you will find that your output looks quite dirty, for instance, there are leading/trailing spaces. It's very convenient to use sed to replace the spaces with comma or empty character or whatever suitable. I use the sed command below to replace multiple spaces with empty character.
sed 's/ \+//g' /tmp/kittipat_out_data.dat > /tmp/kittipat_out_data_clean.dat