日本郵政公社より提供されている全国郵便番号データを業務で利用する事は多いと思います。
でも実際利用する場合には、提供されているCSVそのままを利用する事はなく、一般的には、DBにインポート(コピー)して利用しますよね。
この、CSVインポートをSystem.Data.SqlClient.BulkCopyを利用して高速に行う方法を説明します。
Tipsのテーブルへ複数行の挿入でも説明しているのですが、ここで説明するのは、あくまで"全国郵便番号データ"をインポートする場合の処理です。
■前提
- 全国郵便番号データの全国一括データ(KEN_ALL.CSV)を対象としています。
- インポート(コピー)先は、SQL Server(2005 EXPRESS)
- インポート(コピー)先のデータへ追加するだけで、重複などは考慮しない
■インポート(コピー)先のテーブル概要
CREATE TABLE [dbo].[ZipCode](
[全国地方公共団体コード] [int] NOT NULL,
[郵便番号5] [varchar](5) COLLATE Japanese_CI_AS NULL,
[郵便番号7] [varchar](7) COLLATE Japanese_CI_AS NOT NULL,
[都道府県名カナ] [varchar](100) COLLATE Japanese_CI_AS NULL,
[市区町村名カナ] [varchar](100) COLLATE Japanese_CI_AS NULL,
[町域名カナ] [varchar](100) COLLATE Japanese_CI_AS NULL,
[都道府県名] [varchar](100) COLLATE Japanese_CI_AS NULL,
[市区町村名] [varchar](100) COLLATE Japanese_CI_AS NULL,
[町域名] [varchar](100) COLLATE Japanese_CI_AS NULL,
[FLG1] [smallint] NULL,
[FLG2] [smallint] NULL,
[FLG3] [smallint] NULL,
[FLG4] [smallint] NULL,
[FLG5] [smallint] NULL,
[FLG6] [smallint] NULL
) ON [PRIMARY]
上記のテーブルにCSVデータをインポート(コピー)します。
■フォームにコントロールを配置
また、名前空間を宣言しといてください。
Imports System.Data.SqlClient
Imports System.IO
Imports System.Text
■CSVを分解してDataTable取得する
CSVを分解して格納する為の、適切な列が定義されたDataTableを作成するプロシージャを作成します。
''' <summary>
''' 郵便番号データCSVを格納するDataTableを返します
''' </summary>
''' <returns>郵便番号データDataTable</returns>
''' <remarks></remarks>
Private Function CreateDataTable() As DataTable
Dim dt As New DataTable
With dt
''フィールド(列)を追加
.Columns.Add("全国地方公共団体コード", Type.GetType("System.Int32"))
.Columns.Add("郵便番号5", Type.GetType("System.String"))
.Columns.Add("郵便番号7", Type.GetType("System.String"))
.Columns.Add("都道府県名カナ", Type.GetType("System.String"))
.Columns.Add("市区町村名カナ", Type.GetType("System.String"))
.Columns.Add("町域名カナ", Type.GetType("System.String"))
.Columns.Add("都道府県名", Type.GetType("System.String"))
.Columns.Add("市区町村名", Type.GetType("System.String"))
.Columns.Add("町域名", Type.GetType("System.String"))
.Columns.Add("FLG1", Type.GetType("System.Int16"))
.Columns.Add("FLG2", Type.GetType("System.Int16"))
.Columns.Add("FLG3", Type.GetType("System.Int16"))
.Columns.Add("FLG4", Type.GetType("System.Int16"))
.Columns.Add("FLG5", Type.GetType("System.Int16"))
.Columns.Add("FLG6", Type.GetType("System.Int16"))
End With
Return dt
End Function
次に、CSVを分解して、CreateDataTableで取得したDataTableに、データを格納するプロシージャを作成します。
''' <summary>
''' 郵便番号データが格納されたDataTableを返します
''' </summary>
''' <param name="csvFilePath">郵便番号データCSVファイルのパス</param>
''' <returns>郵便番号データが格納されたDataTable</returns>
''' <remarks></remarks>
Private Function GetZipCodeDataTable(ByVal csvFilePath As String) As DataTable
Dim dt As DataTable = Me.CreateDataTable
Dim row As DataRow
Using sr As New StreamReader(csvFilePath, Encoding.GetEncoding(932))
Dim strLine As String
Dim strFields() As String
strLine = sr.ReadLine()
While (strLine <> "")
row = dt.NewRow
strFields = Split(strLine, ",")
For i As Integer = 0 To strFields.Length - 1
row.Item(i) = strFields(i).Replace("""", "")
Next
dt.Rows.Add(row)
strLine = sr.ReadLine()
End While
End Using
Return dt
End Function
■進捗状況を処理するイベントプロシージャ
プログレスバーを利用して、進捗状況を確認できるようにします。
この時、進捗状況を更新する為に利用するイベントプロシージャを作成します。
イベントが発生するタイミングは、後で説明する、SqlBulkCopyクラスのSqlRowsCopiedイベントです。
''' <summary>
''' 指定行数が処理されるごとに発生するイベント
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub OnSqlRowsCopied(ByVal sender As Object, _
ByVal e As System.Data.SqlClient.SqlRowsCopiedEventArgs)
''プログレスバーにコピーされる行数をセット
Me.pgbCopy.Value = CInt(e.RowsCopied)
End Sub
■実際の処理
Private Sub btnBulkCopy_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnBulkCopy.Click
Dim sw As Stopwatch = Stopwatch.StartNew
Me.Cursor = Cursors.WaitCursor
''バルクコピー
Using bc As New SqlBulkCopy("Data Source=.\SQLEXPRESS;" & _
"Initial Catalog=HogeHoge;" & _
"Integrated Security=True")
With bc
''コピー先テーブル名を指定
.DestinationTableName = "ZipCode"
''進捗状況イベント
AddHandler .SqlRowsCopied, AddressOf Me.OnSqlRowsCopied
''進捗状況イベント発生間隔の行数
.NotifyAfter = 100
''CSVをDataTableで取得
Dim dt As DataTable = GetZipCodeDataTable("C:\KEN_ALL.CSV")
''プログレスバーの最大値をセット
Me.pgbCopy.Maximum = dt.Rows.Count
''データテーブルをコピー先へ書き込み
.WriteToServer(dt)
End With
End Using
Me.Cursor = Cursors.Default
MessageBox.Show(String.Format("MyStopwatch:処理は {0} ミリ秒かかりました。", _
sw.ElapsedMilliseconds))
End Sub
■まとめ
全国郵便番号データは、約12万件あるので、上記の方法での処理速度であれば満足できる速度ではないかと思います。
