Access

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

不定形ファイル

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

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

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

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

具体的な例

例えば、顧客データ。

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

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

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

名前
ふりがな
メール
TEL1
TEL2

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

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

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

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

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

実装方法

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

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

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

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

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

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

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

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

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

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

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

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

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

2.項目の対応表を編集・確認する

ここもポイントです。

目的テーブルのフィールド名を持ったテーブルをあらかじめ用意しておきます(以降、変換マスタテーブルと呼びます)。

変換マスタテーブルをレコードソースとした以下のような帳票フォームを作成します。

変換フォームのサンプル

代替フィールドはコンボボックスにしておきます。

データソースは1.で作成した元データフィールド定義テーブルです。

ユーザーに項目名の紐付けを定義してもらいます。

3.2のルールに従ってデータを目的テーブルに格納する

以下のSQL文を作成し、実行することで元データフィールド定義テーブルに従って元データファイルのデータ目的テーブルにINSERTすることができます。

INSERT INTO 目的テーブル (氏名,フリガナ,メールアドレス,自宅電話番号,携帯電話番号)
SELECT 名前,ふりがな,メール,TEL1,TEL2 FROM インポートテーブル

直接格納できないケースもあります。

  • 名前という項目に格納したいが、元デーファイルでは姓と名の2項目となっている場合
  • 項目の型が目的テーブルの型と一致しない場合(例)生年月日
  • その他

このようなケースの場合は別途対策が必要ですが、本記事では直接格納できるケースを想定しています。

まとめ

いかがでしたか?
テーブルがたくさん出てきてちょっとややこしいかもしれませんが、一度作っておくと応用が効くと思います。

ABOUT ME
アズビーパートナーズ
アズビーパートナーズ
プログラマーと社内SEとしての経験を活かして、 情報システム部門のご相談を承ります。 得意な分野はAccessによる短納期開発、 BIツール(QlikSense/QlikView)の開発です。 現在はCMSを利用したホームページの作成にも力を入れています。