ORACLE TECHNOLOGY NETWORK
 
 
   

Oracle Technology Network (OTN) Japan - 掲示板 » データベース(R/O) » Oracle8iデータベースの部屋(読取専用)

スレッド: spoolでcsvに吐く時varchar2に空白が付与されない様にするには

このスレッドに返信する このスレッドに返信する スレッド一覧へ スレッド一覧へ

Permlink 返信数: 26 - ページ数: 2 [ 1 2 | 次へ ] - 最新投稿 : 2003/12/13 16:57 最新投稿者: 田野@まさ - スレッド表示形式:
田野@まさ

投稿数: 36
登録日時: 02/09/11


spoolでcsvに吐く時varchar2に空白が付与されない様にするには
投稿時刻: 2003/12/05 12:11
  このスレッドに返信します… 返信

Oracle 8i(8.1.7), SQL*Plus8.1.7
srv:Windows2000 Server
clt:Windows2000 Professional, P3 500Mhz, 128MB

いつもお世話になります。
以下の様にして、select結果をCSVに吐き出すscriptをsqlplusから@コマンドで実
行させるのですが、
format a30 だと当然ですが、col1がvarchar2でも、30字以下のデータは末尾に空
白が付与されてしまいます。
回避する方法はありますでしょうか?
他社製品(例えば日立のDBP2等)だとvarchar2に空白が入らなく出来るのですが、
勿論その製品のインストが必要なので、SQL*Plusだけでやりたいのです。
宜しくお願い致します。

rem sqlplus tano/tano@hoge
set echo off
set pagesize 0
set feedback off
set linesize 1000
set trimspool on
set colsep ','

column col1 format a30
column col2 format 99999.99
column col3 format 99999999999999999999999999999999999999

alter session set nls_date_format='yyyy/mm/dd';

spool c:\spool.csv
set verify off

select col1,col2,col3 from tbl1 order by col1;

spool off
exit

koba_mac

投稿数: 1,273
登録日時: 00/05/21


RE:spoolでcsvに吐く時varchar2に空白が付与されない様にするには
投稿時刻: 2003/12/05 12:15   田野@まさ さんへの返信です。 田野@まさ さんへの返信です。
  このスレッドに返信します… 返信

set trimspool on
で解決できると思います。
お試しください。

sunhouse

投稿数: 803
登録日時: 02/04/08


RE:spoolでcsvに吐く時varchar2に空白が付与されない様にするには
投稿時刻: 2003/12/05 12:24   田野@まさ さんへの返信です。 田野@まさ さんへの返信です。
  このスレッドに返信します… 返信

