NDW

SQLServerでCSVをBULK INSERTする方法

SQLServer
目次

概要

  • BULK INSERTを使って、CSVファイルの内容をテーブルに一括登録するサンプルを紹介します。
  • 動作確認で使用している環境は次の通りです。
    OSWindows 10(64ビット)
    DBMicrosoft SQL Server Developer 64ビット版(15.0.2095.3)

基本的な使用方法

  • サンプルの前提
    • m_employeeテーブルにCSVファイル(2行目以降)を一括登録する想定のサンプルです。
    • Windows上で動作するSQL Serverを使用する前提とします。
    • 以降では特に断りがない限り、エンコーディングはShift_JIS、改行コードはCR+LFのファイルを使用します。
    • サンプルの動作確認に使用しているテーブル定義、CSVファイルはこちらで公開しています。
  • 基本的な使用方法
  • CSVファイル（UTF-8形式）を使用 ※BOM有無は関係ない
  • CSVファイル（UTF-8形式＋LF改行）を使用
  • タブ区切りファイル（TSVファイル）を使用
  • サンプルで使用している基本的なオプションの説明
    • CSVファイルを使用するためにFORMATオプションでCSVを指定しています。ここでいう「CSVファイル」とはRFC4180に準拠したファイルです。（既定はTSVファイル）
    • 「1行目がヘッダ行、2行目以降がデータ行」のファイルを想定しており、登録開始行を指定するためにFIRSTROWオプションを指定しています。
    • 区切り文字をカンマに変更するためにFIELDTERMINATORオプションを指定しています。（既定はタブ0x0a）
    • ファイルのエンコーディングを変更する場合はCODEPAGEオプションを指定します。
      Shift_JISは”932″、UTF-8の場合は”65001″(BOM有無に依存しない)です。その他のコードページはリファレンスをご覧ください。
    • 改行コードは既定でCR+LF(0x0d,0x0a)ですが、変更する場合はROWTERMINATORオプションで指定します。
      なお、Linux/Unix等で使われるLFを指定する場合、ROWTERMINATORとして’0x0a’を指定します。（”\n”を指定するとCR+LFと解釈される。）

