Excelで運用していた管理表をACCESSに移行したい。
だけど、データをいちいち手入力するのは面倒。
そんなときにはインポート機能が便利です。
ACCESSには別のシステムから出力されたcsvファイルを一括インポートできる機能が備わっています。
しかし、ACCESSの備えているインポート機能で処理する場合、頻度が高いと手動操作が面倒ですし、そもそもACCESSに詳しくない利用者には手順が難しいですよね。
そこで、ACCESS VBAでインポート処理をつくれば、自動化できて効率が良くなります。
今回はcsvファイルの自動インポート機能の作成方法を紹介します。
こんにちは。
はこにわガジェット （@hakoniwagadget） です。
ACCESSを使った売上管理、顧客管理などのデータベース開発を行っています。
ACCESSは使いこなすために少しスキルが必要なものの、うまく活用すればExcelよりも業務の効率化が図れます。
この記事ではACCESSで実際に作成したフォームやレポートを、その作成方法と共にご紹介していきます。
csvファイル自動インポート機能（完成形）
最初に完成形からです。
上記のようなフォームを作成し、インポートするファイルを置くフォルダパスをフォーム上で入力します。
このフォルダパスはACCESS内で記録しておきますので、毎回入力する必要はありません。
その上で、「データ取込」ボタンを押すと、ACCESSのテーブルに指定したcsvファイルが取り込まれる、という機能になります。
参照設定（事前準備）
まず、事前準備としてACCESSでファイルシステムオブジェクトを扱うための設定が必要になります。
ACCESS上でドライブやフォルダ、ファイルなどを操作する際の設定です。
ACCESSを起動し、「ALT」+「F11」キーを押します。
すると、Visual Basic Editorが起動します。
上段のメニューから「ツール」→「参照設定」を選択します。
参照設定画面が表示されますので、「Microsoft Scripting Runtime」にチェックを入れ、OKを押します。
Microsoft Scripting RuntimeはACCESS VBAから外部のテキストデータを扱う際に利用するライブラリです。
今回はcsvファイルを加工するために追加しています。
これで準備は完了です。
データ取込用テーブルの作成
続いて、データの取り込み用テーブルを作成します。
csvファイルの準備
まずは、今回インポートするデータを準備します。
ここでは、下記の売上データを準備しました。
売上日、顧客名、数量、単価が入ったcsvのデータです。
上記は分かりやすいようにExcelで表示していますが、テキストエディタで表示すると以下のようになります。
各値が,（カンマ）で区切られていることがわかります。
このデータをインポートする機能を作っていきましょう。
デザインビューでテーブル作成
上記のデータを取り込むために、ACCESSに同じデータフォーマットのテーブルを作成します。
テーブルをデザインビューで作成していきます。
今回は下記のテーブルレイアウトとしました。
テーブルのデザインでは、特にデータ型に注意が必要です。
インポートする際に、csvファイルに入力されているデータと、ACCESSのテーブルに設定されたデータ型が異なるとインポートエラーが発生します。
csvファイルのインポートでエラーが出る場合、原因はこのデータ型の設定が誤っているケースが最も多いのです。
インポートデータに数値しか入っていないと思って、テーブル側を数値型に指定すると、途中からテキストが入っていて取り込みエラーになる、ということが良くあります。
もしインポートがうまくいかない場合は、まずデータ型が合致しているかを確認してください。
インポートしてテーブル作成
先ほどはデザインビューでテーブルを作成しましたが、もっと簡単な方法もあります。
それは、一度csvファイルをインポートし、その際に新規テーブルを作成する方法です。
実際にやってみましょう。
ACCESSを起動し、メニューバーから「外部データ」→「テキストファイル」を選択します。
次に、ウィザード画面でインポートするファイルを選択し、「現在のデータベースの新しいテーブルにソースデータをインポートする」を選択して、OKを押します。
csvファイルのデータ形式を聞かれます。
今回は、インポートするファイルがカンマ区切りのため、上の「区切り記号付き」を選択します。
ここはインポートするファイルの形式に合わせて都度選択します。
次の画面では区切り記号を指定します。
今回はカンマですね。
それと、csvファイルの先頭の行が見出しの場合は、「先頭行をフィールド名として使う」にチェックします。
ここにチェックすると、先頭行はデータとして扱わず、フィールド名（見出し）になります。
次にフィールドのデータ型設定画面になります。
基本的にはACCESS側で自動的にインポートファイルを読み取ってデータ型を決めてくれるのですが、たまに間違って文字列型のフィールドを数値型にしてしまっているケースなどがあります。
インポートするファイルと合っているか確認しましょう。
次は主キーの設定画面です。
ここも、インポートするデータによって異なるのですが、今回のデータでは「主キーを設定しない」を選択しておきます。
最後に作成するテーブルの名称を聞かれますので、適切な名称を入力します。
これで、インポート先のテーブルが作成できました。
今回欲しかったのはテーブルのレイアウトだけですので、レコードについては削除しても良いですし、そのまま使ってもよいでしょう。
インポート定義の作成
これでインポート先のテーブルはできました。
ただ、もう一つ準備するものがあります。
それはインポート定義です。
インポート定義とは、ファイルをACCESSに取り込む際に、どのフィールドをどのデータ型で取り込むかを定義したデータです。
では、インポート定義を早速作成しましょう。
先ほどのインポート処理の中で、この画面で「設定」を押します。
すると、インポート定義を設定する画面が表示されます。
下段の「フィールドの情報」で取り込む各フィールドのデータ型を設定します。
基本的には自動で設定されますが、自動設定が誤っている場合は手動で修正が可能です。
また、取り込みしないフィールドは「スキップ」欄にチェックを付けることで取り込みをスキップできます。
この画面で「保存」を押すとインポート定義が保存できます。
今回は「売上データインポート定義」という名前にしました。
ここで保存した名前はあとでVBAで使用しますので覚えておきましょう。
インポート定義が保存できれば、インポート処理自体は中断してかまいません。
フォームの作成
次にフォームを作成します。
フォームはデザインビューでこのような形で作りましょう。
非連結のテキストボックスが中央にありますが、ここに取り込むファイルのフォルダパスを入力するためのコントロールです。
例えば、「C:\import」などと入力させます。
しかし、非連結のコントロールなので一度フォームを閉じると値がクリアされてしまいます。
ファイルの取り込み元フォルダは変わらないことが多いので、毎回入力するのは面倒ですよね。
そこで、ここに入力した値はVBAでテーブルに格納するようにします。
フォルダパスを記載するテキストボックスの更新後処理にイベントを設定します。
VBAのコードは次の項でご説明します。
また、データ取込ボタンにもイベントを設定します。
こちらはクリック時イベントに、csvファイルを取り込むイベントプロシージャを設定しておきます。
こちらも内容は次の項にてご紹介します。
VBAのプログラミング
それでは最後にVBAでのプログラミングです。
今回は以下の2つのプロシージャを作成します。
- フォルダパスの保存・更新
- データ取り込み
1.フォルダパスの保存・更新
csvファイルの保存場所となるフォルダパスの保存・更新用プロシージャは以下の通りです。
2つのプロシージャを掲載しておりますが、1つめはフォームに設定したイベントプロシージャで、2つ目はそのイベントプロシージャから呼び出される「folderpath_kousin」を記述した標準プロシージャです。
フォーム上のフォルダパスを更新した際のイベントプロシージャ
Private Sub フォルダパス_AfterUpdate()
If IsNull(Me!フォルダパス) = True Then
folderpath = ""
Else
folderpath = Me!フォルダパス
End If
Call folderpath_kousin(folderpath)
End Sub
このプロシージャでは、Me!フォルダパスの値がNullであれば変数「folderpath」に空白を、Nullでなければ入力された値を保存し、下記のプロシージャ「folderpath_kousin」を呼び出します。
実際にフォルダーパスを更新するプロシージャ
'変数の宣言
Public folderpath As String
Dim cnn As Object
Dim rst1 As Object
Dim rst2 As Object
Public Sub folderpath_kousin(folderpath As String)
'変数にADOオブジェクトを代入
Set cnn = CreateObject("ADODB.Connection")
cnn.Open = CurrentProject.Connection
Set rst1 = CreateObject("ADODB.Recordset")
rst1.CursorLocation = adUseClient
'レコードセットを取得
rst1.Open "MST_設定", cnn, adOpenKeyset, adLockOptimistic
'設定名で検索
rst1.Find "設定名 = 'フォルダパス'"
rst1.Update "設定値", folderpath
'終了処理
rst1.Close: Set rst1 = Nothing
cnn.Close: Set cnn = Nothing
End Sub
2つ目のプロシージャは先ほどのプロシージャからCallされます。
ADOを使って、引数「folderpath」で渡された値をテーブルに格納するプロシージャです。
ADOはACCESSでVBA上からテーブルのレコードを操作するためのものです。
ADOについて知りたい方は以下の記事をご覧ください。
今回は「MST_設定」というテーブルにファイルパスの値を格納しています。
このテーブルは各種設定データを保存するために作ったもので、設定名がファイルパスとなっているレコードを指定して、値を書き換えるという処理をしています。
rst1.Findで設定名がファイルパスのレコードを探し、rst1.Updateで値を更新しています。
ファイルパスがあまり変わらないのであれば、こんな面倒なことをせずにプロシージャの中に直接ファイルパスを記述してしまえばよいと思うかもしれません。
確かにそれも可能なのですが、その場合はファイルパスを変更するたびにプログラムを書き換える必要が発生してしまいます。
設定情報をVBAに直接記述するのではなく、テーブルに格納しておくと修正時に変更が必要な箇所が減り、メンテナンス性が高まります。
2.データ取込
続いて、実際のcsvファイル取込用プロシージャです。
こちらはフォームの「データ取込ボタン」を押すとまず、以下のイベントプロシージャが動作します。
Private Sub データ取込_ボタン_Click()
Call torikomi
End Sub
そして、ここで呼び出された標準プロシージャの「torikomi」が以下です。
Public Sub torikomi()
'オブジェクト変数の定義
Dim FSO As Object
Set FSO = CreateObject("scripting.FileSystemObject")
Dim imp_file As Object
Dim file_name As String
'フォルダパス指定チェック
If IsNull(DLookup("設定値", "MST_設定", "設定名 = 'フォルダパス'")) = True Then
MsgBox "フォルダパスが指定されていません。", vbCritical + vbOKOnly, "フォルダパス未指定"
Exit Sub
Else
folderpath = DLookup("設定値", "MST_設定", "設定名 = 'フォルダパス'")
End If
'フォルダの存在チェック
If FSO.FolderExists(folderpath) = False Then
'フォルダが存在しない場合
MsgBox "フォルダパスの指定が誤っています。", vbCritical + vbOKOnly, "フォルダパス指定不備"
Exit Sub
End If
'ファイルの存在チェック
If Dir(folderpath & "\*.csv") = "" Then
'フォルダにファイルが存在しない場合
MsgBox "取り込み可能なファイルがありません。", vbCritical + vbOKOnly, "ファイルなし"
Exit Sub
End If
For Each imp_file In FSO.GetFolder(folderpath).Files
'ファイル名を取得
file_name = imp_file.Name
'ACCESSへのインポート処理
DoCmd.TransferText acImportDelim, "売上データインポート定義", "売上データ", DLookup("設定値", "MST_設定", "設定名 = 'フォルダパス'") & "\" & file_name, False
Next
MsgBox "取込が完了しました。", vbInformation + vbOKOnly, "取込完了"
End Sub
以下で内容を解説します。
フォルダパスを変数に格納
最初に、現在MST_設定テーブルに格納されているインポートファイルを置くフォルダパスの値を変数「folderpath」に格納します。
'フォルダパス指定チェック
If IsNull(DLookup("設定値", "MST_設定", "設定名 = 'フォルダパス'")) = True Then
MsgBox "フォルダパスが指定されていません。", vbCritical + vbOKOnly, "フォルダパス未指定"
Exit Sub
Else
folderpath = DLookup("設定値", "MST_設定", "設定名 = 'フォルダパス'")
End If
この際、テーブルに値が設定されていない場合はエラーメッセージを出して処理を中止します。
フォルダ、ファイルの存在チェック
次に指定されたフォルダやファイルの存在チェックをします。
'フォルダの存在チェック
If FSO.FolderExists(folderpath) = False Then
'フォルダが存在しない場合
MsgBox "フォルダパスの指定が誤っています。", vbCritical + vbOKOnly, "フォルダパス指定不備"
Exit Sub
End If
'ファイルの存在チェック
If Dir(folderpath & "\*.csv") = "" Then
'フォルダにファイルが存在しない場合
MsgBox "取り込み可能なファイルがありません。", vbCritical + vbOKOnly, "ファイルなし"
Exit Sub
End If
ユーザーが指定したフォルダやファイルが存在しない場合にエラーメッセージを表示します。
ACCESSの実務ではこうしたエラー処理ルーチンは非常に重要です。
ユーザーが開発者の意図しない操作をした際に、ACCESSのエラーメッセージが出てしまうとユーザーには問題の原因がわかりません。
ユーザーの行動を想定して考えられるるエラー処理ルーチンは事前に組み込んでおくことで、もし操作を間違えてもユーザー自身がそれに気づくことができます。
ACCESSへのインポート処理
いよいよACCESSへのインポート処理です。
For Each imp_file In FSO.getfolder(filepath).files
の部分で、指定のフォルダパスにあるすべてのファイルを順に処理していきます。
以下の箇所でDoCmd.TransferTextを使って指定のテーブルにファイルを取り込みます。
'ACCESSへのインポート処理
DoCmd.TransferText acImportDelim, "売上データインポート定義", "売上データ", folderpath & "\" & file_name, False
この際、先に作っておいたインポート定義ファイルを2つ目の引数に指定して使用します。
今回は”売上データインポート定義”というインポート定義ファイルを指定しています。
インポートするファイルのフォルダパスは folderpathに、ファイル名はfile_nameにそれぞれ格納されたものを利用しています。
最後に処理が完了したらメッセージを出すようにしています。
MsgBox "取込が完了しました。", vbInformation + vbOKOnly, "取込完了"
インポート処理は取り込むファイルのデータ量によっては長い時間がかかることもあるため、完了を明示的にユーザーに伝える処理を作っておく方が運用しやすくなります。
これでcsvファイルのインポート処理は完成です。
数値のカンマ区切りに注意
実は、ACCESSの数ある処理の中でも、データのインポート処理が最も難しいと言っても過言ではありません。
それは、インポートされるデータが開発者の想定外であることが多いためです。
今回作成したように、ACCESSへのデータインポートをする際、インポートされるファイルの項目名やデータ型、並び順は厳密に規定されます。
インポート定義ファイルと少しでも異なったデータがインポートされるとエラーになってしまうので注意してください。特によくある面倒な例は、数値のカンマ区切りです。
例えば、以下のように数値が3桁ごとにカンマで区切られたデータはよく見かけると思います。
これをcsvファイルとしてテキストエディタで見てみると以下のようになります。
数値の前後に”（ダブルクォーテーション）がついたのがわかると思います。
これは、,（カンマ）がそもそもcsvの項目の区切り文字なので、それと識別するために単価フィールドが文字列型に変えられているのです。
このようにデータ型が変わってしまうとインポート処理を行ってもデータ型の変換エラーとなってしまいます。
ACCESSでインポート処理を自動化する際は、インポートするファイルのデータレイアウトが変わらないように注意しましょう。
以上、ACCESSでcsvファイルを自動インポートする機能の作成方法をご紹介しました。
この記事の内容を実際に試したい方へ
本記事の内容を実際にACCESSを動かしながら確認したいという方向けに、記事で紹介した機能が実装されたサンプルファイルを販売しています。記事内で解説しているテーブル、クエリ、フォーム、レポート、VBAのプロシージャなどをそのまま動く形で実装しました。
学習用としてはもちろん、加工して自社業務に利用することも可能です。
ご興味のある方は以下からご覧ください。
ACCESSを使いこなせば、業務の効率化や自動化が実現できます。
しかし、自分でACCESSを学ぶには時間がない、難しそうで不安、という方も多いでしょう。
そんな時は、ACCESS開発歴20年以上、過去に300以上のACCESSデータベースの開発・修正実績のあるはこにわガジェット（@hakoniwagadget）にお任せください。
ACCESSの新規開発、既存のACCESSの修正、ACCESSの操作レッスンなど様々なサービスをご提供しています。
ご興味のある方は下記のサービス一覧をご覧ください。
最後までお読みいただき、ありがとうございました。
