SQL Server で BULK INSERT を使って CSV を取り込む方法と注意点

CSV ファイルを SQL Server にインポート

アプリケーションを開発していると、CSV ファイルから SQL Server にデータを取り込みたい時があると思います。

ここでは CSV ファイルを BULK INSERT を使って SQL Server のテーブルにインポートする一番シンプルな方法をご紹介します。

BULK INSERT とは
BULK INSERT の基本構文
BULK INSERT を実行して結果を確認する
IDENTITY 列の注意点

BULK INSERT とは

BULK INSERT は、テキストファイルのデータを、既存のテーブルにまとめて取り込むための T-SQL コマンドです。

CSV ファイルや TSV ファイルなどを一括で取り込みたい時に便利です。

SQL Server がファイルを直接読み込む仕組みなので、通常の INSERT 文を繰り返すよりも高速に大量データを取り込むことができます。

今回は、テーブルと同じ構造・同じ列順の CSV ファイルを取り込む方法をご紹介します。

BULK INSERT の基本構文

CSV ファイルのデータをシンプルに SQL Server のテーブル取り込む BULK INSERT の基本的な構文は次の通りです。

BULK INSERT テーブル名
FROM 'CSVファイルへのパスとファイル名'
WITH (
  FORMAT = 'CSV', 
  -- ROWTERMINATOR = '\n', 
  CODEPAGE = '65001', -- UTF-8, Shift_JIS:932 
  FIRSTROW = 2 -- ヘッダー行あり
);

まず、BULK INSERT のキーワードに続けて、データを取り込むテーブル名を指定します。

次に、FROM に続けて、取り込みたい CSV ファイルへのパスとファイル名を指定します。

このパスは、SQL Server が動いているサーバー上の場所なので、ファイルをサーバー側に置くか、共有フォルダを使うようにしてください。また、SQL Server のサービスアカウントに、そのフォルダの読み取り権限が必要です。

そして WITH の中で、データを取り込むときのオプションを設定します。

BULK INSERT 文には、いろいろオプションがありますが、テーブルと同じ構造の CSV ファイルをシンプルに取り込む場合によく使うのは、次の4つです。

まず、FORMAT に CSV を指定します。これは SQL Server 2017 以降で使えるオプションで、値がダブルクォートで囲まれていたり、ダブルクォートが２つ続いてエスケープされているような場合でも、正しく処理してくれます。

SQL Server 2017 より前のバージョンでは、FORMAT の代わりに FIELDTERMINATOR = ',' を指定してください。データの中にカンマがなく、値がダブルクォートで囲まれていない場合はこれで問題なく取り込めます。

ROWTERMINATOR には、CSV ファイルに使われている改行コードを指定します。

行末が LF（ラインフィード）だけの時は '\n' を指定してください。

CR+LF の時は ROWTERMINATOR オプションを指定しなくて大丈夫です。今回は改行コードが CR+LF のファイルを使うので、コメントアウトしています。

CODEPAGE には、CSV ファイルが保存されているエンコーディングのコードページを指定します。

今回の CSV ファイルは UTF-8 で保存されているので、CODEPAGE に UTF-8 を表す 65001 を指定します。

もし、Shift_JIS で保存されたファイルを取り込む場合は、932 を指定してください。

最後に、CSVファイルにヘッダー行がある場合は FIRSTROW オプションに 2 を指定します。

ヘッダー行がない場合は、省略して大丈夫です。

BULK INSERT は、CSV の列をテーブルの列の順番に合わせて読み込みます。そのため、CSV ファイルとテーブルの列の順番は同じにしておく必要があります。

列順が異なる場合は、FORMATFILE というオプションを使って柔軟に対応することもできますが、実務では一時テーブルに一度取り込んで整える方法の方が管理しやすく、よく使われます。

今回は、テーブルと同じ構造の CSV ファイルを使って進めます。

BULK INSERT を実行して結果を確認する

取り込む CSV のサンプルファイル

こちらが、今回取り込む CSV ファイルの内容です。

