この記事を読むのに必要な時間は約 18 分です。
データの取得
ダウンロード
今回は、SQLserverに対してデータの一括取得登録を行ってみたいと思います。データの一括登録ですが、インターネット上に中々良い素材が転がっています。
今回は以下の二つのデータを取得して登録する操作を行ってみたいと思います。
- 郵便局が公開する住所データ
- 国税局が公開する企業データ
どちらのデータもお試しで登録するにはちょうど良いのでそれぞれ試してみたいと思います。
郵便局
まずは、郵便局のデータから
Googleで[郵便局 ダウンロード csv]で検索してください。
この様な形で直ぐに見つかります。
リンク先を辿るとダウンロードできるページにつながりますので
今回は赤枠の２データをダウンロードしてみます。
※ローマ字のデータは後で何かに利用できそうだったら使ってみる程度で
ダウンロードするデータは全国一括版をお願いします。
圧縮した状態では小さい物ですね。
圧縮された状態から解凍しCSVの状態にしておきます。
次に国税庁のデータをダウンロードしてみたいと思います。
国税庁
Googleで[国税庁 ダウンロード csv 企業データ]で検索してください。
上側のデータでも良いのですが、下側のリンクから辿った方が早いです。
此方の情報は県別に分かれています。折角なので東京のデータをダウンロードしてみましょう。
圧縮状態で31MB なかなか熱いですね
解凍して郵便局のデータと一緒に置いてみました。
解凍してみたら164MBありました。
テスト登録してみるとしたら中々良いサンプルデータになりそうですね。
一括登録
一括登録する前にテーブル作ります。
CSV形式のデータを取り込む場合、WindowsのOSはそのデータをただのテキストデータとしか見ません。
そのため、 2005/12/17 と日付のデータがあったとしても、それを日付の形式ではなく、‘2’,’0′,’0′,’5′,’/’,’1′,’2′,’/’,’1′,’7′ と、連続した文字のデータがそこにあるだけとしか見てくれません。これらのデータは折角なので日付形式で取り込みたいものです。
※日付形式で取り込んでない場合、そのデータは文字列扱いなので日付の差分などを求めることができなくなります。
今回は、データの取り込みを行う前に、事前にテーブルを作成して意図した形式でデータを取り込むようにします。
住所データ用テーブル作成
以下のクエリをそのままコピペして貼り付けてテーブルを作成してください。
CREATE TABLE [dbo].[yubin]( [全国地方公共団体コード] [nvarchar](500) NULL, [旧郵便番号] [nvarchar](500) NULL, [新郵便番号] [nvarchar](500) NULL, [かな町域名] [nvarchar](500) NULL, [かな都道府県名] [nvarchar](500) NULL, [かな市区町村名] [nvarchar](500) NULL, [漢字都道府県名] [nvarchar](500) NULL, [漢字市区町村名] [nvarchar](500) NULL, [漢字町域名] [nvarchar](500) NULL, [data1] [nvarchar](500) NULL, [data2] [nvarchar](500) NULL, [data3] [nvarchar](500) NULL, [data4] [nvarchar](500) NULL, [data5] [nvarchar](500) NULL, [data6] [nvarchar](500) NULL ) ON [PRIMARY] GO
国税庁データ用テーブル作成
以下のクエリをそのままコピペして貼り付けてテーブルを作成してください。
CREATE TABLE [dbo].[kokuzei]( [sequenceNumber] [int] NULL, [corporateNumber] [nvarchar](500) NULL, [process] [nvarchar](500) NULL, [correct] [nvarchar](500) NULL, [updateDate] 2025/01/21 NULL, [changeDate] 2025/01/21 NULL, [name] [nvarchar](500) NULL, [nameImagedID] [nvarchar](500) NULL, [kind] [nvarchar](500) NULL, [prefectureName] [nvarchar](500) NULL, [cityName] [nvarchar](500) NULL, [streetNumber] [nvarchar](500) NULL, [addressImageID] [nvarchar](500) NULL, [prefectureCode] [nvarchar](500) NULL, [cityCode] [nvarchar](500) NULL, [postCode] [nvarchar](500) NULL, [addressOutside] [nvarchar](500) NULL, [addressOutsideImageID] [nvarchar](500) NULL, [closeDate] 2025/01/21 NULL, [closeCause] [nvarchar](500) NULL, [successorCorporateNumber] [nvarchar](500) NULL, [changeCause] [nvarchar](500) NULL, [assignmentDate] 2025/01/21 NULL, [latest] [nvarchar](500) NULL, [enName] [nvarchar](500) NULL, [enPrefectureName] [nvarchar](500) NULL, [enCityName] [nvarchar](500) NULL, [enAddressOutside] [nvarchar](500) NULL, [furigana] [nvarchar](500) NULL ) ON [PRIMARY] GO
上記でそれぞれ登録用のテーブルができました。
それでは次の項目でそれぞれデータを登録してみましょう。
バルクインサート
私は以下の単語を大昔から知っていました。
bulk insert yubin from 'g:\file\KEN_ALL.CSV' with ( format='csv');
こんな感じでメッセージが表示されれば成功です。
以下のように表示されていれば成功していることになります。
旨く行きましたか？
bcp
フォーマットファイルの作成
bcpユーティリティーを使ってデータを取り込む際に、取り込むデータのデータ型を指定します。そのフォーマットファイルは既存のテーブルからリバースして作成する事ができます。因みにbcpユーティリティーはSQLのコンソールではなくDOSの画面から実行します。
以下のコマンドでフォーマットファイルを作成してください。
bcp test001.dbo.yubin format nul -t, -c -x -f g:\file\format.xml -Usa
特に何も画面にはでてくれませんが、指定した出力先にできています。
以下、メモ帳で開いた結果です。
ソースコードとしても掲載しておきます。
<?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="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> <FIELD ID="15" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1000" COLLATION="Japanese_CI_AS"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="全国地方公共団体コード" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="2" NAME="旧郵便番号" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="3" NAME="新郵便番号" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="4" NAME="かな町域名" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="5" NAME="かな都道府県名" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="6" NAME="かな市区町村名" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="7" NAME="漢字都道府県名" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="8" NAME="漢字市区町村名" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="9" NAME="漢字町域名" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="10" NAME="data1" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="11" NAME="data2" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="12" NAME="data3" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="13" NAME="data4" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="14" NAME="data5" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="15" NAME="data6" xsi:type="SQLNVARCHAR"/> </ROW> </BCPFORMAT>
それでは生成したフォーマットファイルを利用してデータの登録を実施してみましょう
bcp test001.dbo.yubin in g:\file\KEN_ALL.CSV -f g:\file\format.xml -Usa
実行するとパスワードを聞かれた後、以下のように画面で表示されて登録処理が動きます。
登録処理ですが一瞬で終わります。
データベースに丁度良いサンプルデータが登録できましたので、
次回以降はこれらのデータを使って色々やってみたいと思います。
