SQL Server で BULK INSERT をつかってcsvなどのデータを取込む方法

BULK INSERT とは

csvなどのデータを、テーブルにインポートできるコマンドです。

BULK INSERT (Transact-SQL) - SQL Server | Microsoft Docs

SQL Server で、ユーザーが指定した形式で、データベース テーブルまたはビューにデータ ファイルをインポートします。

データのインポートはウィザードを使っても行えるのですが、コマンドを使えばストアドの処理に記載したり自動化ができるので便利です。

構文

おおまかな構文は以下のようなかんじです。

BULK INSERT
	インポート先のテーブル名
FROM
	インポートしたいデータファイルのパス
WITH (
	オプション
)

オプション

BULK INSERT で指定できる主なオプションについてです。

DATAFILETYPE

インポートしたいデータファイルの型を指定できます。

DATAFILETYPE の値	形式
char (既定値)	文字形式。
native	ネイティブ (データベース) データ型。
widechar	Unicode 文字。
widenative	ネイティブ (データベース) データ型。ただし、データが Unicode として格納される char、varchar、text 列は除きます。

FIELDTERMINATOR

インポートしたいデータファイルのフィールド ターミネータ（区切り文字）を指定できます。
既定は \t (タブ文字) です。
ターミネータとしてサポートされる主な文字は以下です。

ターミネータ文字	指定方法
タブ	\t
改行文字	\n
キャリッジ リターン/ライン フィード	\r
カンマ	,

詳細については、「フィールド ターミネータと行ターミネータの指定 (SQL Server)」を参照してください。

ROWTERMINATOR

インポートしたいデータファイルの行ターミネータ（行の終わり）を指定できます。
既定は \r\n (改行文字) です。

FIRSTROW

インポートしたいデータファイルの取込開始行を指定できます。
既定値は、指定したデータファイルの先頭行です。
FIRSTROW は 1 から始まります。

FORMAT

2017以降のバージョンであれば、FORMAT = 'CSV' と指定すれば、csvファイルを簡単に取り込めます。

FORMATFILE

フォーマットファイルのパスを指定できます。
フォーマットファイルは次の場合に使用します。

• データファイルに含まれる列の数が、テーブルまたはビューより多い、または少ない。
• 列の順序が異なる。
• 列の区切り記号が異なる。

フォーマットファイルは、bcp ユーティリティを使用して作成し、必要に応じてテキストエディターで修正します。
詳細については、「bcp ユーティリティ」と「フォーマット ファイルの作成」を参照してください。

BULK INSERT の使用例

実際にBULK INSERT を使用してデータを取込んでみましょう。
テーブル [Sales] にデータをインポートします。

テーブル定義

列名	データ型
SalesOrderID	int IDENTITY(1,1)
RevisionNumber	tinyint
OrderDate	datetime
Status	tinyint
CustomerID	int
SubTotal	money
ModifiedDate	datetime
Description	nvarchar(255)

テーブル作成クエリ

CREATE TABLE [bulkInsertTest].[dbo].[Sales](
	 [SalesOrderID] [int] IDENTITY(1,1)
	,[RevisionNumber] [tinyint]
	,[OrderDate] [datetime]
	,[Status] [tinyint]
	,[CustomerID] [int]
	,[SubTotal] [money]
	,[ModifiedDate] [datetime]
	,[Description] [nvarchar](255)
)

カンマ区切りcsvの取込み（FORMATオプション）

SQL Server が2017以降のバージョンであれば、FORMAT = 'CSV' で簡単にデータを取込みできます。

以下のようなデータを取込みます。

• ヘッダーなし
• カンマ区切り

csvファイルは「D:\bulkInsertTest\csvData.csv」に配置しました。
取込むためのsql文は以下です。

BULK INSERT
	[bulkInsertTest].[dbo].[Sales]
FROM
	'D:\bulkInsertTest\csvData.csv'
WITH(
	FORMAT = 'CSV'
)

BULK INSERT を実行します。

SELECT でデータがインポートされた事を確認します。

カンマ区切りcsvの取込み

今度はFORMATオプションを使用せずに取込みます。
一旦テーブルの中身は削除します。

TRUNCATE TABLE [bulkInsertTest].[dbo].[Sales]

オプションで区切り文字などの指定を行います。
今回は、
DATAFILETYPEは「char」
FIELDTERMINATORは「,」
ROWTERMINATORは「\n」を指定します。

BULK INSERT
	[bulkInsertTest].[dbo].[Sales]
