これまでの記事でAccessへExcelデータをインポートする方法を紹介しました。
これは、Accessの基本ツールを用いることで簡単にExcelデータをインポートする方法です。しかしながら、インポート元のデータに間違いや、ありえないデータがあってもデータ型さえ合っていればインポートしてしまいます。また、生データを基に条件に応じてデータを加工しながらAccessへデータインポートしたいといったニーズもあると思います。
そこで、今回はExcelデータを1件ずつ確認しながら条件に応じたインポート処理をする方法を紹介します。
サンプルデータについて
今回のサンプルデータはExcelで次のように作成します。「No.」、「姓」、「名」、「生年月日」及び「年齢」とします。「姓」、「名」は文字列として、「生年月日」は日付型、「No.」、「年齢」は整数型にします。なお、年齢の計算に際して現在日を「2020/4/19」としています。また、データのところどころにブランクの状態や実際の生年月日に合っていないデータがあると思いますが、これらは後に示すエラーチェックでログを取得するために、わざと間違ったデータを入れています。
■サンプルデータ
| No. | 姓 | 名 | 生年月日 | 年齢 |
| 1 | 佐藤 | 一郎 | 2000/4/10 | 20 |
| 2 | 山本 | 田吾作 | 1973/5/16 | 46 |
| 3 | 山田 | 1985/1/8 | 35 | |
| 4 | 美鈴 | 1994/7/1 | 25 | |
| 5 | 2010/12/5 | 9 | ||
| 6 | 鈴木 | 太郎 | 15 | |
| 7 | 安部 | 新次郎 | 2004/4/15 | |
| 8 | 大和田 | 花子 | 1981/7/3 | 39 |
| 9 | 島田 | 良治 | 1945/4/28 | -10 |
なお、Excelデータの作成においてタイトル行はExcelの1行目に「鈴木美鈴」のデータが6行目に、また「No.」はA列、以下E列までにデータを入力します。
また、作成したExcelファイルの名称は「TEST.xlsx」とし、Cドライブの直下（C:\TEST.xlsx）に保存します。
Accessファイルの準備
今回のデータインポートではADOを用いて、インポート先であるテーブルと取り込み時エラーのログを残すテーブルを作成します。なお、ADOについては以下の記事をご参照ください。今回もVBAの参照設定に「Microsoft ActiveX Data Objects Library」のチェックを入れています。
まずは、インポート先であるテーブルを次のように作成します。
■取り込み先テーブル
| フィールド名 | データ型 |
| No. | 数値型 |
| 氏名 | 短いテキスト |
| 生年月日 | 日付/時刻型 |
| 年齢 | 数値型 |
ここで、「氏名」フィールドはExcelの「姓」と「名」の2フィールドの情報を併せて登録することとします。また、年齢についてはExcelの「生年月日」から計算し、同「年齢」の値と比較する仕組みを設けます。
■エラーログテーブル
| フィールド名 | データ型 |
| No. | 数値型 |
| エラー内容 | 短いテキスト |
エラーログは、エラーのあった列に対する「No.」を取得し、エラー内容を登録するものとします。
データインポート時のエラーチェック
今回のケースでは、データインポートのためにAccessからExcelを起動し、Excelデータのレコード毎に妥当性等を確認し、異常がなければデータ取り込む仕組みとします。
まず考えられるエラーとしては次のものが挙げられます。
① 各フィールドがブランク
② 生年月日から起算して年齢が間違っている
③ 年齢が負の整数
その他にもエラー内容は色々と考えられると思いますが、今回はこれら3つのエラーチェックを行うものとします。そして、エラーがあった場合は、エラーログテーブルに当該データの「No.」とエラー内容を登録するようにします。そして、エラーがあった場合は正式なインポート処理を中止します。
なお、AccessからExcel操作をするため、ADOの参照設定をしたのと同様に、VBAの参照設定で「Microsoft Office XX.X Object Library」にチェックを入れてください。
エラーチェック後のデータインポート
エラーがなければデータをインポートする段階になります。ここで、AccessへはExcelの姓と名を半角スペースで繋いで「氏名」としインポートすることとします。
また、生年月日エラーや年齢が条件に合わない場合はインポートをしないようにします。それ以外の値については、そのままExcelの値をAccessの「取り込み先」テーブルへインポートします。
以上の操作を以下のソースコードで実装します。
ソースコードの作成
VBAを起動し、標準モジュールを作成して「Module1」とします。さらにプロシージャとして「Sub ListCheck」を追加します。
プロシージャを追加したらソースコードによる機能の実装をしていきます。
おおまかな流れとしては、ADOによる起動アクセス → Excelファイルの起動 → ループ文でデータチェック兼インポート → ADO及びExcel終了 → 処理完了 です。
これらの処理をVBAソースコードで表現すると次のようになります。
ソースコードのポイントはコメントを確認してください。
Public Sub ListCheck()
MsgBox "TEST.xlsxのインポートを開始します。"
'Excelファイルのパス設定
Dim FileSelect As string
FileSelect = "C:\TEST.xlsx"
'ADOによるデータベース接続のための変数宣言
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset '取り込み先用
Dim rse As New ADODB.Recordset 'エラーログ用
'カレントデートベースへの接続
Set cn = CurrentProject.Connection
'各テーブルを開く
rs.Open "取り込み先", cn, adOpenKeyset, adLockOptimistic
rse.Open "エラーログ", cn, adOpenKeyset, adLockOptimistic
'インポートするEXCELファイルを起動するための変数宣言
Dim xls As Object
Dim wkb As Object
Dim wks As Worksheet
'Excelの2行目からインポートする。
Dim i As Integer: i = 2
'生年月日から計算した年齢
Dim age As Integer
'TEST.xlsxの起動
'Excelでの処理
Set xls = CreateObject("Excel.application")
Set wkb = xls.Workbooks.Open(FileSelect)
With wkb
'Excelは不可視
.Application.Visible = False
.Activate
End With
'ワークシートのSheet1を開く
Set wks = wkb.Worksheets("Sheet1")
With wks
Do
rs.AddNew '№の登録
rs![№] = .Range("A" & i).Value '姓・名のチェック
If (.Range("B" & i).Value = "" Or IsNull(.Range("B" & i).Value)) And (.Range("C" & i).Value = "" Or IsNull(.Range("C" & i).Value)) Then
'エラーによりエラーログテーブルへの書き込みを行う。
rse.AddNew rse![№] = .Range("A" & i).Value
rse!エラー内容 = "姓・名ブランク"
rse.Update
ElseIf .Range("B" & i).Value = "" Or IsNull(.Range("B" & i).Value) Then
'エラーによりエラーログテーブルへの書き込みを行う。
rse.AddNew rse![№] = .Range("A" & i).Value
rse!エラー内容 = "姓ブランク"
rse.Update
ElseIf .Range("C" & i).Value = "" Or IsNull(.Range("C" & i).Value) Then
'エラーによりエラーログテーブルへの書き込みを行う。
rse.AddNew rse![№] = .Range("A" & i).Value
rse!エラー内容 = "名ブランク" rse.Update
Else rs!氏名 = .Range("B" & i).Value & " " & .Range("C" & i).Value
End If
'生年月日のチェック
If .Range("D" & i).Value = "" Or IsNull(.Range("D" & i).Value) Then
'エラーによりエラーログテーブルへの書き込みを行う。
rse.AddNew rse![№] = .Range("A" & i).Value
rse!エラー内容 = "生年月日ブランク"
rse.Update
Else
rs!生年月日 = .Range("D" & i).Value
End If
'年齢のチェック
age = CInt(IIf(Format(Date, "mmdd") < Format(.Range("D" & i).Value, "mmdd"), DateDiff("yyyy", .Range("D" & i).Value, Date) - 1, _ DateDiff("yyyy", .Range("D" & i).Value, Date)))
If .Range("E" & i).Value = "" Or IsNull(.Range("E" & i).Value) Then
'エラーによりエラーログテーブルへの書き込みを行う。
rse.AddNew rse![№] = .Range("A" & i).Value
rse!エラー内容 = "年齢ブランク"
rse.Update
ElseIf .Range("E" & i).Value < 0 Then
'エラーによりエラーログテーブルへの書き込みを行う。
rse.AddNew
rse![№] = .Range("A" & i).Value
rse!エラー内容 = "年齢マイナス"
rse.Update
ElseIf .Range("E" & i).Value <> age Then
'エラーによりエラーログテーブルへの書き込みを行う。
rse.AddNew
rse![№] = .Range("A" & i).Value
rse!エラー内容 = "年齢間違い"
rse.Update
Else
rs!年齢 = .Range("E" & i).Value
End If
'取り込み先テーブルの更新完了
rs.Update
'Excelにおいて次の行への移動
i = i + 1
'ループ処理は№の値が未記入の行になったら終了
Loop Until .Range("A" & i) = "" Or IsNull(.Range("A" & i))
End With
'テーブルを閉じる
rs.Close: Set rs = Nothing
rse.Close: Set rse = Nothing
cn.Close: Set cn = Nothing
'Excelを閉じる
wkb.Close savechanges:=False
Set wks = Nothing
Set wkb = Nothing
Set xls = Nothing
MsgBox "取り込み処理を終了しました。"
End Sub
インポート処理の結果
サンプルデータをインポート処理した結果を図1に示します。
図1に示すように、取り込み先テーブルにはExcelのデータがインポートされています。ただし、エラーデータについては、取り込みを中止しています。これらエラーについては、エラーログテーブルに情報を記載しています。
データがブランクだったもの、年齢が生年月日と比較して不整合なものについて、そのログを残しています。
このように、ADOを用いてExcelと連携をしてループ処理することでデータのチェック、生データを加工したインポートが可能となります。
まとめ
ExcelからAccessへのデータインポートに際して、データチェック及び生データに基づく加工データのインポート方法について紹介しました。
Accessはデータ型を保持しており、整合性を保持しながら処理をする点ではExcelよりも数倍有効となります。VBAによるデータ処理方法として紹介しましたが、どのような処理をしたいか、業務プロセスを考慮してソースコードを記載することで実務に近い形でのデータ処理が可能となり、結果として業務効率の改善が図れます。
是非、皆さんも今回の記事を参考として業務効率の改善を図っていただければと思います。
スポンサーリンク
コメント