Access VBAでExcel・CSVデータをインポートする

Accessである程度VBAを使って組んでいくと必ずぶつかる壁が「Excelデータのインポート」です。もともとAccess本体にもインポート機能はついていますが、毎回インポートを手動で行わないといけない点と、細かなデータの変換や条件判定などを含めると素直に使えないシーンも多いです。

今回はAccessでExcelデータインポート方法をいくつか紹介したいと思います。利用頻度は少ないかもしれませんが、システム構築上は絶対に必要になるものなので、その周辺機能まで含めて公開します。

今回使用するデーターベースファイル等

今回のAccessには2種類のテーブルを用意してあります。passionテーブルはID列が数値型なのでExcelのID列まで含めてインポート可能。passion2テーブルはID列がオートナンバー型なのでCSVでインポート定義をする事でID列だけ飛ばしてインポート可能です。

Access本体でインポート

Excelファイルをインポートする

Access本体機能を使ってのインポート手順です。手動でExcelファイルを選び、インポート先のテーブルを指定し取り込むだけなのですが、インポート元とインポート先のテーブル構成は同じである必要性があるのと、フィールドの型も一致させておく必要があるので注意です。主な用途は定型的なデータの取り込みが殆ど。細かな調整は出来ないのでテーブルにInsertするだけの機能になります。

