[SQLServer]BULK INSERTを利用したCSVファイルのインポート

SQLServerでCSVファイルをインポートする方法です。ネットでも色々記事があったのですが、データパターンをきちんと考慮したものが見つからなかったのででまとめてみます。

本記事はCSVファイルのインポート方法の解説記事になりますが、実際の業務では可能な限りTSV形式（タブ区切り）のフォーマットをお勧めします。

扱うデータにカンマやダブルクォーテーションが存在しなければ良いのですがほとんどのシステムではこれらの文字を扱っていると思います。CSVの場合はこういった文字を扱うためにエスケープの考慮が必要となります。TSVではそれらの考慮が不要であるため、多くのケースではTSVの方がシンプルになると思います。

CSVの仕様
インポート用テーブルの作成
FORMATオプションを利用したインポート
- BULK INSERTのSQLサンプル
フォーマットファイルを利用したCSVインポート

CSVの仕様

データパターンとしてはデータレコードにカンマとダブルクォーテーションが含まれる事を考慮する必要があります。

インポート用テーブルの作成

以下の３つの列を持つテーブルを例にします。idは自動採番です。

USE [demo1]
GO

CREATE TABLE [dbo].[EMPLOYEE2](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[name] [nchar](10) NOT NULL,
	日付未入力 [int] NULL
) ON [PRIMARY]
GO

FORMATオプションを利用したインポート

利用する製品がSQL Server 2017 (14.x) CTP 1.1以降のバージョンの場合、BULK INSERTのFORMATオプションでCSVがサポートされているので簡単にインポートが行なえます。

サンプルとして以下のヘッダ行を含むCSVをインポートしてみます。データ内にダブルクォーテーションとカンマを入れています。

"id","name","age"
"111","田中","10"
"222","佐""藤","20"
"333","山,本","30"

ポイントは以下の３つです。

１．データにカンマ、ダブルクォーテーションを含む場合、
　　各フィールドをダブルクォーテーションで囲む必要がある
２．フィールドをダブルクォーテーションで囲む場合、フィールドデータの
　　ダブルクォーテーションはエスケープする必要がある（「”」は「””」にする）
３．BULK INSERTのFORMATオプションを利用する場合、
　　ダブルクォーテーションでフィールドを囲むかどうかは統一しておく必要がある

BULK INSERTのSQLサンプル

本来はヘッダ行をスキップするためのオプションではありませんが、「FIRSTROW」オプションを利用しヘッダ行は無視するように指定しています。

TRUNCATE TABLE EMPLOYEE;

BULK INSERT EMPLOYEE               --インポート先のテーブル名
FROM  'C:\test\sample.csv'         --インポートするCSVファイル
WITH (
    FORMAT='CSV',
    DATAFILETYPE='char',           --データファイルタイプ
    CODEPAGE = 'RAW',              --データファイルのコードページ（RAW=変換無)
    BATCHSIZE = 10000,             --１回のトランザクションで処理するバッチサイズ
    KEEPIDENTITY,                  --IDの自動採番を利用せずデータファイルのIDを利用する
    FIRSTROW = 2,                  --ヘッダ行をスキップ
    ERRORFILE='C:\test\sample.err' --エラーが発生したレコードを出力
)

上記のSQLを呼び出すWindowsバッチサンプルです。

@echo off

REM #####################
REM 接続情報
REM #####################
set DB_SERVER=yourservername\yourinstancename
set DB_NAME=yourdbname
set DB_USER=dev
set DB_PASS=dev

REM #####################
REM BULK INSERTの実行
REM #####################
echo [開始] BULK INSERT実行
sqlcmd -S %DB_SERVER% -d %DB_NAME% -U %DB_USER% -P %DB_PASS% -i bulk.sql
echo [終了] BULK INSERT実行

フォーマットファイルを利用したCSVインポート

SQLServer2016以前の場合、FORMATオプションでCSVがサポートされていません。
完全に対応することは出来ないのですが、フォーマットファイルを利用することでCSVインポートを行ってみます。

フォーマットファイルを利用すると列ごとに区切り文字を指定するなど細かな制御が行えます。
詳細な仕様は以下公式ドキュメントを参照ください。
フォーマットファイルの作成 (SQL Server)

フォーマットファイルはXML形式、または非XML形式がありますが、可読性の高いXML形式がお勧めです。今回もXML形式で実施します。

フォーマットファイルのテンプレート生成

bcpコマンドを利用すると既存テーブルのフォーマットファイルを作成することが出来ます。
まずは以下のコマンドでテンプレートを生成します。（後でこのファイルを修正します）

bcp employee format null -f employee.xml -S yourservername -d yourdbname -U dev -P dev -c -x -t,

フォーマットファイルを利用したCSVインポートの前提事項、制限

SQLServer2016以前でCSVをインポートする場合、フォーマットファイルで無理やり対応する形になります。ある程度対応は可能ですが、CSVの仕様であるダブルクォーテーションのエスケープには対応できず前提事項と一部制限がでます。

前提事項

・CSVの各フィールドはダブルクォーテーションで囲む（ヘッダ行も含む）
・フィールドデータ内のダブルクオーテーションはエスケープしない
　（CSV仕様では本来はエスケープが必要）
・最終レコードの最後には改行コードを付与する

制限事項

上記前提事項を満たすデータであれば、データの制限は以下の１つになります。

フィールドデータに連続する「”,”」のデータを含めることは出来ない

これはこの３文字を区切り文字に利用するためです。単独のダブルクォーテーション、カンマは利用可能です。

インポートするCSVデータ

以下のデータをインポートします。フィールドデータにダブルクォーテーション、カンマを含めています。

"id","name","1"
"111","山"本","10"
"222","田,中","20"
"333","佐",藤","30"
"444","中,"田","30"

修正後のフォーマットファイル

bcpで出力したフォーマットファイルを以下のように編集します。
FIELD id=”1″は先頭のダブルクォーテーションを処理するためのダミーフィールドになります。
もともとフィールド区切り文字は「,」になっていましたが、「”,”」に変更しています。
フィールド区切り文字に「,」を指定しているとフィールドデータ内でカンマを扱えないため、区切り文字を変更し、ＣＳＶデータをダブルクォーテーションで区切ることを前提としています。
※もし扱うデータに「,」が含まれないのであればこれらの対応は不要で、単純に区切り文字に「,」を指定するだけで大丈夫です。

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <!-- 先頭のダブルクオーテーション検知用のダミーフィールド -->
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR=""" MAX_LENGTH="1"/>
  <!-- 実際のフィールド定義。ダブルクォーテーション＋カンマを区切り文字とする -->
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="","" MAX_LENGTH="21"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="","" MAX_LENGTH="20" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=""\r\n" MAX_LENGTH="12"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="2" NAME="id" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="3" NAME="name" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="4" NAME="age" xsi:type="SQLINT"/>
 </ROW>
</BCPFORMAT>

フォーマットファイルを利用したBULK INSERT

作成したフォーマットファイルを「FORMATFILE」オプションで指定します。

TRUNCATE TABLE EMPLOYEE;

BULK INSERT EMPLOYEE               --インポート先のテーブル名
FROM  'C:\test\sample.csv'         --インポートするCSVファイル
WITH (
    FORMATFILE='C:\test\employee.xml',
    DATAFILETYPE='char',           --データファイルタイプ
    CODEPAGE = 'RAW',              --データファイルのコードページ
    BATCHSIZE = 10000,             --１回のトランザクションで処理するバッチサイズ
    KEEPIDENTITY,                  --IDの自動採番を利用せずデータファイルのIDを利用する
    FIRSTROW = 2                  --コードを出力
)

以上

リンク