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の場合インポート定義が無いので注意が必要です。データの型をしっかり合わせておく必要があります。
- passionテーブルにimportsheet.xlsxのpassionシートのデータをそのまま取り込みます。
- 外部データタブを開き、インポートとリンクの中にあるExcelのアイコンをクリックする
- ダイアログが出てくるので、参照をクリックし、importsheet.xlsxを指定します。
- 「レコードのコピーを次のテーブルに追加する」にて、Access内のpassionテーブルを指定する（今回はpassionテーブル）
- OKボタンをクリックして次に進む
- Excelの中身がプレビューされる。そのまま次へ進むをクリックする
- 完了をクリックして終了
図：テーブルに取り込むダイアログ
CSVファイルをインポートする
Excelファイル以上に利用頻度が高いのがCSVファイルのインポート。他のシステムから吐き出したデータをAccessや連携先のデータベースファイルなどに取り込む為に非常によく使います。Excelと違ってcsvファイルの場合は、インポート定義の処理があります。この定義は取っておく事ができます。取り込み手順は以下の通り。
- passionテーブルにimportsheet.csvのデータをそのまま取り込みます。
- 外部データタブを開き、インポートとリンクの中にあるテキストファイルをクリックします。
- ダイアログが出てくるので、参照をクリックし、importsheet.csvを指定します。
- 「レコードのコピーを次のテーブルに追加する」にて、Access内のpassionテーブルを指定する（今回はpassion2テーブル）
- 区切り記号はデフォルトの「区切り記号付き」のままでOK
- 次へをクリックします。
- 先頭行をフィールドとして使うにチェックを入れます（csvに先頭行のデータが入ってる場合）
- ここで設定をクリックします。
- インポート定義の画面が出てきます。
- 保存ボタンを押すとこの定義を取っておく事が可能です。新規にテーブルとして取り込む場合は、データ型の指定やインデックスの指定などがフィールドの情報に出てきます。（今回はImportsheet インポート定義として名前を付けました）
- インポートをスキップしたい列はチェックを入れるとスルーしてくれます。今回はID列がオートナンバーであるので、チェックを入れます。
- OKボタンをクリックして、次へクリックするとインポートが始まります。
- 完了をクリックするとインポートが完了
- データ型などが不一致だったりした場合には、失敗したメッセージとその内容が表示されることがあります。
- サンプルでは予め作っておいたインポート定義確認というクエリで作成済みインポート定義の状態を確認できます。
図：CSVはインポート手順が多い
図：インポート定義画面
VBAでインポート
手動でインポートはあくまでもインポート元とインポート先がガッチリフィールドと型が一致している場合には使えます。しかし、実務の世界では以下のような問題点が常にあります。
- インポート元のデータとインポート先のテーブルが一致していない事は非常に多い
- また、テーブルの型も一致していない事も頻繁にある
- そもそも、インポート元はコードで、インポート先はコード名である事が多い（商品IDしかなく商品名しか相手にも無い）
- インポート元は限られた情報しかなく、インポート先でデータを補完してあげる必要がある
- インポート元のコードとインポート先でのコード変換が必要
などなど。これらはVBAを使ってDAOなどで処理をしなければインポートする事は出来ません。
Docmd.Transferを使った事例
VBAで手動でインポートと同じ事を行いたい場合に用意されてるAccess専用のメソッドです。Excelファイルの場合にはDocmd.TransferSpreadsheet、CSVファイルの場合にはDocmd.TransferTextがそれぞれ用意されています。ただし細かい指定は出来ないので一括連続取り込みといった用途などで利用します。ファイル名の指定が必要なので、FileDialogなどを組み合わせて処理をする必要があります。
Excelファイルをインポートする
Excelファイルをインポートする場合に利用します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
'Excelファイルをインポートする Public Function excelimport() Dim strpath As String Dim ret As Integer 'ファイルを開くダイアログ用 Dim dlg As Object, boolResult As Boolean Dim strFiles As String, i As Long Dim myStr As String 'オブジェクト変数にFileDialogオブジェクトを代入 Set dlg = Application.FileDialog(msoFileDialogSaveAs) With Application.FileDialog(msoFileDialogFilePicker) '複数選択可能かを設定 .AllowMultiSelect = False 'ファイル ダイアログ ボックスのタイトル設定 .Title = "Excelファイルのインポート" '表示される初期パスまたはファイル名を設定 .InitialFileName = CreateObject("WScript.Shell").SpecialFolders.Item("Desktop") & "" '初期ビューを設定(バージョンによって無視される) .InitialView = msoFileDialogViewWebView 'ファイル フィルタのコレクション追加 With .Filters .Clear .Add "インポートファイル", "*.xls;*.xlsx" End With 'Showを宣言した所でダイアログが開かれる。戻り値は以下の通り 'ファイル、フォルダが選択 → True 'キャンセルを押された → False If .Show = True Then myStr = .SelectedItems(1) strpath = myStr Else myStr = "" MsgBox "キャンセルされました。" Exit Function End If End With 'passionテーブルにインポートする DoCmd.TransferSpreadsheet acImport, , "passion", strpath, True, "passion!" End Function |
- FileDialogでファイルを指定してパスを取得させています。
- Scripting Runtimeにて初期フォルダをデスクトップに指定させています
- ファイルが選択されるとstrpathに対象のxlsxファイルのパスが格納されます。
- Docmd.TransferSpreadsheetにて取り込みます。構文は以下の通り。
- ただしこの構文を使ったケースの場合、テーブル側のIDがオートナンバーで、尚且Excelファイル側にもID列がある場合、インポートがエラーになります（オートナンバー列はインポートが出来ないため）
- サンプルファイル内では、リボンから呼び出してるので、関数の引数にcontrol As IRibbonControlが入っていますが、通常は不要です。
構文は以下のような感じです。
|
1 |
DoCmd.TransferSpreadsheet acImport, Excelのバージョン指定, インポート先テーブル名, Excelファイルパス, 先頭行を列名にする時はtrue, 取り込みシート名 |
- Excelのバージョン指定はacSpreadsheetTypeExcel9みたいに指定しますが、省略可能
- 先頭行を列名にする時はtrueの値を入れておくと、Excelの1行目はフィールド名と判断します
- 取り込みシート名はシート名!という具合に「！」を最後に指定します
CSVファイルをインポートする
CSVファイル等をインポートする場合に利用します。予め作成しておいたインポート定義を利用する事も可能です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
'csvファイルをインポートする Public Function csvimport() Dim strpath As String Dim ret As Integer 'ファイルを開くダイアログ用 Dim dlg As Object, boolResult As Boolean Dim strFiles As String, i As Long Dim myStr As String 'オブジェクト変数にFileDialogオブジェクトを代入 Set dlg = Application.FileDialog(msoFileDialogSaveAs) With Application.FileDialog(msoFileDialogFilePicker) '複数選択可能かを設定 .AllowMultiSelect = False 'ファイル ダイアログ ボックスのタイトル設定 .Title = "CSVファイルのインポート" '表示される初期パスまたはファイル名を設定 .InitialFileName = CreateObject("WScript.Shell").SpecialFolders.Item("Desktop") & "" '初期ビューを設定(バージョンによって無視される) .InitialView = msoFileDialogViewWebView 'ファイル フィルタのコレクション追加 With .Filters .Clear .Add "インポートファイル", "*.csv" End With 'Showを宣言した所でダイアログが開かれる。戻り値は以下の通り 'ファイル、フォルダが選択 → True 'キャンセルを押された → False If .Show = True Then myStr = .SelectedItems(1) strpath = myStr Else myStr = "" MsgBox "キャンセルされました。" Exit Function End If End With 'passionテーブルにインポートする DoCmd.TransferText acImportDelim, "Importsheet インポート定義", "passion2", strpath, True End Function |
- FileDialogでファイルを指定してパスを取得させています。
- Scripting Runtimeにて初期フォルダをデスクトップに指定させています
- ファイルが選択されるとstrpathに対象のcsvファイルのパスが格納されます。
- Docmd.TransferTextにて取り込みます。構文は以下の通り。
- ただしこの構文を使ったケースの場合、テーブル側のIDがオートナンバーで、尚且Excelファイル側にもID列がある場合、インポートがエラーになります（オートナンバー列はインポートが出来ないため）。なので、インポート定義を使ってID列をスキップする設定を利用しています。
- サンプルファイル内では、リボンから呼び出してるので、関数の引数にcontrol As IRibbonControlが入っていますが、通常は不要です。
構文は以下のとおりです。
|
1 |
DoCmd.TransferText acImportDelim, インポート定義名の指定, インポート先テーブル名, csvファイルパス, 先頭行を列名にする時はtrue |
- acImportDelimでカンマ区切りでインポートします。
- インポート定義名の指定は省略可能です。今回は対象のテーブルがオートナンバーなので、インポート定義でID列をスキップする設定が入っています。
- 先頭行を列名にする時はtrueの値を入れておくと、csvの1行目はフィールド名と判断します
リンクテーブルを使った事例
これまでの手法はただ単純に同じ形式のファイルを同じ形式のテーブルにただ流し込むだけで、非常に自由度がない構文です。実用できるシーンは割と限られてきます。今回提示した問題点をクリアするにはこの構文では力不足です。そこでExcelやCSVファイルに対して、リンクテーブルを貼り、クエリ上やVBA上で処理を施す事で自由度の高いインポートを自動化する事が可能になります。いくつかの注意点もあるので、それらを踏まえて構築する必要があります。
今回リンクテーブルの名前はm_passionという名前で作成します。
クエリを利用した方法
コードを作成する前に、Excelファイルへのリンクテーブルを作成しておき、作成したリンクテーブルからpassionテーブルへの「追加クエリ」を用意しておいて、インポートする方法です。追加クエリの名前は「xlsxデータ追加」という名称で作ってあります。ただし注意点として、
- 作業後にはリンクテーブルは必ず削除し残さないようにしないと、次回リンクテーブル作成時にテーブル名がバッティングしてしまいます。
- データの変換や追加等などの細かい作業はクエリ内のフィールドで行わせるようにします。
- ID列がオートナンバーであるならば、クエリ内でID列はインポート対象から外しておきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
'excel形式をインポートする(クエリを使用する） Public Function linkQryXlsxImport() 'ファイルパスを格納する変数 Dim strpath As String 'ファイルを開くダイアログ用 Dim dlg As Object, boolResult As Boolean Dim myStr As String 'オブジェクト変数にFileDialogオブジェクトを代入 Set dlg = Application.FileDialog(msoFileDialogSaveAs) With Application.FileDialog(msoFileDialogFilePicker) '複数選択可能かを設定 .AllowMultiSelect = False 'ファイル ダイアログ ボックスのタイトル設定 .Title = "Excelファイルへの接続" '表示される初期パスまたはファイル名を設定 .InitialFileName = CreateObject("WScript.Shell").SpecialFolders.Item("Desktop") & "" '初期ビューを設定(バージョンによって無視される) .InitialView = msoFileDialogViewWebView 'ファイル フィルタのコレクション追加 With .Filters .Clear .Add "インポートファイル", "*.xls;*.xlsx" End With 'Showを宣言した所でダイアログが開かれる。戻り値は以下の通り 'ファイル、フォルダが選択 → True 'キャンセルを押された → False If .Show = True Then myStr = .SelectedItems(1) strpath = myStr Else myStr = "" MsgBox "キャンセルされました。" Exit Function End If End With 'DB接続用の変数宣言 Dim db As DAO.Database Set db = CurrentDb Dim tdf As DAO.TableDef 'テーブルリンクを作成する（xlsxに対してリンクテーブルを追加する。） DoCmd.TransferSpreadsheet acLink, , "m_passion", strpath, True, "passion!" '警告ダイアログをON(戻す) DoCmd.SetWarnings False '実績データを追加取り込み DoCmd.OpenQuery "xlsxデータ追加" '警告ダイアログをON(戻す) DoCmd.SetWarnings True '終了処理 'リンクテーブル解除 DoCmd.DeleteObject acTable, "m_passion" End Function |
- FileDialogで取得したファイルパスに対して、Docmd.TransferSpreadsheet acLinkにてリンクテーブルを作成します。
- 取り込みシート名はシート名!という具合に「！」を最後に指定します
- リンクで作成するテーブル名としてm_passionを指定する
- Docmd.Setwarningをtrueにすると、クエリ追加時の問い合わせをオフにすることが可能です。必ず処理が終わったらfalseにしましょう。
- リンクテーブルを作成したら、Docmd.OpenQueryにて、予め作っておいたxlsxデータ追加の追加クエリを実行します。
- 最後の処理としてDocmd.DeleteObjectにてm_passionのリンクテーブルを削除しておきます。
図：今回は極簡単な追加クエリにしてあります
DAOを利用した方法
クエリを使用したリンクテーブルからのインポートはお手軽なのですが、クエリ内で色々と変換や追加の処理を記述しなければならないので、メンテナンス性では劣ります。また複雑な変換処理等をさせた場合には何をしているのか見通しも悪くなります。そこで、リンクテーブルに対してDAOを利用して非常に細かく追加処理を行う事が可能です。また、SQL文を直接発行させる事も可能なので、人によっては非常に管理のしやすいコードになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
'excel形式をインポートする(DAOを使用する） Public Function linkDAOXlsxImport() 'ファイルパスを格納する変数 Dim strpath As String 'ファイルを開くダイアログ用 Dim dlg As Object, boolResult As Boolean Dim myStr As String 'オブジェクト変数にFileDialogオブジェクトを代入 Set dlg = Application.FileDialog(msoFileDialogSaveAs) With Application.FileDialog(msoFileDialogFilePicker) '複数選択可能かを設定 .AllowMultiSelect = False 'ファイル ダイアログ ボックスのタイトル設定 .Title = "Excelファイルへの接続" '表示される初期パスまたはファイル名を設定 .InitialFileName = CreateObject("WScript.Shell").SpecialFolders.Item("Desktop") & "" '初期ビューを設定(バージョンによって無視される) .InitialView = msoFileDialogViewWebView 'ファイル フィルタのコレクション追加 With .Filters .Clear .Add "インポートファイル", "*.xls;*.xlsx" End With 'Showを宣言した所でダイアログが開かれる。戻り値は以下の通り 'ファイル、フォルダが選択 → True 'キャンセルを押された → False If .Show = True Then myStr = .SelectedItems(1) strpath = myStr Else myStr = "" MsgBox "キャンセルされました。" Exit Function End If End With 'テーブルリンクを作成する（xlsxに対してリンクテーブルを追加する。） DoCmd.TransferSpreadsheet acLink, , "m_passion", strpath, True, "passion!" 'データベース接続用変数 Dim dbs As Database Dim rs As DAO.Recordset Dim rst As DAO.Recordset Set dbs = CurrentDb Set rs = dbs.OpenRecordset("passion") 'インポート先テーブル Set rst = dbs.OpenRecordset("m_passion") 'インポート元テーブル 'レコードを追加する Do Until rst.EOF With rs .AddNew !ID = rst!ID !商品名 = rst!商品名 !分類名 = rst!分類名 !価格 = rst!価格 !売上個数 = rst!売上個数 !備考欄 = rst!備考欄 .Update rst.MoveNext End With Loop '終了処理 'リンクテーブル解除 DoCmd.DeleteObject acTable, "m_passion" 'オブジェクトの開放 dbs.Close: Set dbs = Nothing Set rs = Nothing Set rst = Nothing End Function |
- まずはDocmd.TransferSpreadsheet acLinkにてリンクテーブルを作成しておきます。
- DAOのデータベース接続用変数を定義して、インポート先のpassionテーブルとインポート元のm_passionテーブルをレコードセットとしてセットします。
- rstに対してDo Until rst.EOFでレコードの最初から最後まで回しつつ、rsに対してAddNewにて追加します。必ずrst.MoveNextを入れないと無限ループになるので注意。
- AddNewの部分でrst!フィールド名でインポート元テーブルの値を取得しますが、この時に様々な変換処理を行うと良いでしょう。
- 最後にDocmd.DeleteObjectにてリンクテーブルを削除しておきます。
- また、dbs.closeやnothingにて利用した変数をクリアしておきましょう。
SQLを利用する場合
今回のサンプルでは、DAOを使ってのインポートは含まれています。しかし今回の方法以外にも、コードの中でSQLを実行してインサートする方法もあります。
|
1 2 3 4 5 6 7 8 9 10 11 |
'passionテーブルに対してm_passionをINSERT Dim db As dao.Database Dim strSQL As String Set db = CurrentDb 'テーブル作成のSQLを実行 strSQL = "INSERT INTO passion SELECT * FROM m_passion" db.Execute SQL '終了処理 Set db = Nothing |
- strSQLに対してSQL文を構築します。
- db.Execute SQLにてSQLを実行します。
オートナンバーを初期化する
VBA等でExcelデータのインポートを続けていくと、次第にID列等オートナンバー型を指定してる列の数値が大きくなったり、またその値を元に何か処理をしているケースの場合、リセットをしないと二度同じナンバーは振られない為、どんどん数値が大きくなっていきます。通常ID列はそれで問題ないのですが、時としてこのオートナンバーを初期化して、また1から始めたいケースがあります。
そんな時は以下のようなコードを利用して、インポート前にテーブルデータを全削除してから初期化を実行すると、次回のインポートからはまた、1からスタートする事が可能です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
' オートナンバーリセット Public Function autnumreset(tablename As String, fieldname As String, Optional defnum = 1) As Boolean 'バックアップファイルのDBを開くための変数 Dim DB2 As Database 'MDBファイルを開く Set DB2 = CurrentDb '対象のテーブルデータを全削除する SQL = "DELETE * FROM " & tablename DB2.Execute SQL 'SQLを実行 SQL = "Alter Table " & tablename & " Alter Column " & fieldname & " Counter(" & defnum & ",1)" DB2.Execute SQL '値を返す autnumreset = True '終了処理 DB2.Close: Set DB2 = Nothing Exit Function End Function |
- 必ずまず、対象のテーブルのデータは全削除する必要があります。
- Alter Tableにて構造変更し、defnumの値からオートナンバーを開始するSQLを実行する
- リンクテーブルに対してはAlter Tableは発行出来ないので、その場合は対象のリンクテーブルを削除⇒対象のDB内のテーブルに対してAlter Table⇒再度リンクテーブルを貼るというプロセスを踏む必要があります。その場合、DB2にセットするには
123456'database.accdbの構造変更Dim dbpath As Stringdbpath = CurrentProject.Path & "\database.accdb"'別のMDBファイルを開くSet DB2 = OpenDatabase(dbpath)
上記のようなコードにし、この関数を呼ぶ前後に、リンクテーブルの解除と再リンクのコードを追加すると上手く稼動します。 - fieldnameには通常はID列を指定するので、呼び出す時には以下のように関数を呼び出します。
12'ID列のオートナンバーリセットret = autnumreset("テーブル名", "ID")
