各DB上でテーブルのデータをCSVファイルに出力してみた｜ITエンジニアとして経験・学習したこと

今回は、各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の場合、以下のデータが入っているものとする。

Oracleの場合の実行結果

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

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

MySQLの場合の実行結果

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

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

SHOW VARIABLES LIKE "secure_file_priv"

1	SHOW VARIABLES LIKE "secure_file_priv"

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

SELECT 'sale_date', 'product_name', 'place_name', 'sales_amount' 
UNION 
SELECT sale_date, product_name, place_name, sales_amount 
FROM world.sales 
INTO OUTFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\output_sales.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'

SELECT 'sale_date', 'product_name', 'place_name', 'sales_amount'

UNION

SELECT sale_date, product_name, place_name, sales_amount

FROM world.sales

INTO OUTFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\output_sales.csv'

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\r\n'

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

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

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

Androidロックを解除する裏ワザ「4uKey for Android」をご紹介Android端末では、以下の画像のような画面ロックパスワードを設定することができますが、このパスワードを忘れてしまうと、Android...

PostgreSQLの場合の実行結果

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

COPY sales TO 'D:\\work\\output_sales.csv' 
WITH CSV HEADER DELIMITER ',' FORCE QUOTE *

1 2	COPY sales TO 'D:\\work\\output_sales.csv' WITH CSV HEADER DELIMITER ',' FORCE QUOTE *

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

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

freelance hubを利用して10万件を超える案件情報からJava Spring案件を検索してみたfreelance hubは、レバテックフリーランスやフリエン(furien)を始めとした多くのフリーランスエージェントの案件をまとめて...

SQL Serverの場合の実行結果

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

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

cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn 
SQLCMD -S localhost -E -i d:\work\query.sql -s , -o d:\work\sqlserver_output_sales.csv -W -h -1

1 2	cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn SQLCMD -S localhost -E -i d:\work\query.sql -s , -o d:\work\sqlserver_output_sales.csv -W -h -1

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

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

set nocount on 

select 'sale_date, product_name, place_name, sales_amount' 

select 
     '"' + FORMAT(sale_date, 'yyyy/MM/dd HH:mm:ss') + '"' 
   , '"' + product_name + '"' , '"' + place_name + '"' 
   , '"' + CONVERT(varchar, sales_amount) + '"' 
from 
  dbo.sales

set nocount on

select 'sale_date, product_name, place_name, sales_amount'

select

'"' + FORMAT(sale_date, 'yyyy/MM/dd HH:mm:ss') + '"'