【初心者向け】GISデータを題材にAccess+VBAでのデータ読込みのサンプル

この記事の背景

　私は本来、プログラム作成は関係ない仕事なのだが、ここ数年、割り振られた仕事をこなすためにはプログラムを作らなければならない状況にいる。それも基幹系システムと部門システム間の台帳連携とか、職員が片手間で作るようなレベルとは考えにくいレベルのものを作っている。
　職場のIT環境は、システム管理部門が「マクロは後から業務がわかりにくくなるので使うべきではない」とか言っててAccessも「データベースは難しいから使うな」というような状況なんですね。
　IT研修もITリテラシなんていうレベルではなく、OAリーダにも「パソコンに触ってみた」レベルで終わってる。
　内部がこういう状況であっても、外部環境はITシステムを前提にしたものに変わってきているのです。
　で、前任者まではExcelで手作業で行っていた業務を、何とか自前でAccess+VBAでシステム化してみてノウハウみたいなものをまとめてみたのが、この記事です。

どのような業務を対象としているのか

　業務システム間のデータ連係を行う際、データ加工が必要な状況を想定しています。
　業務システムＡから出力されたデータ（CSVファイルが一般的か）を業務システムＢに登録する必要があるが、そのままでは登録できず、項目を入れ替えたり、データを変形したりということはよくあります。
　おそらくEUC(End User Computing)とかいって、職員がこういう業務を行うとき一般的にはExcelで読込、加工を行うことが多いと思いますが、これを自動化する前提としてAccessに取り込むアイデアを提供します。

題材「Geolonia 住所データ」

　『無料で使える「住所マスターデータ」公開、表記統一や緯度経度への変換に活用可能』（安達 崇徳著、INTERNET Watch、2020年8月20日）で紹介された株式会社Geoloniaと一般社団法人不動産テック協会がオープンデータとして公開している、日本全国の町丁目レベル18万9540件の住所データと代表点の緯度経度のデータなどを記録した「Geolonia 住所データ」を題材にします。
　自組織内で住所マスターが整備されているなら、このデータと結合することでGISで業務データを利用することも可能になります。

具体的な方法について

　「Geolonia 住所データ」を業務データだと想定してAccessに取り込む手順は、次のとおりです。
　 読み込むデータの構造を調べる→テーブルを作成する→データを読み込む
　データベースに取り込んでしまえば、後はクエリなりVBAなりで自由に加工できます。

なぜAccessを使うのか

　私が職場で使えるプログラミング環境がAccess+VBAしかない、というのが理由です。Accessはデスクトップ・データベースソフトと言われ、データベースとVBAを密接に関連させて簡単に利用することができます。
　また、MS Office Proがインストールされているパソコンが一般的だろうし、古い言語仕様とはいえ本格的なプログラミングが可能なVBAを使えると意外と高度な処理も可能になります。
　視点を代え、Excelで行わない理由を説明すると、EXcelでは大量のデータを効率的に扱えないこと、データ加工を行う際、データを論理的な塊で処理するのではなく、ワークシートへの操作として行うためプログラムが大変わかりにくくなるためです。
　また、Accessでプログラムを作成して処理する理由は、作業手順の再現性・作業の正確性の向上を確保し、後から作業が正しく行われたことを監査できるようにするため、次に作業効率を向上させるためです。

データ定義を調べる

　まず、最初に行うことは、加工するデータの構造を理解することです。
　題材のデータは配布しているサイトに、次のようにファイルフォーマットが公開されています。

　緯度・経度は小数になるので、実数として扱うこととします。
　緯度・経度以外の項目はすべて文字列として扱います。というのも、都道府県コード・市区町村コードは桁に意味があるため数値化すると、利用が困難になるからです（市区町村コードの左2桁は都道府県を表す）。
　Excelで、次のようにまとめました（サイトからコピペでワークシートに貼り付けて適宜加工する）。

　この表はAccessデータベースからリンクして利用します。
　データベースの中に表定義のためのテーブルを作成しても良いのですが、Excelを使うと人間用の資料作成とテーブル定義上をまとめて管理できるため便利です。
　というのも、原則としてリンクした際、ワークシートのセルの内容は読み込まれますが、ワークシートに貼り付けたテキストボックスやイラストは読み込まれません。また、表定義している領域を名前で管理しリンクすることで、ワークシートにも自由に情報を入力でき、わかりやすい資料を作成できます。

##テーブル定義情報をリンクする
　バージョンによって見た目は変わりますが、やることはほぼ同じです（職場のAccess2013と同じでした）。
　私はMicrosoft 365 Personalを使用しているので、外部データへのリンクは次のようなメニューになっています。

