| ホーム | 掲示板 | 特集 | サンプル | Tips | リンク | その他 | blog 

Tips
[VS2003]
 日付時刻の演算
 ListBoxにファイル一覧
 ファンクションF10
 WMIでサービス一覧
 YAHOO検索エンジン
[VS2005]
 Administrator権限チェック
 非同期ExecuteReader
 非同期ExecuteReader(CallBack)
 パスの結合
 ファイルを圧縮して保存
 コントロール配列の実装
 DataGridViewのCellイベント
 CSVファイルをDataGridViewに表示
 DataGridViewのリンクセルを見やすく
 DataGridViewマウス右ボタンで選択
 DataGridViewスクロール制御
 DataGridViewのCell移動をEnterで
 DataRepeaterコントロール
 GZ圧縮ファイルを解凍
 ファイルの削除
 ディレクトリ有無チェック
 ファイルのダウンロード
 非同期ファイルダウンロード
 ドライブの一覧
 ファイルのコピー
 ファイル有無チェック
 テキストファイルを読み込む
 特定の文字列を含むファイル検索
 フォームを表示(Show)する
 フォルダ一覧取得
 ドライブ詳細情報取得
 ファイル一覧取得
 テンポラリファイルパス取得
 継承コントロールの作り方
 フォルダの移動
 ファイルの移動
 My.Settingの利用
 MySQLCommandで抽出
 MySQLDeleteCommandで削除
 MySQLInsertCommandで挿入
 MySQLSelectCommandで抽出
 MySQLUpdateCommandで更新
 ネットワークに繋がってる?
 ネットワーク接続イベント取得
 固定長テキストファイルを読む
 Oracleストアドファンクション呼出1
 Oracleストアドファンクション呼出2
 OracleのLong Row型の画像を表示
 OracleのLong Row型をファイル保存
 Oracleパラメータクエリ実行
 ORACLE接続文字列生成
 VS2005でPing
 非同期でPing
 VB6のPrinterオブジェクトを.NETで
 Reportでテーブル形式表示
 ReportViewerでパラメータ設定
 メール送信
 非同期でメール送信
 メール送信(MailMessage利用)
 シリアル通信プログラム
 シリアル通信(文字列送信)
 二重起動の禁止
 特殊フォルダ取得
 スプラッシュウインドウ表示
 テーブルへ複数行の挿入
 CSVファイルをテーブルへ挿入
 テーブルへ複数行の挿入(マッピング)
 SqlBulkCopyで郵便番号データ処理
 SQL Server接続文字列生成
 Stopwatchで時間の計測
 システムサウンド再生
 CSVファイル読み込み
 固定長テキストファイルを読む2
 TableAdapterでTransaction
[VS2005] SqlBulkCopyで郵便番号データ処理 2006/07/24
日本郵政公社より提供されている全国郵便番号データを業務で利用する事は多いと思います。
でも実際利用する場合には、提供されているCSVそのままを利用する事はなく、一般的には、DBにインポート(コピー)して利用しますよね。
この、CSVインポートをSystem.Data.SqlClient.BulkCopyを利用して高速に行う方法を説明します。
Tipsのテーブルへ複数行の挿入でも説明しているのですが、ここで説明するのは、あくまで"全国郵便番号データ"をインポートする場合の処理です。
 
■前提
  1. 全国郵便番号データ全国一括データ(KEN_ALL.CSV)を対象としています。
  2. インポート(コピー)先は、SQL Server(2005 EXPRESS)
  3. インポート(コピー)先のデータへ追加するだけで、重複などは考慮しない
 
■インポート(コピー)先のテーブル概要
CREATE TABLE [dbo].[ZipCode](
	[全国地方公共団体コード] [int] NOT NULL,
	[郵便番号5] [varchar](5COLLATE Japanese_CI_AS NULL,
	[郵便番号7] [varchar](7COLLATE Japanese_CI_AS NOT NULL,
	[都道府県名カナ] [varchar](100COLLATE Japanese_CI_AS NULL,
	[市区町村名カナ] [varchar](100COLLATE Japanese_CI_AS NULL,
	[町域名カナ] [varchar](100COLLATE Japanese_CI_AS NULL,
	[都道府県名] [varchar](100COLLATE Japanese_CI_AS NULL,
	[市区町村名] [varchar](100COLLATE Japanese_CI_AS NULL,
	[町域名] [varchar](100COLLATE Japanese_CI_AS NULL,
	[FLG1] [smallint] NULL,
	[FLG2] [smallint] NULL,
	[FLG3] [smallint] NULL,
	[FLG4] [smallint] NULL,
	[FLG5] [smallint] NULL,
	[FLG6] [smallint] NULLON [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 StringAs 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万件あるので、上記の方法での処理速度であれば満足できる速度ではないかと思います。

Copyright © 2005 tadahiro higuchi. All Rights Reserved.