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

I checked it.
The problem is that he has chr(10) (newline) in his data so the output is truncated.
Using select replace(field,chr(10),'') instead of select field make the problem go away.

BTW - I also tested doing the replace on numeric fields and it comes out ok. No need to check if the field type is char.

Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Tuesday, December 24, 2002 7:48 AM

> 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.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: oraora oraora
> INET: oraoraora_at_rediffmail.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yechiel Adar
  INET: adar76_at_inter.net.il

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).