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テーブルを作成する。

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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
)
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 )
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コマンドの配置されているディレクトリに移動する。

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn
dir
cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn dir
cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn
dir
フォーマットファイルの作成_2_1

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

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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
bcp dbo.employee format nul -c -x -f "C:\tmp\employee.xml"
-t , -S localhost -U USER01 -P USER01 -d master
bcp dbo.employee format nul -c -x -f "C:\tmp\employee.xml" -t , -S localhost -U USER01 -P USER01 -d master
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
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<?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>
<?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>
<?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」を追加する。

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
ALTER TABLE dbo.employee ADD new_col VARCHAR(20)
ALTER TABLE dbo.employee ADD new_col VARCHAR(20)
ALTER TABLE dbo.employee ADD new_col VARCHAR(20)
フォーマットファイルの利用_2

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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT * FROM dbo.employee
SELECT * FROM dbo.employee
SELECT * FROM dbo.employee
フォーマットファイルの利用_3

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

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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn
cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn
cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn
フォーマットファイルの利用_5

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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
bcp dbo.employee in "C:\tmp\employee_in.csv"
-f "C:\tmp\employee.xml" -S localhost -U USER01 -P USER01 -d master
bcp dbo.employee in "C:\tmp\employee_in.csv" -f "C:\tmp\employee.xml" -S localhost -U USER01 -P USER01 -d master
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テーブルのデータは、以下の通り。

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT * FROM dbo.employee
SELECT * FROM dbo.employee
SELECT * FROM dbo.employee
フォーマットファイルの利用_7

要点まとめ

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