Re: Migrate table data to CSV file - Urgent help !!!

oraora oraora wrote:
>
> Guys,
>
> i want to export table data into a CSV file.
> i am using the script below to do the same.
> ----------------------------------------------------------
> set wrap off
> set linesize 2000
> set feedback off
> set pagesize 0
> set verify off
> set termout off
>
> spool ytmpy.sql
>
> prompt prompt LOAD DATA
> prompt prompt INFILE *
> prompt prompt INTO TABLE &1
> prompt prompt REPLACE
> prompt prompt FIELDS TERMINATED BY ','
> prompt prompt (
> select 'prompt ' || decode(column_id,1,'',',') || lower(column_name)
> from user_tab_columns
> where table_name = upper('&1')
> order by column_id
> /
> prompt prompt )
> prompt prompt BEGINDATA
>
> prompt select
> select lower(column_name)||'||chr(44)||'
> from user_tab_columns
> where table_name = upper('&1') and
> column_id != (select max(column_id) from user_tab_columns where
> table_name = upper('&1'))
> order by column_id
> /
> select lower(column_name)
> from user_tab_columns
> where table_name = upper('&1') and
> column_id = (select max(column_id) from user_tab_columns where
> table_name = upper('&1'))
> order by column_id
> /
> prompt from &1
> prompt /
>
> spool off
>
> set termout on
>
> @ytmpy.sql
> exit
> -----------------------------------------------------------------
> things work fine.
> for example , i have a record as below (fields seperated by , ) :
> AAA,BBBBB,CCCCC,DDDDDEEEE,FFFF,GGG
> the  at the end of DDDDDEEEE is new line character .....hope so.
> when this record gets written to CSV file , it is like this :
> AAA,BBBBB,CCCCC,DDDDDEEEE
> you can see the data after  is truncated.
> and the records after this without  are written properly.
> problem occurs when there is  in a record.
> how to get rid of this ?
>
> kindly help me plzzz. This is quite urgent.
>
> TIA.
> Jp.
>

Jp,

  RTF "Utilities" M. You may specify with SQL*Loader that _records_ are terminated by a special character, in which case it ignores line breaks. Alternatively you can use replace() (or translate(), I always have to check which is which) to get rid of undesirable chr(10) characters.

-- 
HTH,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).