使用時の注意点や補足

  • SQLServerの実行環境で、使用可能なCSVファイルのエンコーディングが変わります。
    • Windowsの場合はShift_JIS(コードページ932)になるようです。
    • 日本語を含むCSVファイルをbulk insertするアプリ場合、開発環境や試験環境によって文字化けが発生する可能性があります。環境によって期待するエンコーディング（コードページ）が変わらないよう、CODEPAGEオプションを指定することをお薦めします。
  • CSVのデータ行の空値(“”)は、登録先列がnull非許容(not null)の場合は空(“”)、null許容の場合はnullが登録されます。
  • IDENTITYが指定された列がある場合、既定では自動採番された値（CSVファイルの値は無視）が登録されます。他テーブルとのリレーションで使用している列の場合、データの整合性が崩れる可能性があるので注意が必要です。CSVファイルの値を使用する場合はKEEPIDENTITYオプションを指定します。
  • ヘッダ行の考え方とFIRSTROWオプションの注意点
    • 初見だと「ヘッダ行をスキップ（＝無視）するオプション」と思われますが、実際には無視されるわけではなく登録先テーブルの列数と一致することが検証されます。これを理解していないとエラー発生時の問題切り分けが難しくなります。
    • BULK INSERT仕様上、ヘッダ行という考えはなく全ての行がデータ行のようです。FIRSTROWは「登録開始するデータ行を指定するオプション」であり、「それより前のデータ行を完全に無視するためのオプション」ではありません。
    • 業務システムで使用するCSVファイルではヘッダ行が含まれる場合が多いと思います。このヘッダ行をスキップするために”FIRSTROW=2″を指定してもヘッダ行(1行目)の検証が行われるので、ヘッダ行でもデータ行と同じ列数を指定する必要があります。ただし、列数の検証が行われるだけで値（列名）の検証は行われないので、空文字等の適当な値で十分です。
    • この辺の考え方はリファレンス(FIRSTROWオプション)に記載されていますが、何度も動作検証してやっと理解できた気がします。

      The FIRSTROW attribute isn’t intended to skip column headers. Skipping headers isn’t supported by the BULK INSERT statement. If you choose to skip rows, the SQL Server Database Engine looks only at the field terminators, and doesn’t validate the data in the fields of skipped rows.

      意訳：『FIRSTROWはヘッダ行をスキップ（＝完全無視）するためのオプションではなく、そのような機能はBULK INSERTでサポートされていない。FIRSTROWを指定してスキップされた行（＝FIRSTROWで指定された行より前の行）は、値の妥当性は検証されず、フィールド区切りが確認（＝テーブル列数との一致の検証）される』

  • ここではローカルPC上のCSVファイルを使用していますが、共有フォルダにあるCSVファイル(“\\”で始まるUNC形式パス）や、外部データソースとして定義したAzure BLOBストレージにあるCSVファイルを使用することもできます。外部データソースを使う方法は、こちらのリファレンスをご覧ください。
  • 性能要件が厳しい場合、パフォーマンスチューニングが必要となります。
    （CODEPAGE, BATCHSIZE, ORDER, TABLOCKなどのオプションの検討）

典型的なエラーと解決のヒント

ヘッダ行の列数不正

リンク サーバー "(null)" の OLE DB プロバイダー "BULK" から必要なインターフェイス ("IID_IColumnsInfo") を取得できません。Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".
  • FIRSTROWオプションを指定時、FIRSTROW指定行より前の行で列数が不正だとこのエラーが発生します。（このエラーメッセージから原因を推測できる人は少ない気がします…）
  • 間接的に列数が不正になる場合があるので、問題の切り分けが難しい場合があります。
    1. 列名に含まれた日本語の文字化けによる列数誤判定
      • BULK INSERTで期待するコードページとCSVファイルのエンコーディングが一致しない場合、日本語部分を正しい値と判定できなくなります。（ここでは「文字化け」と表記）
      • 列値の日本語が文字化けすると列・行の区切りを正しく判断できなくなり、列数を誤判定するようです。
      • この場合、CODEPAGEオプションで「CSVファイルに対応するコードページ」を指定するか、CSVファイルのエンコーディングをBULK INSERT(Windowsでの既定はShift_JIS)に合わせます。
    2. 改行コードの誤認識による列数誤判定
      • BULK INSERTで期待する改行コードとCSVファイルの改行コードが一致しない場合、行の終わりを正しく判定できなくなります。
      • この場合、2行目以降も1行目として処理されるため、列数を誤判定するようです。
      • 経験的に、Windows環境以外で作成したCSVファイルや、Excel VBAで生成したCSVファイル(VBAで”\n”=LFしか出力していない)で本事象を見かけます。ほとんどのエディタでは改行コードが表示されない、されても違いが分かりづらいので、注意する必要があります。
      • この場合、ROWTERMINATORで「CSVで使われている改行コード」を指定するか、CSVファイルの改行コードをBULK INSERT(既定はCR+LF)に合わせます。
  • なお、FIRSTROW指定行以降で列数が不正な場合、「データ行の列数不正」のエラーになります。BLUK INSERTの実装で、FIRSTROW指定行の前後で検証ロジックが異なるので別々のエラーになっているのではないか、と推測します。

データ行の列数不正

CSV データ ファイル filename.csv の列値が無効であるため、一括読み込みが失敗しました。Bulk load failed due to invalid column value in CSV data file filename.csv in row xx, column xx.
  • テーブル列数とCSVファイル列数が不一致の場合に発生します。エラーメッセージの行番号の列数が正しいかを確認してください。
  • BULK INSERTで期待する改行コードとCSVファイルの改行コードが一致せずに、このエラーが発生する場合があります。この場合、ROWTERMINATORで「CSVで使われている改行コード」を指定するか、CSVファイルの改行コードをBULK INSERT(既定はCR+LF)に合わせます。

データ変換エラー

(column_name) の一括読み込みデータ変換エラー (型の不一致または指定されたコードページでは無効な文字)。Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row xx, column xx (column_name)
  • データ行の値を格納先列の型に変換できない場合に発生します。（例：”abc”をdatetime2型に格納など）
  • テーブル名の間違い、テーブル名の仕様変更等でテーブル列とデータ行の列の対応（順番や型）が合わない場合に頻出するエラーです。
  • この場合、テーブル側の列とデータ行の列（順番・型）が対応していることを確認します。

不適切な構文(FORMAT)

'FORMAT' 付近に不適切な構文があります。Incorrect syntax near 'FORMAT'.
  • FORMATオプションはSQL Server 2017以降からの対応になっており、接続先がSQL Server 2016以前の場合に発生します。
    BULK INSERT (Transact-SQL) – SQL Server | Microsoft Learn … FORMATオプション
  • 接続先のSQL Serverのバージョンは”select @@VERSION“で確認できます。
    なお、SQL Serverの製品名とバージョンの対応はこちらで確認できます。
  • 古いVisual Studio 2019をインストールすると、Local DBのバージョンがSQL Server 2016になり、この事象が発生する場合があります。この場合、LocalDBをSQL Server 2017以上にバージョンアップすることをお薦めします。
    SQL Server Express LocalDB – SQL Server | Microsoft Learn

参考

BLOBストレージの使用事例

  • 「Azure BLOBストレージ上にあるCSVファイルを使ってBULK INSERTする」という業務要件がありました。
  • これを実現するためのサンプルがリファレンスにあります。
    Azure BLOBストレージを外部ストレージとしてSQL Serverに登録し、そこからBULK INSERTする形になります。
  • Visual Studio付属のLocalDB(SQL Server Express LocalDB)、Azure Storage Emulatorを使ってローカルPCのみでテスト環境を作ろうとしたのですが、実現できませんでした。結果として、SQL Server Expressと実際のAzure BLOBストレージが必要でした。
    • 外部データソースを作成するための”CREATE EXTERNAL DATA SOURCE”ステートメントはLocalDBでサポートされていないため、SQL Server Express以上を使用する必要があります。私の場合、仕方ないので無償で使用できるSQL Server Expressをダウンロード・インストールしました。
    • SQL Server Expressを使ってAzure Storage Emulatorを外部データソースとして登録できました。BULK INSERTでその外部データソースを使用するとファイルが見えません。Azure BLOBストレージのコンテナや当該ファイルに無制限のSAS URLを発行し、ブラウザで参照できることを確認しているのですが、改善されません。
    • 問題切り分けのために、Azure Storage EmulatorではなくAzure BLOBストレージを使って同じことをしたら、正常にBULK INSERTできました。
      （2022/10現在、Azure Storage Emulatorは非推奨でAzuriteが推奨されています。こちらでできる？？）