DB

SQL Serverのbcpコマンドでフォーマットファイルを利用してみた

 

bcpコマンドを利用すると、CSVファイルのデータをSQL Serverのテーブルに書き込めるが、その際に利用するCSVファイルの形式を、フォーマットファイルで指定することができる。また、フォーマットファイルを、bcpコマンドで自動生成することもできる。

今回は、bcpコマンドでフォーマットファイルを自動生成すると共に、生成したフォーマットファイルを利用してCSVファイルのデータをSQL Serverのテーブルに書き込んでみたので、その手順を共有する。

前提条件

下記記事のように、SQL Serverでbcpコマンドを利用できること。

SQL Serverでbcpコマンドを利用してみたbcpコマンドを利用すると、SQL ServerのテーブルのデータをCSVファイルに出力したり、逆にCSVファイルのデータをSQL Se...

やってみたこと

  1. フォーマットファイルの作成
  2. フォーマットファイルを利用したCSVインポート

フォーマットファイルの作成

bcpコマンドを利用すると、SQL Serverのテーブルに書き込むためのフォーマットファイルを自動生成できる。その手順は、以下の通り。

なお、フォーマットファイルの形式は、XML形式とXML形式以外の2通りがあるが、今回はXML形式でフォーマットファイルを作成するものとする。

1) create table文を利用して、employeeテーブルを作成する。

create table dbo.employee (
    emp_id int not null primary key
  , emp_name nvarchar(40) not null
  , memo nvarchar(120)
  , create_user varchar(40)
  , create_date datetime
  , update_user varchar(40)
  , update_date datetime
)
フォーマットファイルの作成_1

2) コマンドプロンプトを起動し、bcpコマンドの配置されているディレクトリに移動する。

cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn
dir
フォーマットファイルの作成_2_1

　なお、以下の「bcp.exe」が、bcpコマンドになる。
フォーマットファイルの作成_2_2

3) bcpコマンドを利用して、XML形式のフォーマットファイルを作成する。

bcp dbo.employee format nul -c -x -f "C:\tmp\employee.xml" 
     -t , -S localhost -U USER01 -P USER01 -d master
フォーマットファイルの作成_3

なお、bcpコマンドは、「bcp (テーブル名) format nul -c -x -f (フォーマットファイルパス(XML形式)) -t(区切り文字) -S (ホスト名) -U (ユーザー名) -P (パスワード) -d (データベース名)」という形式で指定している。

また、オプション「-x」を指定することで、作成されるフォーマットファイルがXML形式になる。

4) 3)で出力されたXMLファイルの内容は、以下の通り。
フォーマットファイルの作成_4_1

フォーマットファイルの作成_4_2
<?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="12"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="80" 
      COLLATION="Japanese_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="240" 
      COLLATION="Japanese_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" 
      COLLATION="Japanese_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="24"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" 
      COLLATION="Japanese_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="emp_id" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="emp_name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="memo" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="create_user" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="create_date" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="6" NAME="update_user" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="7" NAME="update_date" xsi:type="SQLDATETIME"/>
 </ROW>
</BCPFORMAT>



「Envader」はLinuxコマンドやDatabase SQL等のスキルを、環境構築不要で習得できる学習サイトだった「Envader」は、ITエンジニアとしてよく使うLinuxコマンドやDatabase SQL等のスキルを、解説を読んだ上で、問題を解き...

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

bcpコマンドを利用すると、CSVファイルのデータをSQL Serverのテーブルに書き込めるが、その際にフォーマットファイルを利用することもできる。その手順は、以下の通り。

1) インポートする予定のCSVファイルの内容は、以下の通り。
フォーマットファイルの利用_1

2) employeeテーブルに、カラム「new_col」を追加する。

ALTER TABLE dbo.employee ADD new_col VARCHAR(20)
フォーマットファイルの利用_2

3) インポートする前のemployeeテーブルのデータは、以下の通り。

SELECT * FROM dbo.employee
フォーマットファイルの利用_3

4)「C:\tmp」フォルダ下に、フォーマットファイルとインポートする予定のファイルを配置する。
フォーマットファイルの利用_4

5) コマンドプロンプトを起動し、bcpコマンドの配置されているディレクトリに移動する。

cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn
フォーマットファイルの利用_5

6) bcpコマンドでXML形式のフォーマットファイルを利用して、CSVファイルのデータをemployeeテーブルに追加する。

bcp dbo.employee in "C:\tmp\employee_in.csv" 
    -f "C:\tmp\employee.xml" -S localhost -U USER01 -P USER01 -d master
フォーマットファイルの利用_6

なお、bcpコマンドは、「bcp (テーブル名) in (インポートするCSVファイル名) -f (フォーマットファイルパス(XML形式)) -S (ホスト名) -U (ユーザー名) -P (パスワード) -d (データベース名)」という形式で指定している。

7) インポートした後のemployeeテーブルのデータは、以下の通り。

SELECT * FROM dbo.employee
フォーマットファイルの利用_7

要点まとめ

  • bcpコマンドを利用すると、CSVファイルのデータをSQL Serverのテーブルに書き込めるが、その際に利用するCSVファイルの形式を、フォーマットファイルで指定することができる。また、フォーマットファイルを、bcpコマンドで自動生成することもできる。
RELATED POST