bcpコマンドを利用すると、CSVファイルのデータをSQL Serverのテーブルに書き込めるが、その際に利用するCSVファイルの形式を、フォーマットファイルで指定することができる。また、フォーマットファイルを、bcpコマンドで自動生成することもできる。
今回は、bcpコマンドでフォーマットファイルを自動生成すると共に、生成したフォーマットファイルを利用してCSVファイルのデータをSQL Serverのテーブルに書き込んでみたので、その手順を共有する。
前提条件
下記記事のように、SQL Serverでbcpコマンドを利用できること。
やってみたこと
フォーマットファイルの作成
bcpコマンドを利用すると、SQL Serverのテーブルに書き込むためのフォーマットファイルを自動生成できる。その手順は、以下の通り。
なお、フォーマットファイルの形式は、XML形式とXML形式以外の2通りがあるが、今回はXML形式でフォーマットファイルを作成するものとする。
1) create table文を利用して、employeeテーブルを作成する。
1 2 3 4 5 6 7 8 9 | 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 ) |
2) コマンドプロンプトを起動し、bcpコマンドの配置されているディレクトリに移動する。
1 2 | cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn dir |
なお、以下の「bcp.exe」が、bcpコマンドになる。
3) bcpコマンドを利用して、XML形式のフォーマットファイルを作成する。
1 2 | bcp dbo.employee format nul -c -x -f "C:\tmp\employee.xml" -t , -S localhost -U USER01 -P USER01 -d master |
なお、bcpコマンドは、「bcp (テーブル名) format nul -c -x -f (フォーマットファイルパス(XML形式)) -t(区切り文字) -S (ホスト名) -U (ユーザー名) -P (パスワード) -d (データベース名)」という形式で指定している。
また、オプション「-x」を指定することで、作成されるフォーマットファイルがXML形式になる。
4) 3)で出力されたXMLファイルの内容は、以下の通り。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | <?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> |
フォーマットファイルを利用したCSVインポート
bcpコマンドを利用すると、CSVファイルのデータをSQL Serverのテーブルに書き込めるが、その際にフォーマットファイルを利用することもできる。その手順は、以下の通り。
1) インポートする予定のCSVファイルの内容は、以下の通り。
2) employeeテーブルに、カラム「new_col」を追加する。
1 | ALTER TABLE dbo.employee ADD new_col VARCHAR(20) |
3) インポートする前のemployeeテーブルのデータは、以下の通り。
1 | SELECT * FROM dbo.employee |
4)「C:\tmp」フォルダ下に、フォーマットファイルとインポートする予定のファイルを配置する。
5) コマンドプロンプトを起動し、bcpコマンドの配置されているディレクトリに移動する。
1 | cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn |
6) bcpコマンドでXML形式のフォーマットファイルを利用して、CSVファイルのデータをemployeeテーブルに追加する。
1 2 | bcp dbo.employee in "C:\tmp\employee_in.csv" -f "C:\tmp\employee.xml" -S localhost -U USER01 -P USER01 -d master |
なお、bcpコマンドは、「bcp (テーブル名) in (インポートするCSVファイル名) -f (フォーマットファイルパス(XML形式)) -S (ホスト名) -U (ユーザー名) -P (パスワード) -d (データベース名)」という形式で指定している。
7) インポートした後のemployeeテーブルのデータは、以下の通り。
1 | SELECT * FROM dbo.employee |
要点まとめ
- bcpコマンドを利用すると、CSVファイルのデータをSQL Serverのテーブルに書き込めるが、その際に利用するCSVファイルの形式を、フォーマットファイルで指定することができる。また、フォーマットファイルを、bcpコマンドで自動生成することもできる。
