郵便番号データを SQL Server で利用するには（１） – SQL Server 2012 Management Studio でデータ変換サービス DTS を使って CSV データを SQL Server に取り込む

データ変換サービス DTS はSQL Server 2000 の頃からよく使っていた。Excel や Access 、CSV 形式のテキストファイルなどを SQL Server にウィザードで取り込むことができる便利なツールだ。だが DTS は無償版の SQL Server 2012 Management Studio のメニューからは利用できない。

＜Fig.1 : SQL Server 2012 Management Studio の「ツール」メニューからはデータ変換サービス DTS が使えない＞

データ変換サービス DTS を利用するには、Windowsのスタートメニューで Microsoft SQL Server 2012 から「データのインポートおよびエクスポート」を実行するか、以下のフォルダにある DTSWizard.exe を直接実行する。フォルダ名はインストールしている SQL Server や Management Studio のバージョンによって異なる場合もある。

C:\Program Files\Microsoft SQL Server\110\DTS\Binn

＜Fig.2 : Windowsのスタートメニューから「データのインポートおよびエクスポート」を実行する＞

＜Fig.3 : C ドライブの Program Files フォルダから直接 DTS ウィザード DTSWizard.exe を起動する＞

データ変換サービス DTS を起動する前に、日本郵便の Web ページから最新の郵便番号データをダウンロードしておく。データは CSV 形式で、ZIP 形式の圧縮になっている。ファイルをダウンロードし、適当なフォルダに保存して展開しておく。展開すると次のように KEN_ALL.CSV というファイルができる。これは全国の郵便番号を一括でダウンロードしたファイルだ。

ちなみにこのファイルは、Excel をインストールしているコンピュータなら下のように Excel との関連付けがされており、ダブルクリックすると Excel で開くことができる。しかし Excel のバージョンや保存形式によって最大行数の制約を受けることがあるので、くれぐれも上書き保存しないように。うっかり上書き保存するとデータが切り捨てられてしまう場合がある。

＜Fig.4 : 日本郵便の Web サイトから郵便番号データをダウンロードする＞

＜Fig.5 : 日本郵便からダウンロードした ZIP 圧縮形式の郵便番号データを解凍した＞

郵便番号データの準備ができたら、データ変換サービス DTS を起動する。次の起動画面が表示されるだろう。

＜Fig.6 : データ変換サービス DTS を起動する＞

「次へ」をクリックしてウィザードをすすめると「データソースの選択」画面になる。ここでは CSV 形式のテキストファイルを読み込みたいので、「データソース」を「フラットファイルソース」にし、先に保存した郵便番号の CSV ファイルを「ファイル名」のところで「参照」ボタンをクリックして選択する。

「ロケール」と「コードページ」には文字コード体系がデフォルトで設定されているはずだ。「形式」では「テキスト修飾子」がデフォルトで＜なし＞になっているが、この郵便番号ファイルはテキストデータをダブルクォーテーション「”」で囲んでいるので、「テキスト修飾子」に「”」を入力する。またデータにフィールド名はないので「先頭データ行を列名として使用する」のチェックを外す。

＜Fig.7 : データ変換サービス DTS でデータソースの選択をする＞

左側の「データソース」で「全般」から「列」のビューに切り替えると、データをプレビューで確認できる。

＜Fig.8 : データ変換サービス DTS のデータソースの選択で「列」のビューに切り替える＞

次に左側の「データソース」で「全般」から「詳細設定」のビューに切り替える。ここで重要なのは、OutputColumnWidth の設定で、この値を読み込むデータの最大サイズ以上に指定しておかなければならない。

そこであらかじめ Excel で郵便番号データの各フィールドの要素と最大データ長を調べておく。フィールドの要素は次のようになっている。

列0 : 何かの管理番号（最大5桁の数字）
列1 : 郵便番号の前から3～5桁を切り取った数値
列2 : 郵便番号（7桁の数字）
列3 : 都道府県の半角カナ（最大文字長7）
列4 : 市区町村の半角カナ（最大文字長22）
列5 : 市区町村以降の半角カナ（最大文字長73）
列6 : 都道府県（漢字、最大文字長4）
列7 : 市区町村（漢字、最大文字長10）
列8 : 市区町村以降（漢字、最大文字長37）
列9～14 : 住所データを管理する何らかの記号（1桁の数値）

デフォルトで OutputColumnWidth の値は50に設定されている。ここで問題になるのは、列5の半角カナ73文字と列8の漢字37文字だ。漢字は2バイト文字なので、文字数の2倍、74以上のサイズを必要とする。そこでこの2つのフィールドは、それぞれサイズを増やしておかなければならない。

＜Fig.9 : データ変換サービス DTS のデータソースの選択で「詳細設定」のビューに切り替える＞