FROM
	'D:\bulkInsertTest\csvData.csv'
WITH(
	 DATAFILETYPE = 'char'
	,FIELDTERMINATOR = ','
	,ROWTERMINATOR = '\n'
)

BULK INSERT を実行し、正しくインポートされました。

ヘッダー付きcsvの取込み

次は以下のような、1行目にヘッダー（カラム名）を含んでいるcsvの取込みをします。

• ヘッダーあり
• カンマ区切り

csvファイルは「D:\bulkInsertTest\csvDataHeader.csv」に配置しました。

このまま取込むと、ヘッダーもデータとして取込んでしまいます。

メッセージ 4864、レベル 16、状態 1、行 3
行 1、列 1 (SalesOrderID) の一括読み込みデータ変換エラー (型の不一致または指定されたコードページでは無効な文字)。

SalesOrderID がint型なのに、"SalesOrderID"という文字列を取込もうとして、失敗しました。

ヘッダーを取込みたくない場合は、FIRSTROWオプションを使用します。
取込むためのsql文は以下です。

BULK INSERT
	[bulkInsertTest].[dbo].[Sales]
FROM
	'D:\bulkInsertTest\csvDataHeader.csv'
WITH(
	 DATAFILETYPE = 'char'
	,FIELDTERMINATOR = ','
	,ROWTERMINATOR = '\n'
	,FIRSTROW = 2	--2行目から取込みたい
)

文字列に改行コードを含むcsvの取込み

文字列の中に改行コードを含んでいる場合のcsvの取込みです。

• ヘッダーなし
• カンマ区切り
• 文字列内に改行あり
• 改行のある列はダブルクォーテーションでくくられている

csvファイルは「D:\bulkInsertTest\csvDataLine.csv」に配置しました。

普通に取込むとこのようなエラーが出てしまいます。

SQL Server が2017以降のバージョンであれば、FORMAT = 'CSV' を指定すれば取込みが可能です。

BULK INSERT
	[bulkInsertTest].[dbo].[Sales]
FROM
	'D:\bulkInsertTest\csvDataLine.csv'
WITH(
	FORMAT = 'CSV'
)

ダブルクォーテーションは省いて取込んでくれます。

FORMATオプションを使用せずに取込む際は少し大変です。
今回のサンプルデータでは、改行コードを含んでいる文字列のカラム [Description] が一番最後であること、[Description] はすべてダブルクォーテーションで囲まれているので、以下のクエリで取込みができました。

BULK INSERT
	[bulkInsertTest].[dbo].[Sales]
FROM
	'D:\bulkInsertTest\csvDataLine.csv'
WITH(
	 DATAFILETYPE = 'char'
	,FIELDTERMINATOR = ','
	,ROWTERMINATOR = '"\n'
)

ただ、冒頭のダブルクォーテーションも一緒に取込まれてしまったり、サンプルデータのように上手いこと行の終わりにダブルクォーテーションがついているような事はまれなので、2017以降のバージョンなのであれば無難にFORMATオプションを使用したほうがよいです。

文字列内の改行コードが\r、行の終わりの改行コードが\nなどと区別出来る場合であれば取込みやすいです。

BULK INSERT
	[bulkInsertTest].[dbo].[Sales]
FROM
	'D:\bulkInsertTest\csvDataLine2.csv'
WITH(
	 DATAFILETYPE = 'char'
	,FIELDTERMINATOR = ','
	,ROWTERMINATOR = '\n'
)

ただやはりダブルクォーテーションも取込まれてしまうので、あとで取り除くなどをしてあげる必要があります。

固定長テキストファイルの取込み

固定長のテキストファイルの場合は、フォーマットファイルを使用して取込む必要があり、ひと手間かかります。

• ヘッダーなし
• 固定長

csvファイルは「D:\bulkInsertTest\csvDataLen.csv」に配置しました。

bcp ユーティリティを使用してフォーマットファイルを作成します。

bcp ユーティリティとは
bcp ユーティリティ - SQL Server | Microsoft Docs

b ulk c opy p rogram ユーティリティ (bcp) は、Microsoft SQL Server のインスタンスと、ユーザー指定の形式のデータ ファイルとの間でデータの一括コピーを行います。 bcp ユーティリティを使うと、多数の新規行を SQL Server テーブルにインポートしたり、データをテーブルからデータ ファイルにエクスポートしたりできます。

フォーマットファイルとは
XML 以外のフォーマット ファイル (SQL Server) - SQL Server | Microsoft Docs

