Re: Re: Migrate table data to CSV file - Urgent help !!!
Thanx Guys. I used a combination of repalce/translate to remove the unwanted characters.it worked out. thanx everybody. Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: oraora oraora INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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 <[EMAIL PROTECTED]>
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)
> fromuser_tab_columns
> where table_name = upper('&1')
> order by column_id
> /
> prompt prompt )
> prompt prompt BEGINDATA
>
> prompt select
> select lower(column_name)||'||chr(44)||'
> fromuser_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)
> fromuser_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,B,C,D ,,GGG
> the at the end of D is new line character .hope so.
> when this record gets written to CSV file , it is like this :
> AAA,B,C,D
> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: Migrate table data to CSV file - Urgent help !!!
If I understand your question correctly:
If you are on Unix, or have the sed utility available. then you can do
something like the following. Assume X is the character to eliminate.
sed 's/X//g' < CSV_file > temp_file
verify temp_file is OK, then replace CSV_file with temp_file. Perl has
syntax similar to this.
> -Original Message-
> From: oraora oraora [mailto:[EMAIL PROTECTED]]
> Sent: Monday, December 23, 2002 11:49 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Migrate table data to CSV file - Urgent help !!!
>
>
> 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)
> fromuser_tab_columns
> where table_name = upper('&1')
> order by column_id
> /
> prompt prompt )
> prompt prompt BEGINDATA
>
> prompt select
> select lower(column_name)||'||chr(44)||'
> fromuser_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)
> fromuser_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,B,C,D ,,GGG
> the at the end of D is new line character .hope so.
> when this record gets written to CSV file , it is like this :
> AAA,B,C,D
> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Stephen Lee
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
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)
> fromuser_tab_columns
> where table_name = upper('&1')
> order by column_id
> /
> prompt prompt )
> prompt prompt BEGINDATA
>
> prompt select
> select lower(column_name)||'||chr(44)||'
> fromuser_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)
> fromuser_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,B,C,D ,,GGG
> the at the end of D is new line character .hope so.
> when this record gets written to CSV file , it is like this :
> AAA,B,C,D
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Migrate table data to CSV file - Urgent help !!!
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)
fromuser_tab_columns
where table_name = upper('&1')
order by column_id
/
prompt prompt )
prompt prompt BEGINDATA
prompt select
select lower(column_name)||'||chr(44)||'
fromuser_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)
fromuser_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,B,C,D ,,GGG
the at the end of D is new line character .hope so.
when this record gets written to CSV file , it is like this :
AAA,B,C,D
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
