SQL Server 2008 R2 Express コマンドでテーブルに郵便番号データをロードする(bcp)

コマンドで、SQL Server 2008 R2にデータベースとテーブルを作成しました。
SQL Server 2008 R2 Express コマンドでデータベースとテーブルを作成する(sqlcmd)

せっかくなので、郵便番号のデータを投入してみます。

データ登録用のプログラムを作成すれば、すぐに狙い通りの
ことは実行できるのですが、ここはあえてコマンドだけで。

ちなみに、SQL Serverへの接続を試した記事はこちら。
統合認証のSQL Server 2008にJDBCで接続する
IronPythonから統合認証でMS SQL Server 2008 Expressに接続する
Visual Basic 2010 ExpressからSQL Server 2008 Expressに接続する




登録用のデータ



いつもサンプルデータでお世話になっている日本郵便の
郵便番号データダウンロードサイトです。

郵便番号データダウンロード


色々試行錯誤したかったので、データ件数の少ない

●平成21年 10月 30日更新分


このデータをダウンロードして、サンプルに使わせていただきました。





bcp ユーティリティ



データの一括登録を行いたい場合には、「bcp」というコマンドを
使用すればよいようです。

bcp ユーティリティ

どんな形式のデータを用意すればよいか確かめるため、
手作業で以下のファイルを作成しました。


08203,茨城県土浦市小山田




郵便番号と住所のカンマ区切りのデータです。
これをD:\data\load.txtに保存しました。

bcpを使用してファイルをロードするコマンドは以下のとおり。


bcp [データベース.スキーマ.テーブル] in [取り込むファイル] -f [フォーマットファイル] -S [サーバー\インスタンス] -T



「データベース.スキーマ.テーブル」部分には、「SAMPLE.dbo.post_code」
「取り込むファイル」部分には作成したファイルのパス「D:\data\load.txt」
「サーバー\インスタンス」には「WIN-9UJK3SHIFUD\SQLEXPRESS」を指定しました。

ちなみに、-Tは信頼関係接続を行うオプションです。
SQL Server認証を使用する場合には、-U(ユーザー名)と-P(パスワード)を指定する
必要があるかと思います。


苦戦したのはフォーマットファイル。
これは、データベースと取り込むファイルのマッピングを指定するようで、
ここで区切り文字の指定なども行います。

フォーマット ファイルの作成
ここを見ながら、見よう見まねで作成したフォーマットファイルは以下のとおり。



<?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="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="200" COLLATION="Japanese_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="zip_code" xsi:type="SQLCHAR"/>
  <COLUMN SOURCE="2" NAME="address" xsi:type="SQLNCHAR"/>
 </ROW>
</BCPFORMAT>



これをD:\data\format.xmlにutf-8で保存しました。



以下のコマンドを実行すると・・・


bcp SAMPLE.dbo.post_code in D:\data\load.txt -f D:\data\format.xml -S WIN-9UJK3SHIFUD\SQLEXPRESS -T



03_001_20101116224320.png


ちゃんとデータベースにも取り込めたようです。

03_002_20101116224320.png








フォーマットファイルでの取り込まない行指定



適当な取り込み用サンプルデータをロードすることはできましたが、
実際に取り込もうとするオリジナルのファイルには、不要な行が存在します。

日本郵便から提供されいているCSVファイルのレイアウト
郵便番号データの説明


1:全国地方公共団体コード(JIS X0401、X0402)
2:(旧)郵便番号(5桁)
3:郵便番号(7桁)
4:都道府県名(カナ)
5:市区町村名(カナ)
6:町域名(カナ)
7:都道府県名(漢字)
8:市区町村名(漢字)
9:町域名(漢字)
10:一町域が二以上の郵便番号で表される場合の表示
11:小字毎に番地が起番されている町域の表示
12:丁目を有する町域の場合の表示
13:一つの郵便番号で二以上の町域を表す場合の表示
14:更新の表示
15:変更理由




欲しいのは3の「郵便番号」と7,8,9を結合した「漢字住所」です。


思案した結果、データを取り込む作戦として、


1.テンポラリテーブルに一旦データを入れて、SQLで加工したデータを
本来のテーブルにインサートする。

2.バッチで加工済みデータを作成してロード

3.ループしながらデータを加工し、sqlcmdでインサート


という三つを考えてみました。

一つずつ試してみた結果がこちら。



1.SQL Server 2008 R2 Express 郵便番号データロードの考察1

2.SQL Server 2008 R2 Express 郵便番号データロードの考察2

3.SQL Server 2008 R2 Express 郵便番号データロードの考察3



関連記事

コメント

非公開コメント

プロフィール

Author:symfo
blog形式だと探しにくいので、まとめサイト作成中です。
Symfoware まとめ

PR




検索フォーム

月別アーカイブ