※Excelの場合インポート定義が無いので注意が必要です。データの型をしっかり合わせておく必要があります。

  1. passionテーブルimportsheet.xlsxpassionシートのデータをそのまま取り込みます。
  2. 外部データタブを開き、インポートとリンクの中にあるExcelのアイコンをクリックする
  3. ダイアログが出てくるので、参照をクリックし、importsheet.xlsxを指定します。
  4. レコードのコピーを次のテーブルに追加する」にて、Access内のpassionテーブルを指定する(今回はpassionテーブル
  5. OKボタンをクリックして次に進む
  6. Excelの中身がプレビューされる。そのまま次へ進むをクリックする
  7. 完了をクリックして終了

図:テーブルに取り込むダイアログ

CSVファイルをインポートする

Excelファイル以上に利用頻度が高いのがCSVファイルのインポート。他のシステムから吐き出したデータをAccessや連携先のデータベースファイルなどに取り込む為に非常によく使います。Excelと違ってcsvファイルの場合は、インポート定義の処理があります。この定義は取っておく事ができます。取り込み手順は以下の通り。

  1. passionテーブルimportsheet.csvのデータをそのまま取り込みます。
  2. 外部データタブを開き、インポートとリンクの中にあるテキストファイルをクリックします。
  3. ダイアログが出てくるので、参照をクリックし、importsheet.csvを指定します。
  4. レコードのコピーを次のテーブルに追加する」にて、Access内のpassionテーブルを指定する(今回はpassion2テーブル)
  5. 区切り記号はデフォルトの「区切り記号付き」のままでOK
  6. 次へをクリックします。
  7. 先頭行をフィールドとして使うにチェックを入れます(csvに先頭行のデータが入ってる場合)
  8. ここで設定をクリックします。
  9. インポート定義の画面が出てきます。
  10. 保存ボタン押すとこの定義を取っておく事が可能です。新規にテーブルとして取り込む場合は、データ型の指定やインデックスの指定などがフィールドの情報に出てきます。(今回はImportsheet インポート定義として名前を付けました)
  11. インポートをスキップしたい列はチェックを入れるとスルーしてくれます。今回はID列がオートナンバーであるので、チェックを入れます。
  12. OKボタンをクリックして、次へクリックするとインポートが始まります。
  13. 完了をクリックするとインポートが完了
  14. データ型などが不一致だったりした場合には、失敗したメッセージとその内容が表示されることがあります。
  15. サンプルでは予め作っておいたインポート定義確認というクエリで作成済みインポート定義の状態を確認できます。

図:CSVはインポート手順が多い

図:インポート定義画面

VBAでインポート

手動でインポートはあくまでもインポート元とインポート先がガッチリフィールドと型が一致している場合には使えます。しかし、実務の世界では以下のような問題点が常にあります。

  1. インポート元のデータとインポート先のテーブルが一致していない事は非常に多い
  2. また、テーブルの型も一致していない事も頻繁にある
  3. そもそも、インポート元はコードで、インポート先はコード名である事が多い(商品IDしかなく商品名しか相手にも無い)
  4. インポート元は限られた情報しかなく、インポート先でデータを補完してあげる必要がある
  5. インポート元のコードとインポート先でのコード変換が必要

などなど。これらはVBAを使ってDAOなどで処理をしなければインポートする事は出来ません。

Docmd.Transferを使った事例

VBAで手動でインポートと同じ事を行いたい場合に用意されてるAccess専用のメソッドです。Excelファイルの場合にはDocmd.TransferSpreadsheet、CSVファイルの場合にはDocmd.TransferTextがそれぞれ用意されています。ただし細かい指定は出来ないので一括連続取り込みといった用途などで利用します。ファイル名の指定が必要なので、FileDialogなどを組み合わせて処理をする必要があります。

Excelファイルをインポートする

Excelファイルをインポートする場合に利用します。

  • FileDialogでファイルを指定してパスを取得させています。
  • Scripting Runtimeにて初期フォルダをデスクトップに指定させています
  • ファイルが選択されるとstrpathに対象のxlsxファイルのパスが格納されます。
  • Docmd.TransferSpreadsheetにて取り込みます。構文は以下の通り。
  • ただしこの構文を使ったケースの場合、テーブル側のIDがオートナンバーで、尚且Excelファイル側にもID列がある場合、インポートがエラーになります(オートナンバー列はインポートが出来ないため)
  • サンプルファイル内では、リボンから呼び出してるので、関数の引数にcontrol As IRibbonControlが入っていますが、通常は不要です。

構文は以下のような感じです。

  • Excelのバージョン指定はacSpreadsheetTypeExcel9みたいに指定しますが、省略可能
  • 先頭行を列名にする時はtrueの値を入れておくと、Excelの1行目はフィールド名と判断します
  • 取り込みシート名はシート名!という具合に「」を最後に指定します

CSVファイルをインポートする

CSVファイル等をインポートする場合に利用します。予め作成しておいたインポート定義を利用する事も可能です。

  • FileDialogでファイルを指定してパスを取得させています。
  • Scripting Runtimeにて初期フォルダをデスクトップに指定させています
  • ファイルが選択されるとstrpathに対象のcsvファイルのパスが格納されます。
  • Docmd.TransferTextにて取り込みます。構文は以下の通り。
  • ただしこの構文を使ったケースの場合、テーブル側のIDがオートナンバーで、尚且Excelファイル側にもID列がある場合、インポートがエラーになります(オートナンバー列はインポートが出来ないため)。なので、インポート定義を使ってID列をスキップする設定を利用しています。
  • サンプルファイル内では、リボンから呼び出してるので、関数の引数にcontrol As IRibbonControlが入っていますが、通常は不要です。

構文は以下のとおりです。

  • acImportDelimでカンマ区切りでインポートします。
  • インポート定義名の指定は省略可能です。今回は対象のテーブルがオートナンバーなので、インポート定義でID列をスキップする設定が入っています。
  • 先頭行を列名にする時はtrueの値を入れておくと、csvの1行目はフィールド名と判断します

リンクテーブルを使った事例

これまでの手法はただ単純に同じ形式のファイルを同じ形式のテーブルにただ流し込むだけで、非常に自由度がない構文です。実用できるシーンは割と限られてきます。今回提示した問題点をクリアするにはこの構文では力不足です。そこでExcelやCSVファイルに対して、リンクテーブルを貼り、クエリ上やVBA上で処理を施す事で自由度の高いインポートを自動化する事が可能になります。いくつかの注意点もあるので、それらを踏まえて構築する必要があります。

今回リンクテーブルの名前はm_passionという名前で作成します。

クエリを利用した方法

コードを作成する前に、Excelファイルへのリンクテーブルを作成しておき、作成したリンクテーブルからpassionテーブルへの「追加クエリ」を用意しておいて、インポートする方法です。追加クエリの名前は「xlsxデータ追加」という名称で作ってあります。ただし注意点として、

  • 作業後にはリンクテーブルは必ず削除し残さないようにしないと、次回リンクテーブル作成時にテーブル名がバッティングしてしまいます。
  • データの変換や追加等などの細かい作業はクエリ内のフィールドで行わせるようにします。
  • ID列がオートナンバーであるならば、クエリ内でID列はインポート対象から外しておきます。

  • FileDialogで取得したファイルパスに対して、Docmd.TransferSpreadsheet acLinkにてリンクテーブルを作成します。
  • 取り込みシート名はシート名!という具合に「」を最後に指定します
  • リンクで作成するテーブル名としてm_passionを指定する
  • Docmd.Setwarningをtrueにすると、クエリ追加時の問い合わせをオフにすることが可能です。必ず処理が終わったらfalseにしましょう。
  • リンクテーブルを作成したら、Docmd.OpenQueryにて、予め作っておいたxlsxデータ追加の追加クエリを実行します。
  • 最後の処理としてDocmd.DeleteObjectにてm_passionのリンクテーブルを削除しておきます。

図:今回は極簡単な追加クエリにしてあります

DAOを利用した方法

クエリを使用したリンクテーブルからのインポートはお手軽なのですが、クエリ内で色々と変換や追加の処理を記述しなければならないので、メンテナンス性では劣ります。また複雑な変換処理等をさせた場合には何をしているのか見通しも悪くなります。そこで、リンクテーブルに対してDAOを利用して非常に細かく追加処理を行う事が可能です。また、SQL文を直接発行させる事も可能なので、人によっては非常に管理のしやすいコードになります。

  • まずはDocmd.TransferSpreadsheet acLinkにてリンクテーブルを作成しておきます。
  • DAOのデータベース接続用変数を定義して、インポート先のpassionテーブルとインポート元のm_passionテーブルをレコードセットとしてセットします。
  • rstに対してDo Until rst.EOFでレコードの最初から最後まで回しつつ、rsに対してAddNewにて追加します。必ずrst.MoveNextを入れないと無限ループになるので注意。
  • AddNewの部分でrst!フィールド名でインポート元テーブルの値を取得しますが、この時に様々な変換処理を行うと良いでしょう。
  • 最後にDocmd.DeleteObjectにてリンクテーブルを削除しておきます。
  • また、dbs.closenothingにて利用した変数をクリアしておきましょう。

SQLを利用する場合

今回のサンプルでは、DAOを使ってのインポートは含まれています。しかし今回の方法以外にも、コードの中でSQLを実行してインサートする方法もあります。

  • strSQLに対してSQL文を構築します。
  • db.Execute SQLにてSQLを実行します。

オートナンバーを初期化する

VBA等でExcelデータのインポートを続けていくと、次第にID列等オートナンバー型を指定してる列の数値が大きくなったり、またその値を元に何か処理をしているケースの場合、リセットをしないと二度同じナンバーは振られない為、どんどん数値が大きくなっていきます。通常ID列はそれで問題ないのですが、時としてこのオートナンバーを初期化して、また1から始めたいケースがあります。

そんな時は以下のようなコードを利用して、インポート前にテーブルデータを全削除してから初期化を実行すると、次回のインポートからはまた、1からスタートする事が可能です。

  • 必ずまず、対象のテーブルのデータは全削除する必要があります。
  • Alter Tableにて構造変更し、defnumの値からオートナンバーを開始するSQLを実行する
  • リンクテーブルに対してはAlter Tableは発行出来ないので、その場合は対象のリンクテーブルを削除⇒対象のDB内のテーブルに対してAlter Table⇒再度リンクテーブルを貼るというプロセスを踏む必要があります。その場合、DB2にセットするには

    上記のようなコードにし、この関数を呼ぶ前後に、リンクテーブルの解除と再リンクのコードを追加すると上手く稼動します。
  • fieldnameには通常はID列を指定するので、呼び出す時には以下のように関数を呼び出します。

関連リンク

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)