bcp ユーティリティがダンロードされていなければ、bcp ユーティリティのページからダウンロードしてください。

コマンドプロンプトでbcpコマンドを実行します。

コマンドプロンプト

bcp [database_name] format null -f [format_file] -c -t[field_term] -T

bcp コマンドの主な引数

引数	内容
bcp [database_name]	取込み先のテーブルを指定します。
format	データ ファイルのパスの代わりにnullを指定します。
-f [format_file]	フォーマットファイルをエクスポートしたいパスを指定します。
-c	このオプションを使用すると、フィールドごとにプロンプトが表示されません。
-t [field_term]	フィールド ターミネータを指定します。今回取込みたいファイルは区切り文字がないので空文字を指定しています。
-T	Windows認証を使用してSQL Serverに接続します。-T を指定しない場合、正常にログインするには -U と -P を指定する必要があります。
-S [server_name]	接続先となる SQL Server のインスタンスを指定します。サーバーを指定しない場合、 ローカル コンピューター上の SQL Server の既定のインスタンスに接続されます。

今回は以下コマンドを実行しました。

コマンドプロンプト

bcp [bulkInsertTest].[dbo].[Sales] format null -f D:\bulkInsertTest\ff_csvDataLen.fmt -c -t "" -T

エクスポートされたフォーマットファイル「ff_csvDataLen.fmt」をテキストエディタで開きます。

4つ目の列の数字が固定長数なので、取込みたいcsvの固定長数に編集します。

14.0
8
1       SQLCHAR             0       5       ""       1     SalesOrderID                     ""
2       SQLCHAR             0       5       ""       2     RevisionNumber                   ""
3       SQLCHAR             0       20      ""       3     OrderDate                        ""
4       SQLCHAR             0       5       ""       4     Status                           ""
5       SQLCHAR             0       10      ""       5     CustomerID                       ""
6       SQLCHAR             0       15      ""       6     SubTotal                         ""
7       SQLCHAR             0       20      ""       7     ModifiedDate                     ""
8       SQLCHAR             0       30     "\r\n"   8     Description                      Japanese_CI_AS

FORMATFILEオプションに上記で作成したfmtファイルを指定して取込みます。

BULK INSERT
	[bulkInsertTest].[dbo].[Sales]
FROM
	'D:\bulkInsertTest\csvDataLen.csv'
WITH(
	 FORMATFILE = 'D:\bulkInsertTest\ff_csvDataLen.fmt'
)

列の順序が異なる固定長テキストファイルの取込み

フォーマットファイルを使用すれば、列の順序を変更して取込むこともできます。
以下のようなデータを取込んでみます。

• ヘッダーなし
• 固定長
• 列の「ModifiedDate」と「Description」の順序が逆になっている

csvファイルは「D:\bulkInsertTest\csvDataLen2.csv」に配置しました。

先ほどのフォーマットファイル「ff_csvDataLen.fmt」をテキストエディタで開き、以下のように編集します。

14.0
8
1       SQLCHAR             0       5       ""       1     SalesOrderID                     ""
2       SQLCHAR             0       5       ""       2     RevisionNumber                   ""
3       SQLCHAR             0       20      ""       3     OrderDate                        ""
4       SQLCHAR             0       5       ""       4     Status                           ""
5       SQLCHAR             0       10      ""       5     CustomerID                       ""
6       SQLCHAR             0       15      ""       6     SubTotal                         ""
7       SQLCHAR             0       30      ""       8     Description                      Japanese_CI_AS
8       SQLCHAR             0       20      "\r\n"   7     ModifiedDate                     ""

FORMATFILEオプションに上記で作成したfmtファイルを指定して取込みます。

BULK INSERT
	[bulkInsertTest].[dbo].[Sales]
FROM
	'D:\bulkInsertTest\csvDataLen2.csv'
WITH(
	 FORMATFILE = 'D:\bulkInsertTest\ff_csvDataLen.fmt'
)

フォーマットファイルで指定したとおり、「ModifiedDate」と「Description」の順序を逆にして取込んでくれました。

最後に

BULK INSERT を使用する際は、取込みたいデータの区切り文字など注意深く見る必要があります。
上手く使えば作業を自動化できたり、ストアド化して別のツールからデータを取込んだりできるのでとても便利だと思います。

参考サイト

SQL Server BCP ユーティリティのインポートする方法(固定長ファイル)
【SQL SERVER】固定長データのインポート