仕事で大量のデータを一括でAccessの特定のテーブルに読み込ませたい時ってないでしょうか。Excelという表計算ソフトがありますが、一般的に多くの企業で使われており、多くの人が使用経験があると思います。
しかし、データの量や複雑さがある程度以上になってくるとAccessを使わざるを得なくなってくると思います。
Accessを使わなくてはいけないというのはわかってはいるけど社内にAccessを使える人は少ないし、使い慣れたExcelで何とかできないものか、と考える人もいるのではないでしょうか。
ExcelやCSVのデータをAccessの特定のテーブルに入れる際に、コピペやAccessのインポート機能を使うかもしれません。でもコピペだと手作業なので、ちょっとした手違いで元データをコピーしてテーブルに貼り付けてしまうかもしれませんし、他にも何らかのミスが起こる可能性もあります。
Accessの「外部データ」タブから「テキストファイル」を押してCSVデータを取り込むというやり方もありますが、参照先のファイルの設定とか何となく面倒くさいし何よりファイルをひとつずつしか取り込めません。
「Excelのシート上に作ったボタンを押すだけで、Accessの特定のテーブルに複数のCSVファイルのデータ部分だけを一括で取り込める方法はないものか・・・。」
そんなニーズもあるんじゃないかと思いましたし、自分の仕事での必要性から備忘録も兼ねて「ExcelVBAとTransferTextを使ってAccessにCSVを取り込ませる方法」について整理してみます。
ExcelVBAとTransferTextを使ってAccessにCSVを取り込ませるコード(Excelファイルに必要な分)
以下に記述したコードが「ExcelVBAとTransferTextを使ってAccessにCSVを取り込ませるコード(Excelファイルに必要な分)」です。動作を確認した環境はExcel2013で、記述したモジュールは標準モジュールになります。
今回のコードを記述する前にデスクトップ上でもどこでもいいのですが、新しくフォルダを作成して下さい。フォルダ名はなんでも構いません。
そのフォルダの中に今回のコードを記述するExcelファイルとAccessファイル、それとAccessの特定のテーブルにデータを取り込むためのCSVファイルを3つ程用意しておくと結果がわかりやすいかと思います。
処理を実行する前にAccessに用意されたテーブルの構造とCSVファイルのテーブル構造は事前に一致させておく必要があります。これを一致させておかないとエラーメッセージが出てしまうので注意が必要です。
他にも必要な準備として、事前に参照設定で「Microsoft ActiveX Date Objects 6.1 Library」にチェックを入れてください。もしかしたら人によってバージョンの数値が異なるかもしれません。
以下がExcelの標準モジュールに記述するコードです。
Sub DBへCSV取込()
'-----------------------------------------------------------------------
'初期設定
'参照設定で「Microsoft ActiveX Date Objects 6.1 Library」にチェックを入れる
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim ConectString As String
Dim accApp As Object
Dim strFileName As String
strFileName = "データベース4.accdb" 'データベースのファイル名
'-----------------------------------------------------------------------
'Accessに接続( 注意：Data と Source の間に半角スペースを入れる)
ConectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
ThisWorkbook.Path & "\" & strFileName & ";"
cn.Open ConectString
rs.Open Source:="テーブル1", _
ActiveConnection:=cn, _
CursorType:=adOpenKeyset, _
Locktype:=adLockOptimistic
'一度テーブルのデータを全て削除する(テーブルの構造を残したまま削除する)
'strSQL = "DELETE * FROM テーブル1"
'cn.Execute strSQL
'-----------------------------------------------------------------------
'接続したAccessのテーブル1に順番にCSVファイルをインポートしていく。
DBfile = ThisWorkbook.Path & "\" & strFileName
Set accApp = GetObject(DBfile)
'Accessの指定のプロシージャを実行(予めAccessに標準モジュールとプロシージャをつくっておく必要がある)
accApp.Run "CSV読込"
accApp.Quit
MsgBox "CSVデータを取り込みました。"
'-----------------------------------------------------------------------
rs.Close
cn.Close
End Sub概要としては次のような感じになります。
まずConnectionオブジェクトを使ってデータベースに接続し、Recordsetオブジェクトを使って特定のテーブルを操作できるようにします。
次の項目でコードを説明しますが、Accessに記述したコードを使ってAccessの特定のテーブルに拡張子が「CSV」のファイルを全て読み込ませます。この読み込ませるCSVファイルは同一フォルダに格納されているものになります。
これによって今回の目的の「ExcelVBAとTransferTextを使ってAccessにCSVを取り込ませる」ことができます。
ポイントを以下に整理していきます。
次のコードでは「Data 」 と「Source」 の間に半角スペースを入れる必要があります。ちょっとしたことですが、これができていないとエラーが表示されます。
ConectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ ThisWorkbook.Path & "\" & strFileName & ";"また、「strFileName」には実際に使うAccessのファイル名を入れます。
以下のコードの引数「Source:=」の部分にはCSVファイルのデータを入れたいテーブル名を入れます。
rs.Open Source:="テーブル1", ActiveConnection:=cn, CursorType:=adOpenKeyset, Locktype:=adLockOptimistic
以下のコードは、特定のテーブルにCSVファイルを読み込ませる前に一旦データを全て削除するようにしていますが、コメントアウトで実行されないようにしています。
'strSQL = "DELETE * FROM テーブル1"
'cn.Execute strSQLこのコードの削除は、テーブル構造を残したまま内部のデータを削除します。テーブル自体は削除しません。
コメントアウトした状態で2回続けてこの処理を実行しても「キー違反のため～」といったメッセージが表示されて、テーブルにデータは追加されません。
必要であればコメントアウトを解除して、テーブルにデータを入れる前に削除する処理を実行する、という方法も取れます。
以下のコードは次の項目で説明する、Accessに記述したプロシージャを実行するためのコードです。
accApp.Run "CSV読込"accAppは「Set accApp = GetObject(DBfile)」で指定のAccessファイルのオブジェクトを格納しています。このAccess内のプロシージャを実行する、というコードが「accApp.Run “CSV読込”」になります。
ExcelVBAとTransferTextを使ってAccessにCSVを取り込ませるコード(Accessファイルに必要な分)
今度はAccessファイルに必要なコードです。確認した動作環境はAccess2013で、記述したモジュールは標準モジュールになります。
'-------------------------Accessの標準モジュール--------------------
Sub CSV読込()
Dim CSVfile As String
Dim FSO As Object, f As Variant
Set FSO = CreateObject("Scripting.FileSystemObject")
For Each f In FSO.GetFolder(Application.CurrentProject.Path).Files
If FSO.GetExtensionName(f.Path) = "csv" Then
CSVfile = Application.CurrentProject.Path & "\" & f.Name
DoCmd.TransferText acImportDelim, , "テーブル1", CSVfile, True
End If
Next
End Sub上記のコードの概要は、次のような流れになります。
事前の準備から上記のコードが記述されているAccessファイルが格納されたフォルダ内にCSVファイルがいくつか用意されていると思います。そのCSVファイルを全て「テーブル1」テーブルに読み込んでいくというものです。
「テーブル1」という部分は、CSVデータを入れたいテーブル名に変更すればそのテーブルに取り込むことが出来ます。
上記のコードのポイントは以下の部分です。
DoCmd.TransferText acImportDelim, , "テーブル1", CSVfile, True注意したいのは、TransferSpreadsheetではなく「TransferText」を使うということです。今回のコードでAccessに読み込ませるのはCSVなのでTransferTextを使います。
自分は最初、この２つのコードを見てTransferSpreadsheetを使うんだろうと思って使ったのですが、エラーで止まってしまいました。TransferTextだと問題なく実行できたので、勘違いしやすいですが今回の処理のようにCSVのデータを取り込ませたい場合はTransferTextを使います。
もうひとつポイントとしては、読み込ませるCSVファイルの最初の行に何らかの項目名が入っていたら、TransferTextの最後の引数を「True」にする必要があります。
ここをTrueではなく、何も入力しないかFalseにすると項目名も全て読み込まれてしまいます。もしくはエラーになるかですが、どちらにしろ意図した通りには動作しなくなってしまいます。
ちなみにTransferTextの引数の内容は、『VBAエキスパート公式テキスト Access VBA ベーシック』には次のように書かれています。
P.173
【TransferTextメソッドの引数】
引数 定数 説明 変換種類
(省略可)
acImportDlim(既定) カンマ区切りのテキストをインポートする acImportFixed 固定長のテキストをインポートする acExportDelim カンマ区切りのテキストをエクスポートする acExportFixed 固定長のテキストをエクスポートする acExportMerge Word差し込みデータをエクスポートする acImportHTML HTML形式でインポートする acExportHTML HTML形式でエクスポートする 定義名(省略可) インポート・エクスポートの定義名を指定する テーブル名 対象となるテーブル名を指定する ファイル名 対象となるファイルのパスとファイル名を指定する フィールド名
設定(省略可)
True 1行目をフィールド名とする False(既定) 1行目をフィールド名としない
まとめ
今回のコードはExcelとAccessに記述するという形でしたが、本当はExcelだけで完結させたいと思っていました。しかし、Accessの標準モジュールに記述した、
DoCmd.TransferText acImportDelim, , "テーブル1", CSVfile, TrueのコードをどうすればExcelの方で実行できるかわからなかったので、妥協して今回のようにExcelとAccessの二つに記述するという形にしました。
Runメソッドの説明については以下の過去記事でも書きましたが、Excelから他のMicrosoftofficeのソフトにあるマクロやプロシージャを実行させる時に使うことが出来ます。
こういったコードを知っておくと、どうしても本体だけで完結させることができないといった時に今回のような方法も考えることができるので、いろいろと勉強しておくというのは大事だなと思いました。
追記
本来、「accApp.Run “CSV読込”」の部分を実行するだけであれば、
cn.Open～
rs.Open～
といったコードやそれに関連する部分は必要ないです。
けれども、削除コードのコメントアウトを解除して実行したい場合は必要になってきます。(ただし、Accessの標準モジュールの方に記述してしまえば同様にExcelの方でcn.Open～やrs.Open～のコードは使わなくてもよくなる。)
ただ、このブログでは自分の知識の整理や備忘録、今後の拡張性などを考えて書いているので、とりあえずこのままの形で残しておこうかなと思います。
なお、今回のコードでAccessの指定のテーブルに全てのデータが取込めないとか、元データと同じ順番で取込めないといった場合は以下の記事を見ていただければと思います。
コメント