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

コマンドだけで、SQL Server 2008 R2 Expressに日本郵便で
公開されている郵便番号情報を加工し、テーブルにロードしようと
試行錯誤しています。

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



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

という作戦を試してみます。





投入手順



1.以下の形式で仮テーブルを作成
zip_code char(7) --郵便番号
add_1 NCHAR(100) --都道府県名(漢字)
add_2 NCHAR(100) --市区町村名(漢字)
add_3 NCHAR(100) --町域名(漢字)


2.一旦、上記で作成したテーブルに必要な箇所のデータを入れる。

3.仮テーブルをSELECTし、住所を結合したデータを取得。

4.仮テーブルから取得したデータを本来のテーブルに投入。


という手順で行ってみることにしました。




テーブルの作成



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

ここと同じ手順です。

以下の内容を記載したファイルを、
D:\data\strategy1\create_post_code_tmp.sql
に保存しました。


USE SAMPLE;
GO

CREATE TABLE post_code_tmp (
    zip_code char(9) not null,
    add_1 nchar(100) not null,
    add_2 nchar(100) not null,
    add_3 nchar(100) not null,
);
GO




zip_codeをchar(9)としているのは、bcpコマンドで、「"」を
取り除く方法がわからず、区切り文字の指定を「,」にしているからです。
ダブルクォーテーションもあわせてテーブルに登録されるので、
結果的に9文字領域が必要です。


以下のコマンドを実行して、テーブルを作成しておきます。


sqlcmd -S WIN-9UJK3SHIFUD\SQLEXPRESS -i D:\data\strategy1\create_post_code_tmp.sql


※WIN-9UJK3SHIFUDはSQLServerの端末名




フォーマットxmlの検討



作成したpost_code_tmpテーブルにデータを投入するための
フォーマットファイルを考えます。

「RECORD」の指定が、読み込むファイルのレイアウト。
「ROW」の指定が、取り込むテーブルのレイアウトになりますので、
それを加味し、以下の内容のフォーマットファイルを作成します。



<?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="9"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="9"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200" COLLATION="Japanese_CI_AS"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="3"/>
  <FIELD ID="15" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="3"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="3" NAME="zip_code" xsi:type="SQLCHAR"/>
  <COLUMN SOURCE="7" NAME="add_1" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="8" NAME="add_2" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="9" NAME="add_3" xsi:type="SQLNCHAR"/>
 </ROW>
</BCPFORMAT>




COLUMNのSOURCEがFIELD IDと対応しています。
上記指定で、3行目がzip_codeに、7行目がadd_1にそれぞれマッピングされます。

このファイルを、D:\data\strategy1\format.xmlという名前で
utf-8で保存しました。


また、ダウンロードしておいた郵便番号のデータを解凍し、
D:\data\strategy1\ADD_0910.CSVに保存しておきます。




bcp SAMPLE.dbo.post_code_tmp in D:\data\strategy1\ADD_0910.CSV -f D:\data\strategy1\format.xml -S WIN-9UJK3SHIFUD\SQLEXPRESS -T


※WIN-9UJK3SHIFUDはSQLServerの端末名


上記コマンドを実行すると、データの取り込みが行われます。

04_001_20101116233916.png






bulk_insert



csvファイルをデータベースの仮テーブルにロードすることができましたので、
ダブルクォーテーションを取り除きつつ、住所を結合しつつ、
post_codeテーブルにインサートしようと思います。


まず、post_codeに投入する結果セットを得るためのSQLを考えます。


SELECT
REPLACE(zip_code, '"', ''),
REPLACE(RTRIM(add_1) + RTRIM(add_2) + RTRIM(add_3),'"', '')
FROM sample.dbo.post_code_tmp



04_002_20101116233916.png




厳密には、住所に「"」を含む場合まずいことになるのですが、
今回はこのSQLで行こうと思います。

この結果をそのままinsert文に当てはめ、post_codeテーブルに
突っ込みます。

参考にしたのは以下のURLです。
OPENROWSET (Transact-SQL)



INSERT sample.dbo.post_code (zip_code, address)
SELECT
REPLACE(t.zip_code, '"', ''),
REPLACE(RTRIM(t.add_1) + RTRIM(t.add_2) + RTRIM(t.add_3),'"', '')
FROM sample.dbo.post_code_tmp as t







一括実行のためのバッチファイル



create_post_code_tmpは既に作成済みとして、

create_post_code_tmpの内容を削除。
csvファイルのデータをロード。
post_codeへのデータインサート

上記処理を一括で行うため、load1.batという名前のバッチファイルを作成しました。



@echo off

rem 一旦、テンポラリテーブルの削除
rem -Qで引数のクエリを発行する
sqlcmd -S WIN-9UJK3SHIFUD\SQLEXPRESS -Q "DELETE FROM SAMPLE.dbo.post_code_tmp"
sqlcmd -S WIN-9UJK3SHIFUD\SQLEXPRESS -Q "DELETE FROM SAMPLE.dbo.post_code"

rem データのロード
bcp SAMPLE.dbo.post_code_tmp in D:\data\strategy1\ADD_0910.CSV -f D:\data\strategy1\format.xml -S WIN-9UJK3SHIFUD\SQLEXPRESS -T

rem post_codeへデータを投入
sqlcmd -S WIN-9UJK3SHIFUD\SQLEXPRESS -Q "INSERT sample.dbo.post_code (zip_code, address) SELECT REPLACE(t.zip_code, '""', ''),REPLACE(RTRIM(t.add_1) + RTRIM(t.add_2) + RTRIM(t.add_3),'""', '') FROM sample.dbo.post_code_tmp as t"

pause


※WIN-9UJK3SHIFUDはSQLServerの端末名



狙い通りのデータが作成できた模様です。

04_003_20101116233916.png













関連記事

コメント

非公開コメント

プロフィール

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

PR




検索フォーム

月別アーカイブ