StudentID,FirstName,LastName,Birthday,Remarks
3,陽翔,佐藤,2014-04-15,特になし
4,葵,鈴木,2014-12-30,"数学, 英語が得意"
5,結衣,高橋,2015-07-07,
6,蓮,田中,2014-03-12,"部活は""サッカー"", 放課後は塾"

ヘッダー行がある CSV ファイルで、StudentID, FirstName, LastName, Birthday, Remarks の 5 列になっています。

Remarks のデータで、カンマが含まれている値はダブルクォーテーションで囲まれています。

さらに、値の中にあるダブルクォーテーションは、ダブルクォーテーションを２つ続けてエスケープされています。

このファイルを、ファイル名は Students.csv として、 C ドライブの Test フォルダに保存してあります。

取り込み先のテーブルを作成する

次に、この CSVファイルのデータを取り込むためのテーブルを作成します。

こちらが CREATE TABLE 文です。

CREATE TABLE dbo.Students (
    StudentID INT NOT NULL PRIMARY KEY,
    FirstName NVARCHAR(50) NULL,
    LastName  NVARCHAR(50) NULL,
    Birthday  DATE NULL,
    Remarks NVARCHAR(MAX) NULL
);

カラムは、取り込む CSV ファイルと同じ順番になっています。

これを実行して、Students を作成しておきます。

BULK INSERT を実行して CSV を取り込む

それでは次の BULK INSERT 文を実行して、先ほどの Students.csv ファイルを dbo.Students テーブルに取り込んでみます。

BULK INSERT dbo.Students
FROM 'C:\Test\Students.csv'
WITH (
    FORMAT = 'CSV',
    CODEPAGE = '65001', -- UTF-8
    FIRSTROW = 2
);

[実行結果]
CSV ファイルを BULK INSERT を使ってインポートする 3

Students.csv ファイルのデータが Students テーブルに取り込まれました。

IDENTITY 列の注意点

先ほど作った Students テーブルの主キーの StudentID カラムは、IDENTITY 列ではありませんでした。

もし、この StudentID が IDENTITY 列だった場合は、BULK INSERT で CSV ファイルを取り込むときに、少し注意が必要です。

どのようになるのか、実際に確認してみます。

先ほどの Students テーブルを削除し、以下のスクリプトで作り直します。

CREATE TABLE dbo.Students (
    StudentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NULL,
    LastName  NVARCHAR(50) NULL,
    Birthday  DATE NULL,
    Remarks NVARCHAR(MAX) NULL
);

そして、先ほどと同じ BULK INSERT のスクリプトを実行します。

BULK INSERT dbo.Students
FROM 'C:\Test\Students.csv'
WITH (
    FORMAT = 'CSV',
    CODEPAGE = '65001', -- UTF-8
    FIRSTROW = 2
);

[実行結果]
CSV ファイルを BULK INSERT を使ってインポートする 4

取り込まれたデータの Students テーブルの StudentID カラムの値が、Students.csv と違う値になっています。

これは、取り込み先のテーブルに IDENTITY 列がある場合、CSVファイルの中の、そのカラムの値は無視されて、SQL Server が自動的に連番を採番するためです。

CSV ファイルの IDENTITY 列の値をそのまま取り込みたい場合は、KEEPIDENTITY というオプションを指定する必要があります。

BULK INSERT dbo.Students
FROM 'C:\Test\Students.csv'
WITH (
    FORMAT = 'CSV',
    CODEPAGE = '65001', -- UTF-8
    FIRSTROW = 2,
    KEEPIDENTITY
);

TRUNCATE TABLE でデータを空にして IDENTITY のカウンターをリセットしてから、KEEPIDENTITY ありの BULK INSERT 文を使って再取り込みしてみます。

[実行結果]
CSV ファイルを BULK INSERT を使ってインポートする 5

今回は、CSVファイルの StudentID の値がそのまま取り込まれていますね。

以上、SQL Server で BULK INSERT を使って CSV を取り込む方法と注意点をご説明しました。