　Excelファイルを選ぶと、つぎのようなダイアログが開くので作成したワークブックを指定します。
　
　今回は表の定義情報の領域に名前をつけているので、それをリンクしました。

　これが済むと、Accessのテーブルとして利用できます。

テーブル作成のプログラムを作る

　続いて定義情報を元にテーブルを作成するプログラムを作ります。
　この程度の項目数なら、わざわざプログラムを作る必要は無いが、何回も項目定義を変更したり、項目数が多かったりするとプログラムにしておく良さを実感できる。
　仕事で項目数500近いCSVファイルを処理する必要があった。実際に必要なのは、そのうちの50くらいの項目なんだけど、似たような名称で沢山項目があると正確に項目名を入力するだけでもストレスになってくる。こういう場合は、送られてくるファイルの定義情報を入手し、必要な項目でテーブルを作成するプログラムを組んだ方が精神衛生上良かった。
　で、今回のプログラムは次のとおり。

Option Compare Database
Option Explicit

Sub makeTblAddress2Point()
    Dim dbTmp As DAO.Database
    Dim tblDef As DAO.Recordset             '定義情報のあるテーブル（Define）
    Dim tdfTmp As DAO.TableDef
    
    '項目定義に必要な変数（field）
    Dim fldName As String                   '項目名
    Dim fldType As String
    Dim fldLength As Long
    Dim fldInit As Variant                  'nullが戻る可能性がある他、値が予測できないので。
    
    Dim fldIndex As Long                    '項目を番号で管理するための変数
    
    
    'データベース処理開始
    Set dbTmp = CurrentDb
    Set tblDef = dbTmp.OpenRecordset("住所データ定義")
    Set tdfTmp = dbTmp.CreateTableDef("住所テーブル")   '新しいテーブル定義を開始する（テーブルを作成する準備）。
    
    Do
    'テーブルの定義表にある項目を追加していく。
        '項目情報を取得する。
        fldName = tblDef.Fields("項目名").Value
        fldType = tblDef.Fields("データ型").Value
        fldLength = Nz(tblDef.Fields("データ長").Value, 0)  '文字列型のみ長さが返る。
        fldInit = tblDef.Fields("初期値").Value             '大抵はnullが返る。
        
        
        'テーブル定義開始
        'データ型によって定義が必要な内容が違う。
        '
        If fldType = "文字列型" Then
        '文字列は文字列の長さの設定が必要
            tdfTmp.Fields.Append tdfTmp.CreateField(fldName, dbText, fldLength)
        ElseIf fldType = "長整数型" Then
            tdfTmp.Fields.Append tdfTmp.CreateField(fldName, dbLong)
            tdfTmp.Fields(fldName).DefaultValue = fldInit   '初期値設定
        ElseIf fldType = "倍精度型" Then
            tdfTmp.Fields.Append tdfTmp.CreateField(fldName, dbDouble)
            tdfTmp.Fields(fldName).DefaultValue = fldInit   '初期値設定
        End If
        
        'NUllを許容させる。
        tdfTmp.Fields(fldName).AllowZeroLength = True
        
        tblDef.MoveNext
    Loop Until tblDef.EOF
    
    '作成したテーブルをデータベースに追加する。
    dbTmp.TableDefs.Append tdfTmp
    
Wind_UP:
    tblDef.Close
    dbTmp.Close
    MsgBox "END"
    
    
End Sub

　関数名と作成するテーブル名は一致させたほうがわかりやすいと思う。
　特段、高度なことはしていないので、DAOのドキュメントを参照のこと。
　なお、処理ごとにモジュールを分け、人間がわかる言葉で名前をつけておいた方が良い。
　今回は、表定義・データの読込でモジュールを分けている。

データ読み込みのプログラムを作る

　そしてデータを読み込むためのプログラムを作る。
　今回のデータは漢字を含むutf-8のテキストファイルとしてダウンロードできる。さらに改行文字はLFのみ。
　このままではVBA標準のファイル処理関数やテキスト処理関数等では、正しく処理できない。
　でも、ADOを利用するという方法が準備されている。
　プログラム前の準備として、Accessに併せて適切なライブラリを参照する（事前バインディング、下の図参照）。
　
　参照を行わず実行時にオブジェクトを定義する遅延バインディングという方法もあるが、インテリセンスの支援を受けられないのでコーディングが難しくなる。
　作成したプログラムは次のとおり。
　処理としては、一行毎に読込み、「,」を区切り文字として分割し配列に読み込んでいる。こうすることでいちいち項目名を記述せずループ処理でデータを登録できる。この方法の利点として、行毎に項目数が変わる場合に対応できることがあげられる。

