Oracleのデータをエクセルでインポート/エクスポートする方法(ORA-01821の対処含む)

Oracleを使っているときに、テーブルのデータを一時的に保存したり、テストデータなどをインポートしたい場面があります。

そのような場合、ファイルに保存しておくとよいですが、特にエクセルの形式で保存しておくと、値の加工もできて、非常に便利なので、その方法をメモしておきます。

利用するのは、Oracleのクライアントをインストールするとデフォルトで付いてくる「SQL Developer」です。

 

データのエクスポート

最初にデータをエクスポートする方法です。

エクスポートの場合は、数クリックで完了するので、簡単です。

エクスポート対象のテーブルがあるデータベースに接続したら、対象のテーブルを右クリックして、「データのエクスポート」を選択しましょう。

ここで出力するファイルの形式を選べます。Csvやloader形式もありますが、今回は「xls」形式を選択することにします。

image

 

 

後は、ファイルを保存する場所を指定して「適用」ボタンを押すだけです。

image

 

 

ちなみに、エクスポートするレコードを選択することもできます。

上記のダイアログの上部に、「列」というタブと「WHERE」というタブがあります。

「列」タブでは出力する対象の列を絞り込むことができます。

image

 

 

「WHERE」タブでは出力する対象のレコードを絞り込むことができます。

SQLのwhere句が利用できるので、必要なレコードのみに絞るのも楽ですね。

image

 

 

データのインポート

データのインポートにもエクセルを使用できます。

上記でエクスポートしたエクセルファイルをひな形として利用すると、インポート用ファイルの作成の手間が省けて良いでしょう。

エクセルのインポート時には取り込み対象のシート名を指定できますので、複数のテーブルのデータを1つのエクセルブックにまとめたりもできますね。

早速インポートしてみましょう。

インポート対象のテーブルがあるデータベースに接続したら、対象のテーブルを右クリックして、「データのインポート」を選択します。

image

 

 

ファイルの選択ダイアログが開きますので、そこでインポートするエクセルを指定します。

すると、以下のようなデータのインポートウィザードが開きます。

最初の画面では、インポートするシートを指定します。

シートにヘッダー行を含む場合には、「ヘッダー」のチェックボックスにチェックを入れると、それを無視してくれます。

image

 

 

次の画面では、取り込む列を指定します。ここで指定した列を、次の画面で、テーブルの各カラムにマッピングすることになります。

image

 

 

次の画面では、ソースデータの各列をテーブルの各カラムにマッピングします。

image

 

 

最後の画面では、インポート内容を検証することができます。

右上部の「検証」ボタンを押すと、検証が開始されます。

全てのステータスが「SUCCESS」と表示されていることを確認して、「終了」ボタンを押しましょう。

image

 

 

注意

DATE型やTIMESTAMP型をインポートする場合には日付書式を指定します。

ファイルに保存されたデータは文字列ですので、インポート時に

to_date(’13-05-21′, ‘yyyy/mm/dd’)

のように変換されるためです。

そのため、ファイルに記述してある日付またはタイムスタンプの内容に従って、適切な日付書式を指定しましょう。

例えば、

「2013/05/13」と書いてある場合には、「yyyy/mm/dd」などと指定します。

注意2

TimeStamp型の場合は、その書式に注意する必要があります。

エクスポートする前にSQL Developerの「ツール」⇒「プレファレンス」から、「データベース」⇒「NLSパラメータ」を選択し、

タイムスタンプ書式を「YY-MM-DD HH24:MI:SS」等(XFFがない形)に変更しておけば、インポート時の書式設定で、「YY-MM-DD HH24:MI:SS」を設定すればよいです。

「FF」(秒の小数点以下)を含む書式だと、インポート時に

SQL Error: ORA-01821: date format not recognized

SQLエラー: ORA-01821: 日付書式コードが無効です

というエラーが発生してしまいます。

 



こんな記事も関係あるかも。読んでみてね。

コメントを残す

サブコンテンツ

このページの先頭へ