あらかじめデータの最大サイズがわかっていれば手作業でOutputColumnWidth の値を変更するが、わからないときは「型の推測」ボタンをクリックし、実際のデータをスキャンして自動的に調べることもできる。

＜Fig.10 : データ変換サービス DTS で列の型の推測をする＞

＜Fig.11 : データ変換サービス DTS で型の推測をした＞

ここまでで入力データの設定ができた。次は変換先の設定をする。変換先はデフォルトの SQL Server Native Client とし、サーバー名をドロップダウンで選択する。そしてデータベースをデータをインポートしたいデータベースに変更する。

＜Fig.9 : データ変換サービス DTS で変換先を選択する＞

次の「コピー元のテーブルおよびビューを選択」では、元データとインポートするテーブルとのデータマッピングを編集することができる。「マッピングの編集」ボタンをクリックする。

＜Fig.10 : データ変換サービス DTS でコピー元のテーブルおよびビューを選択する＞

「列マッピング」のビューでは、変換元と変換先のフィールド名、フィールドの型、サイズが自動的に設定されている。設定値は「型の推測」で元データから得られたものだ。

＜Fig.11 : データ変換サービス DTS で列マッピングを編集する＞

「型の推測」で列0から列2までは数字と判断されている。SQL Server でどのような処理をしたいかによるが、数字であってもコードとして処理したい場合は、文字列にしたほうが扱いやすい場合がある。また住所のデータは varchar ではなく nvarchar にしたい。そこで型やサイズを手作業で変更する。

＜Fig.12 : データ変換サービス DTS で変換先の型とサイズを編集する＞

またデフォルトで「変換先テーブルを作成する」のラジオボタンが選択されており、SQL Server に新しくテーブルを作成することができるが、「SQL の編集」ボタンをクリックすると「テーブル作成 SQL ステートメント」のウィンドウが表示され、SQL Server に生成するテーブルのテーブル名やフィールド名を編集することができる。テーブル名を「郵便番号住所」とし、各フィールド名を設定する。

＜Fig.13 : テーブル作成 SQL ステートメントを編集する＞

テーブル作成 SQL ステートメントの編集を終了すると「列マッピング」のビューに戻る。このビューに戻っても「変換先」のフィールド名は変わらず「列 0」や「列 1」といったデフォルトのままであるので、手作業で「変換先」のフィールド名を書き換える。ドロップダウンリストには表示されないので、手作業で書き換える必要がある。

＜Fig.14 : データ変換サービス DTS の列マッピングで変換先のフィールド名を編集する＞

列マッピングの編集が終わると「コピー元のテーブルおよびビューを選択」画面に戻る。このときも戻ったときは「変換先」のテーブル名がデフォルトで与えられたままなので、「テーブル作成 SQL ステートメント」で変更したテーブル名に手作業で書き換える必要がある。

＜Fig.15 : データ変換サービス DTS のコピー元のテーブルおよびビューを選択で変換先のテーブル名を書き換える＞

次に「データ型マッピングの確認」になる。変換先のテーブル名やフィールド名、データの型やサイズを変更したので、データの切り捨てなどがおこる可能性があるとの注意がされるが、間違いがなければこのまま次にすすめてよい。

＜Fig.16 : データ変換サービス DTS でデータ型マッピングの確認をする＞

ここまでで設定は終了し、最後の「パッケージの実行」では、いますぐ実行したい場合は「すぐに実行する」のチェックボックスを入れたままにし、次へすすむ。

＜Fig.17 : データ変換サービス DTS でパッケージの実行をする＞

＜Fig.18 : データ変換サービス DTS でウィザードを完了する＞

ウィザードを完了すると、データ変換サービス DTS が変換元のデータを読みとり、SQL Server に送信する。変換のプロセスがわかり、レポートが表示される。

＜Fig.19 : データ変換サービス DTS で変換プロセスとレポートを表示する＞

SQL Server Management Studio でテーブルとデータを確認する。すべてのデータが変換されたかどうかは、ビューを作って確かめる。

——————————————————————-

SELECT COUNT(*) AS Expr1 FROM dbo.郵便番号住所

——————————————————————-

＜Fig.20 : SQL Server Management Studio でテーブルとデータを確認する＞

＜Fig.21 : SQL Server Management Studio でデータの件数を確認する＞

データ変換サービス DTS では、変換元のフィールドサイズの最大サイズがわかっていれば手作業で OutputColumnWidth の値を変更し、わからなければ「列の型の推測」でデータサイズを調べ、自動的に適用させればよい。その場合は、変換先のフィールドが設定したい型やサイズにならない場合があるので、そのときは手作業で編集する。

データ変換サービス DTS でエラーがおこったときは、まずフィールドサイズが OutputColumnWidth の設定値を超えていないかを確認しよう。