Sub loadAdressData(inFileName As String)
'引数で与えられたファイル名のファイルを住所データと見なして読み込む
'DAOでレコードを追加する。
'
    Dim inFileStream As ADODB.Stream                    'ADOを利用する
    
    'データベース処理に必要な変数
    Dim dbTmp As DAO.Database
    Dim tblDist As DAO.Recordset
    Dim retTmp As Variant                               'どの項目もnullが返りかねない。
    
    'テキスト読込処理に必要な変数
    Dim tmpStr As String
    Dim tmpArray As Variant                             '配列に変換するための変数
    
    Dim fldIndex As Long
    
    'データベースを開く。
    Set dbTmp = CurrentDb
    Set tblDist = dbTmp.OpenRecordset("住所テーブル")
    
    'ADOでテキスト読み込む処理
    Set inFileStream = New ADODB.Stream
    
    With inFileStream
        .Open
        .Charset = "utf-8"
        .LineSeparator = adLF
        .Type = adTypeText
        .LoadFromFile inFileName
    
        '将来、項目行が必要になるかもしれないので明示的に読み飛ばしておく。
        tmpStr = .ReadText(adReadLine)
        
        'データ本体を読む。
        
        Do
            tblDist.AddNew
            '二重引用符が邪魔なので読み込んだら消しておく。
            tmpStr = .ReadText(adReadLine)
            tmpStr = Replace(tmpStr, """", "")
            
            '項目の値を読み込む
            tmpArray = Split(tmpStr, ",", , vbBinaryCompare) 'バイナリを指定すること。
            
            For fldIndex = 0 To UBound(tmpArray)
                '配列から直接テーブル登録しても良いが、何か処理することを考えた。
                retTmp = tmpArray(fldIndex)
                
                'データ型によって登録処理を変える必要がある。
                
                
                If tblDist.Fields(fldIndex).Type = dbText Then
                '文字列型の項目
                    tblDist.Fields(fldIndex).Value = Nz(retTmp, "")
                ElseIf tblDist.Fields(fldIndex).Type = dbDouble Then
                    tblDist.Fields(fldIndex).Value = Nz(retTmp, 0)
                End If
                
            Next
            'レコード追加処理
            tblDist.Update
        Loop Until inFileStream.EOS
        
        .Close
    End With


Wind_UP:
    tblDist.Close
    dbTmp.Close
    MsgBox "End"
End Sub

　このプロシージャでは読み込むファイルをフルパスで指定する必要がある。
　しかし、実際に業務で使うとき、ファイルをいつも同じところに保存して同じ名前にするといった運用は考えにくい。
　ダイアログで読み込むファイルを指定できた方が良い。
　ということで、ファイル選択をするプロシージャも作成しておく。

Sub selectAdressDataFile()
    Dim inFileName As String
    
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False   'ファイルを一つ選択する。
        .InitialFileName = "d:\accessdb\"
        .Filters.Add "CSV Files", "*.csv", 1
        
        If .Show Then
            inFileName = .SelectedItems(1)
            loadAdressData (inFileName)
        End If
        
    End With
End Sub

完成したデータベース

　操作用のフォームも作っておく。
　できたデータベースはこんな感じ。

「住所データを登録する」ボタンのイベントプロシージャは次のとおり。

Private Sub cmdLoadAdressData_Click()
'住所データを登録する。
'
'

    '登録済みのデータを削除する。
    DoCmd.OpenQuery "qryInittblAdress", acViewNormal
    'データを登録する。
    selectAdressDataFile

End Sub

　データを読み込む前に、テーブル「住所データ」に登録済みのデータを削除するクエリを実行する。

その他・まとめ

　こうやってデータベースを作り、データを読み込んでしまえば、好きにデータ加工を行える。
　特にRDBMSではあるテーブルを元に、他のテーブルのレコードを紐付ける外部結合処理が簡単かつ正確に行えるので、Excelなんかと比較にならないぐらい効率的にデータ加工を行える。
　プログラマの人たちにとっては、初歩的な内容だと思うけど、普通の事務をしている人が大量のデータ加工を担当することになったとき、少しでも参考になったら幸いです。

・・・

　今回、書き込んだ方法をベースに可変項目数のデータファイルを読み込んで実績を集計するプログラムとか組んでるんだけど、これってEUCのレベルなんですかね・・・・。