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

2025年4月13日

当ページのリンクには広告が含まれています。

URLをコピーしました！

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

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

２．項目の対応表を編集・確認する

ここもポイントです。

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

変換マスタテーブルのテーブル構成

本フィールド名：　目的テーブルの項目名

フィールド型：　目的テーブルの項目の型

代替フィールド名：元データファイルの対応するフィールド名

元データサンプル：元データファイルの対応するフィールドのサンプルデータ

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

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

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

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

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

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

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

'   処理内容:変換マスタに則って元データを本テーブルに格納する
'   引数:
'   戻り値:
'------------------------------------------------------------
Private Sub setData()
    Dim myDb        As Database
    Dim myRs        As Recordset
    Dim sql_insert  As String
    Dim sql_select  As String
    Dim strsql      As String
    
    On Error GoTo Err_Exit
    
    Set myDb = CurrentDb
    
    '一旦作成先のデータを削除する
    myDb.Execute "DELETE * FROM 目的テーブル"
    
    Set myRs = myDb.OpenRecordset("データ変換マスタ", dbReadOnly)
    
    'INSERT句用の文字列とSELECT句用の文字列定義
    sql_insert = ""
    sql_select = ""
    
    
    '変換マスタを読み出す
    With myRs
        If Not .EOF Then
            .MoveFirst
            While Not .EOF
                'INSERT句用のフィールド名文字列
                If sql_insert <> "" Then
                    sql_insert = sql_insert & ", "
                End If
                sql_insert = sql_insert & .Fields("本フィールド名")
                'SELECT句用のフィールド名文字列
                If sql_select <> "" Then
                    sql_select = sql_select & ", "
                End If
                sql_select = sql_select & .Fields("代替フィールド名")
                .MoveNext
            Wend
        End If
    End With
    
    'すべての文字列を結合し、INSERT文を作成する
    strsql = "INSERT INTO 目的テーブル (" & sql_insert & ") " & vbCrLf & _
            "SELECT " & sql_select & " FROM インポートデータ"
    'INSERT実行
    myDb.Execute strsql
    
    Set myRs = Nothing: Close
    Set myDb = Nothing: Close
    
    Exit Sub
    
Err_Exit:
    MsgBox Err.Number & ":" & Err.Description, vbOKOnly + vbCritical, "setMailData()"
    Set myRs = Nothing: Close
    Set myDb = Nothing: Close

End Sub

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