DB

各DB上でテーブルのデータをCSVファイルに出力してみた

今回は、各DB上のデータをCSVファイルに出力する方法について共有する。

前提条件

Windows端末上に、Oracle・MySQL・PostgreSQL・SQL Serverをそれぞれインストール済であること。また、salesテーブルを作成済であること。
Oracle・MySQL・PostgreSQLのインストール手順は下記記事を参照のこと。

Windows端末にMySQL、PostgreSQL、Oracleをインストールしてみた今回はいろいろな種類のデータベースを使ってみたかったので、Windows端末に、MySQL、PostgreSQL、Oracleそれぞれの...

また、SQL Serverのインストール手順は下記記事を参照のこと。

SQL Serverのデータ分析を行う環境構築を行った(1)今回は、SQL Serverのデータ分析を行う環境構築として、SQL Server・SQL Server Management Stud...

また、salesテーブルのデータは、例えばMySQLの場合、以下のデータが入っているものとする。
salesテーブルのデータ

やってみたこと

  1. Oracleの場合の実行結果
  2. MySQLの場合の実行結果
  3. PostgreSQLの場合の実行結果
  4. SQL Serverの場合の実行結果

 

Oracleの場合の実行結果

Oracleの場合の実行結果は、下記記事を参照のこと。

OracleのspoolコマンドでテーブルのデータをCSVファイルに出力してみた今回は、OracleのテーブルデータをCSVファイルに出力してみたので、共有する。sqlplusで接続し、spoolコマンドを利用すると...

MySQLの場合の実行結果

MySQLのバージョン8.0の場合は、インストールした時点から、secure_file_privのディレクトリ設定がされていて、このディレクトリ以外にファイルを出力することができない。

secure_file_privの設定値は、以下のようにして確認できる。

MySQL_secure_file_priv

また、CSVファイルの出力は、以下のように、SELECT (カラム名一覧) FROM (テーブル名) INTO OUTFILE (ファイルパス) (各種オプション)で行える。

MySQL_CSV出力
上記コマンドは、salesテーブルのデータを、secure_file_privで設定されたディレクトリ配下のCSVファイル「output_sales.csv」に、区切り文字:,(カンマ)、囲い文字:”(ダブルクォート)、改行文字:\r\nで出力する例となる。

上記コマンドで出力されたCSVファイルは、以下の通りとなる。
MySQL_CSV出力結果

なお、secure_file_privで設定されたディレクトリ以外にCSVファイルを出力しようとすると、以下のようなエラーメッセージが出力される。
MySQL_CSV出力エラー

PostgreSQLの場合の実行結果

PostgreSQLの場合、salesテーブルのデータをCSVファイルに出力するのは、以下のように、COPY (テーブル名) TO (ファイルパス) (各種オプション)で行える。

Postgresql_CSV出力
上記コマンドは、CSVファイル「D:\work\output_sales.csv」に、salesテーブルのデータを、区切り文字:,(カンマ)、囲い文字:”(ダブルクォート)で出力する例となる。

上記コマンドで出力されたCSVファイルは、以下の通りとなる。
Postgresql_CSV出力結果

SQL Serverの場合の実行結果

SQL Serverの場合は、SQL Serverをインストール時にSQLCMDを利用すると、salesテーブルのデータをCSVファイルに出力することができる。
SQLCMDコマンドは、以下の場所に配置されている。
SQLCMDコマンド

SQLCMDコマンドを利用して、salesテーブルのデータをCSVファイルに出力するのは、例えば以下のように行える。

SQLServer_CSV出力
上記コマンドは、「-S localhost -E」でローカルSQLServerにWindows認証でログインし、クエリ「D:\work\query.sql」を利用し、区切り文字「,(カンマ)」とし、出力するCSVは「D:\work\sqlserver_output_sales.csv」とし、列から余計な空白を削除し、ヘッダ行を出力しない、という意味となる。

また、上記コマンドで読み込んでおく「query.sql」の内容は以下の通り。

SQLServer_CSV出力SQL
上記SQL文は、「X件処理されました」というメッセージを非表示とし、ヘッダ行を出力し、各データを文字列変換し囲い文字を”(ダブルクォート)として出力する、という意味となる。

また、SQLCMDコマンドを利用して出力されたCSVファイルは、以下の通りとなる。
SQLServer_CSV出力結果