Re: Exporting to a tab delimited file - no spaces
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: cameronkgd@xxxxxxxxxxxxxxx
- Date: Tue, 05 Oct 2004 00:27:08 +0200
Kristen Cameron wrote:
>List,
>
>One of my clients wants a script that will export 28 tables to a tab-delimited
>format. I have managed to use 'set colsep' to set the delimiter to a tab.
>This resulted in a tab-delimited file, but all the fields were padded with
>spaces to the max field width. The spaces cause problems for the software
>destined to use these files.
>
>The other options is select col1 || chr(9) || col2 ... etc., but I don't want
>to have to type each column name for 28 tables. I also know lots of ways to
>clean up the files once they are created, but I'm looking for a way to get a
>clean file right from the SQL prompt.
>
>Does anyone have a neat trick to help out with this one?
>Oracle 8.1.7 running on Windows 2000.
>
>Thanks!
>
>------------------------------------------------------
>Kristen Cameron
>
>Database Administrator | Administrateur de bases données
>Information Management Group | Groupe de gestion de l'information
>Indian and Northern Affairs Canada | Affaires Indiennes et du Nord Canada
>Yellowknife, NT X1A 2R3
>(867)669-2536 | cameronkgd@xxxxxxxxxx
>-----------------------------------------------------
>
>--
>//www.freelists.org/webpage/oracle-l
>
>
>
Kristen,
What you want is a double-action query; becomes tedious when you
have too many quotes, but this isn't your case.
Something like this should do :
set pagesize 0
set recsep off
set trimspool on
set feedback off
set termout off
spool spit_data.sql
select decode(COLUMN_ID, 1, 'spool ' || TABLE_NAME || '.dat' || chr(10) ||
'select ',
' || chr(9) || ') ||
decode(T, 'D', 'to_char(' || COLUMN_NAME || ', ''MM/DD/YYYY
HH24:MI:SS'')',
'N', 'to_char(' || COLUMN_NAME || ')',
COLUMN_NAME) ||
decode(COLUMN_ID,
LAST_ID, chr(10) || 'from ' || TABLE_NAME || ';' ||
chr(10) ||
'spool off',
'')
from (select TABLE_NAME,
COLUMN_ID,
max(COLUMN_ID) over (partition by TABLE_NAME) LAST_ID,
decode(DATA_TYPE, 'DATE', 'D',
'NUMBER', 'N',
'FLOAT', 'N',
'S') t,
COLUMN_NAME
from USER_TAB_COLUMNS
where TABLE_NAME in (list of 28 tables)
order by TABLE_NAME, COLUMN_ID)
/
spool off
set linesize 4000 -- or whatever is required
@spit_data
assuming of course you have no nasty datatype, eg LOBs or LONGs.
It will generate one .dat file per table.
--
Regards,
Stephane Faroult
RoughSea Ltd
http://www.roughsea.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
