Zenn
🚵

SQLServerへのCSVからのデータインポート（郵便番号データでの実例）

2024/06/24に公開
SQL
SQL Server
query
bulk
郵便番号データ
tech

概要・はじめに

SQLServerにCSVファイルなどで作成されているデータをインポートしたいとき、色々な手法ありますが、クエリとしてBulk Insert From使うのが簡単だと思うので、その手法です。
「郵便番号データ」は公開されいて入手可能な情報なので、そちらを使いながらの説明になります。

前提・準備

  • SQLServerを利用可能な状況にする。
    • 既存のSQLServerの利用か、ローカルにSQLServerを入れるかは環境次第、目的次第だと思います。
    • 今回はtestdbでの実行を想定しています。
  • 郵便番号のCSVファイルの入手
    • 郵便番号データの入手先から、「住所の郵便番号（CSV形式）」→「読み仮名データの促音・拗音を小書きで表記するもの」→「都道府県一覧」→「全国一括」でダウンロード
    • ZIP内のKEN_ALL.CSVが対象。以下の例ではC:\tempに保存しているものとします。

手順

手順１ ダミーテーブル作成

  • CSVファイルから取り込むダミーテーブルの作成
  • CSVファイルが期待通り落ちていないときなど調整必要なテーブルです。
  • CSVファイルに想定外のデータがあるケースもあるので、そこの細かい扱いについては、手順３でカバーする想定で、ひとまず入れておくスペースの想定です。
  • 郵便番号データのフォーマット形式の説明については郵便番号データの説明 へ
USE testdb
GO
-- ダミーテーブル「org_postcode」を作成
-- drop table org_postcode
CREATE TABLE [dbo].[org_postcode] (
	[organization_no] char(7) NULL,
	[oldadrsubno] char(7) NULL,
	[adrsubno] char(9) NULL,
	[prefecture_kname] nvarchar(20) NULL,   -- 都道府県名-カナ
	[locality_kname] nvarchar(40) NULL,     -- 市区町村名-カナ
	[city_kname] nvarchar(max) NULL,        -- 町域名-カナ
	[prefecture_jpname] nvarchar(20) NULL,  -- 都道府県名-日本語
	[locality_jpname] nvarchar(40) NULL,    -- 市区町村名-日本語
	[city_jpname] nvarchar(max) NULL,   -- 町域名-日本語
	[flg1] int NULL,
	[flg2] int NULL,
	[flg3] int NULL,
	[flg4] int NULL,
	[flg5] int NULL,
	[flg6] int NULL
)

手順２　ダミーテーブルへのBulkInsert

  • 物理ファイル（CSV）からSQLServer上のデータベース（ダミーテーブル）に取り込みます。
  • ダミーテーブルの列情報と、CSVの列情報（桁数など）に差異がある場合は失敗します。
    • その場合はダミーテーブルの定義見直し手順１の見直し（桁数の調整、Maxなども有効）が有効です。
    • ひとまず、SQLServer上に入れてしまえば、調整可能だと考えています・・・
  • BulkInsertについての詳細は、MSサイト、Zenn へ
-- org_postcodeの登録 BulkInsert From CSV
BULK INSERT dbo.org_postcode FROM 'C:\temp\KEN_ALL.csv' --ローカルパス
   WITH (
      FIELDTERMINATOR = ',', -- csv区切り文字
      ROWTERMINATOR = '\n',  -- row区切り文字
      DATAFILETYPE = 'char'
   )
  • ローカルパスはつないでいるホストでの場所を示します。
    • 自端末以外のSQLServerにつないでいる場合は、そのSQLServerに「C:\temp\KEN_ALL.csv」が必要です。
    • SSMSでクエリ画面開くときの接続先がサーバーAなら、サーバーAのCドライブに上記ファイルが必要になります。

手順３・実テーブルの作成

  • 実際に利用したいテーブル「postcode」（実テーブル）を作成します。
  • ダミーテーブルを整形して、Insertする先です。
-- postcodeの作成 idはキーとして持っておく
--drop table postcode
CREATE TABLE [dbo].[postcode] (
	id    INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	[organization_no] char(5) NULL,
	[oldadrsubno] char(5) NULL,
	[adrsubno] char(7) NULL,
	[prefecture_kname] nvarchar(10) NULL,   -- 都道府県名
	[locality_kname] nvarchar(30) NULL,     -- 市区町村名
	[city_kname] nvarchar(max) NULL,        -- 町域名　
	[prefecture_jpname] nvarchar(10) NULL,  -- 都道府県名-日本語
	[locality_jpname] nvarchar(30) NULL,    -- 市区町村名-日本語
	[city_jpname] nvarchar(max) NULL,       -- 町域名-日本語
	[flg1] int NULL,
	[flg2] int NULL,
	[flg3] int NULL,
	[flg4] int NULL,
	[flg5] int NULL,
	[flg6] int NULL
)

手順４・実テーブルへのBulkInsert

  • ダミーテーブルから実テーブルへのInsert
  • 列ごとの微調整はここで入れる想定
    • replaceで不要なダブルコーテーション「"」を削除しています
-- postcodeへのデータインサート
insert into postcode 
(
	[organization_no],[oldadrsubno],[adrsubno]
	,[prefecture_kname],[locality_kname],[city_kname]
	,[prefecture_jpname],[locality_jpname],[city_jpname]
	,[flg1],[flg2],[flg3],[flg4],[flg5],[flg6]
)
select 	
	org.organization_no
	,replace(org.oldadrsubno,'"','') -- 不要な”を省く
	,replace(org.adrsubno,'"','')
	,replace(org.prefecture_kname,'"','')
	,replace(org.locality_kname,'"','')
	,replace(org.city_kname,'"','')
	,replace(org.prefecture_jpname,'"','')
	,replace(org.locality_jpname,'"','')
	,replace(org.city_jpname,'"','')
	,org.flg1,org.flg2,org.flg3,org.flg4,org.flg5,org.flg6
from org_postcode org

手順５ 確認・後始末

  • 確認
    • 無事にインサートされているかの確認
select * from postcode where locality_jpname like '江東区%' 
--Indexなどは未考慮なので、３０秒から１分かかる場合あります。
  • 後始末
    • ダミーで作成したテーブルの削除（一時テーブルの場合は不要）
drop table org_postcode

総括・やってみた結果

  • 実際の公開されているCSVから利用するための手順です。
  • 他にも入手できるデータ（CSV）はあるので、それに合わせて、対象テーブルのテーブル構造を調整することで、応用は効くと思います。
  • ダミーテーブルの代わりに、一時テーブル（TempTable）などもいいと思いますが、再現性優先のために、実テーブルを想定しています。（作業後、ダミーテーブルはドロップを想定）
  • 権限などによっては、一時テーブル利用せざる得ない場合もあると思います。その場合は一時テーブル利用のために「org_postcode」を「#org_postcode」に変更することで、一時テーブルので利用になります。

補足

Discussion