不定型のデータファイル（Excelなど）を定形のテーブルに格納する方法

Accessで開発していてたまに遭遇する案件です。

「どんな項目名で元データファイルが届くか分からないけれども、決まったテーブルにデータを格納したい。」というニーズです。

もちろん、格納することがゴールではなくそれぞれ後工程がありますが、まずはデータを整形しないことには次の処理に進めません。

今回は、不定形データを定型のテーブルに格納するACCESS VBAのプログラムをご紹介します。

具体的な例

例えば、顧客データ。

最終的に格納したいテーブル（以降目的テーブルと呼びます）の項目名が以下のとき、

氏名
フリガナ
メールアドレス
自宅電話番号
携帯電話番号

いただいた不定形のデータファイル（以降元データファイルと呼びます）の項目名がこのようになっている場合

名前
ふりがな
メール
TEL1
TEL2

最終的には目的テーブルに元データファイルの該当するフィールドからデータをセットします。

氏名　　　　　　　←　名前
フリガナ　　　　　←　ふりがな
メールアドレス　　←　メール
自宅電話番号　　　←　TEL1
携帯電話番号　　　←　TEL2

元データファイルの出処が複数あり、それが別々の業者や客先である場合、必ずしもこちらの指定する形式でデータをいただけないことがあります。

不定形のファイルが１つであれば手動で対応すれば済みますが、あれこれ色んなパターンでデータがやってくるともう大変です。

そこで、どんな項目名で元データファイルが届いても目的テーブルの形に整形する仕組みを作成することにします。

実装方法

前提条件として元データファイルは１行目に項目名があるものとします。
また、その項目名には重複がないことも合わせてご確認ください。

ユーザーの操作が必要なトリガーは以下の３点です。

元データファイルをインポートする
項目の対応表を編集・確認する
２のルールに従ってデータを目的テーブルに格納する

元データファイルをインポートする

後処理で利用するので、インポート先のテーブル名は固定にします。（以降インポートテーブルと呼びます）

①インポートする元データファイルの形式は不定型ですから、インポートする前にインポート先のテーブルが存在していた場合はDROPしておきます。

VBAでテーブルの存在チェックをする関数一時テーブル作成前、インポート前などあらかじめこれから作成する予定のテーブルが存在するかどうかチェックする際に使える関数です。コピペでどうぞ。...

②インポートを実行します。

③元データファイルの項目名と1行目のデータ（サンプルデータとして）を元データフィールド定義テーブルに格納します。（ここがポイント！）

元データフィールド定義テーブル構成

フィールド名：インポートテーブルの各項目名

サンプルデータ：1行目の生データ

コードサンプルは以下の通りです。

インポートしたテーブルをmyRsにセットし、myRsのフィールド名をループしながら取得します。

また、取得したフィールド名の値もついでにサンプルデータとして取得していきます。

set myDb = CurrentDb

'インポートテーブルを開く
Set myRs = myDb.OpenRecordset("インポートテーブル")
Set tRs = myDb.OpenRecordset("元データフィールド定義テーブル")

For Each fld In myRs.Fields
    'フィールド名レコードを登録する
    tRs.AddNew
    tRs.Fields("フィールド名") = fld.Name
    tRs.Fields("サンプルデータ") = CStr(Nz(myRs.Fields(fld.Name)))
    tRs.Update
Next fld

※エラー処理や解放処理など割愛してますので、適宜追加してくださいね！