select rtrim(col1)||',',||to_char(col2, 'fm99999.99)||',',||
to_char(col3, 'fm99999999999999999999999999999999999999) as col4
from tbl1 order by col1;
として試してみては?

yamataka

投稿数: 2,528
登録日時: 00/06/02


RE[1]:spoolでcsvに吐く時varchar2に空白が付与されない様にするには
投稿時刻: 2003/12/05 12:52   koba_mac さんへの返信です。 koba_mac さんへの返信です。
  このスレッドに返信します… 返信

>set trimspool on
>で解決できると思います。

trimspool は行の最後にあるスペースをtrimする設定です。
各列の最後ではありません。
それに質問者はすでにtrimspool onをしている旨を書いていますが。


で本題に戻って、今回必ず30文字分確保されるのは以下の指定をしたからです。
>column col1 format a30

ただし指定しない場合は、試したところSQL*Plusでselectすると
charやvarchar2の列は設定した列長の2倍になって表示されるようです。

よって、sunhouseさんの例のように、colsepを使用するのではなく、手動で
カンマなどを挿入するSQL文に変更するのが良いと思います。

hiromon

投稿数: 51
登録日時: 00/12/13


RE[1]:spoolでcsvに吐く時varchar2に空白が付与されない様にするには
投稿時刻: 2003/12/05 12:59   sunhouse さんへの返信です。 sunhouse さんへの返信です。
  このスレッドに返信します… 返信

>select rtrim(col1)||',',||to_char(col2, 'fm99999.99)||',',||
> to_char(col3, 'fm99999999999999999999999999999999999999) as col4
> from tbl1 order by col1;
>として試してみては?

いくつか間違いがあるようなので,下記で,一応直します。

select substr(col1,1,30)||','||to_char(col2, 'fm99999.99') ||','||
to_char(col3, 'fm99999999999999999999999999999999999999') as col4
from tbl1 order by col1;

この場合、「set colsep ','」は不要です。


hiromon

投稿数: 51
登録日時: 00/12/13


RE[2]:spoolでcsvに吐く時varchar2に空白が付与されない様にするには
投稿時刻: 2003/12/05 13:05   hiromon さんへの返信です。 hiromon さんへの返信です。
  このスレッドに返信します… 返信

念のためですが,

>column col1 format a30
という事は,31文字以降は切捨てでよかったんですよね?

切り捨てたくないなら,
>select substr(col1,1,30)||','||to_char(col2, 'fm99999.99') ||','||
をsubstrをとってください。
更に後ろのスペースをとりたいなら、sunhouseさんのようにrtrimしてください。




sunhouse

投稿数: 803
登録日時: 02/04/08


RE[2]:spoolでcsvに吐く時varchar2に空白が付与されない様にするには
投稿時刻: 2003/12/05 13:09   hiromon さんへの返信です。 hiromon さんへの返信です。
  このスレッドに返信します… 返信

>いくつか間違いがあるようなので,下記で,一応直します。

カンマが余計でしたね。(^_^);
ポイントは辞退。

田野@まさ

投稿数: 36
登録日時: 02/09/11


RE[3]:spoolでcsvに吐く時varchar2に空白が付与されない様にするには
投稿時刻: 2003/12/05 13:20   hiromon さんへの返信です。 hiromon さんへの返信です。
  このスレッドに返信します… 返信

hiromon さま、ありがとうございます。

>念のためですが,
>
>>column col1 format a30
>という事は,31文字以降は切捨てでよかったんですよね?
>
>切り捨てたくないなら,
>>select substr(col1,1,30)||','||to_char(col2, 'fm99999.99') ||','||
>をsubstrをとってください。
>更に後ろのスペースをとりたいなら、sunhouseさんのようにrtrimしてください。

例えば或る行の col1 varchar2(30) に 29BITEのデータなら29BYTE分だけ「,」
で区分してCSVに生成させたいのです。

田野@まさ

投稿数: 36
登録日時: 02/09/11


RE[2]:spoolでcsvに吐く時varchar2に空白が付与されない様にするには
投稿時刻: 2003/12/05 13:28   yamataka さんへの返信です。 yamataka さんへの返信です。
  このスレッドに返信します… 返信

yamataka さま、ありがとうございます。

>で本題に戻って、今回必ず30文字分確保されるのは以下の指定をしたからです。
>>column col1 format a30
>
>ただし指定しない場合は、試したところSQL*Plusでselectすると
>charやvarchar2の列は設定した列長の2倍になって表示されるようです。
format a を使わないと、SQL*Plusのデフォルトのカラム幅でcsvのセルに入るの
ですね。
するとデフォルトのカラム幅がcol1(varchar2)内のデータより大きいとやはり末尾
に空白が付与されてしまいます。

>よって、sunhouseさんの例のように、colsepを使用するのではなく、手動で
>カンマなどを挿入するSQL文に変更するのが良いと思います。
自分の仕事はQA担当でして、SQLを何度も変更繰り返しながら、spool流して結果を
検証して、を繰り返す必要があり、
なるべく簡単なSQL(select文の列にカンマを挿入しないで済む方法)が有難いの
です。

format a を使わない時のSQL*Plusのデフォルトのカラム幅を調整する方法はない
でしょうか?
またその設定をSQL*Plusの設定ファイルにではなくspoolスクリプト内にコマンド
で埋め込む方法はないでしょうか?

yamataka

投稿数: 2,528
登録日時: 00/06/02


RE[3]:spoolでcsvに吐く時varchar2に空白が付与されない様にするには
投稿時刻: 2003/12/05 13:45   田野@まさ さんへの返信です。 田野@まさ さんへの返信です。
  このスレッドに返信します… 返信

>format a を使わない時のSQL*Plusのデフォルトのカラム幅を調整する方法はない
>でしょうか?

多分ないでしょう。
例えできたとしても、データの列長が一定でないならば、結局colで指定した場合
と同様ですよね。


>自分の仕事はQA担当でして、SQLを何度も変更繰り返しながら、
spool流して結果を
>検証して、を繰り返す必要があり、
>なるべく簡単なSQL(select文の列にカンマを挿入しないで済む方法)が有難いの
>です。

何度もやられるならば、自分でプロシージャで作成したりすれば良いのでは?
例えば、以下のTipsなどを参考にしてはどうでしょう。

http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=69

macbeth

投稿数: 752
登録日時: 02/03/31


RE[3]:spoolでcsvに吐く時varchar2に空白が付与されない様にするには
投稿時刻: 2003/12/05 13:51   田野@まさ さんへの返信です。 田野@まさ さんへの返信です。
  このスレッドに返信します… 返信

>なるべく簡単なSQL(select文の列にカンマを挿入しないで済む方法)が有難いの
>です。
>
>format a を使わない時のSQL*Plusのデフォルトのカラム幅を調整する方法はない
>でしょうか?

COL1の桁数は31バイトあるのか、後ろスペースを削除するため
にRTRIMする必要があるのか、質問の内容を明確にされては?
CREATE TABLE文も載せると良いのでは?

>またその設定をSQL*Plusの設定ファイルにではなくspoolスクリプト内にコマンド
>で埋め込む方法はないでしょうか?

Object Browser
http://www.sint.co.jp/siob/
のような有償ソフトでCSVに落とす方法もあります。
AccessやExcelに取り込んでもCSVに落せたと思います。
最近やっていないからやり方忘れた。(^_^);

liuhanwei

投稿数: 848
登録日時: 02/12/13


RE:spoolでcsvに吐く時varchar2に空白が付与されない様にするには
投稿時刻: 2003/12/05 21:08   田野@まさ さんへの返信です。 田野@まさ さんへの返信です。
  このスレッドに返信します… 返信

列毎にSQL文を工夫すること以外に、一発のコマンドでそれ実現
できないでしょうと思います。

 寧ろ、空白込みのCSVファイルをTRIM関数を使って編集したほうが
楽ではないでしょうか?
 マクロにしろ、VBAにしろ簡単にできると思います。


田野@まさ

投稿数: 36
登録日時: 02/09/11


RE[4]:やっぱりRTRIMは必要でしょうか?
投稿時刻: 2003/12/06 10:58   macbeth さんへの返信です。 macbeth さんへの返信です。
  このスレッドに返信します… 返信

>COL1の桁数は31バイトあるのか、後ろスペースを削除するため
>にRTRIMする必要があるのか、質問の内容を明確にされては?
(25153)でも書きましたが、例えば或る行の col1 varchar2(30) に、
29BITEのデータなら29BYTE分だけ「,」
5BITEのデータなら 5BYTE分だけ「,」
で区分してCSVに生成させたいのです。
--------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaa,line1 in col2,
bbbbb,line2 in col2,
--------------------------------------------------------------------
といった感じです。
やっぱりRTRIMは必要でしょうか?

>CREATE TABLE文も載せると良いのでは?
create table tbl1(
col1 varchar2(30),

);
といった感じです。

>Object Browser
>http://www.sint.co.jp/siob/
>のような有償ソフトでCSVに落とす方法もあります。
>AccessやExcelに取り込んでもCSVに落せたと思います。
>最近やっていないからやり方忘れた。(^_^);
参考になります。thx!!!

hiromon

投稿数: 51
登録日時: 00/12/13


RE[5]:やっぱりRTRIMは必要でしょうか?
投稿時刻: 2003/12/06 14:56   田野@まさ さんへの返信です。 田野@まさ さんへの返信です。
  このスレッドに返信します… 返信

>(25153)でも書きましたが、例えば或る行の col1 varchar2(30) に、
>29BITEのデータなら29BYTE分だけ「,」
> 5BITEのデータなら 5BYTE分だけ「,」
>で区分してCSVに生成させたいのです。
>--------------------------------------------------------------------
>aaaaaaaaaaaaaaaaaaaaaaaaaaaaa,line1 in col2,
>bbbbb,line2 in col2,
>--------------------------------------------------------------------
>といった感じです。
>やっぱりRTRIMは必要でしょうか?

こんにちは。

RTRIMを使いたくない理由は何でしょうか?
使ったほうが安全だという気はしますが。。。

データの格納方法によると思います。
varchar2(30)に対して、5バイトの文字を入れると仮定した場合、
'AAAAA'
と常に後ろに空白が無い状態で格納されるならRTRIMしなくても
良いでしょう。

場合によって、
'AAAAA '
のように、後ろにスペースが入ることがある場合には、RTRIMが
必要という事になります。

両方のパターンを実際に試してみたらいかがでしょうか?


田野@まさ

投稿数: 36
登録日時: 02/09/11


目的は検証なのでRTRIMは「使っちゃ駄目」なのでした。(mv_vm)
投稿時刻: 2003/12/06 15:48   hiromon さんへの返信です。 hiromon さんへの返信です。
  このスレッドに返信します… 返信

私の書き方が間違ってました。皆様、今更ながらで誠に申し訳ありません!!!
(mv_vm)
hiromon さまの整理された文章と、自分で何をやりたかったかを比べて、あっ!と
思い出してしまいました。

>RTRIMを使いたくない理由は何でしょうか?
私の目的においては「RTRIMは使っちゃ駄目」なのでした。

何故なら私の仕事はQAで、「NULLであるべきが空白になってたり」、「文字列末尾
に空白が付与されてたり」してない事の検証なのでした。

つまり、
col1 varchar2(30) に対し、
--------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaa_,line1 in col2,
bb___,line2 in col2,
_,line3 in col2,
ccc,line4 in col2,
,line5 in col2,
--------------------------------------------------------------------
のようなデータがあればその通りに出て欲しい。
(1〜3行め結果がバグなので開発者に修正を要求)

実際は空白は無い(バグではない)のに、
--------------------------------------------------------------------
e_____________________________,line32 in col2,
--------------------------------------------------------------------
みたいに30BYTE分(あるいはSQL*Plusのデフォルト幅で)出て来られては検証にな
らない、なのでした。

とすると、やっぱりselect文に手で「,」を埋め込むしかないですか?






ウェブサイトのご使用条件 | 個人情報保護基本方針/情報保護基本方針