こんにちはmasalifeです。
皆さんは複数のCSVファイルを簡単に一つのテーブルにまとめたいと思ったことはありませんか？
売上データのように月ごとのCSVファイルがある場合、特定の期間（半年等）の集計をするためにはそれぞれのCSVファイルを一つのテーブルにまとめる必要があります。
今回はそのような状況を解決する簡単なツールをAccessで作成する方法を紹介します。
※ツールを動かすためには以下の条件を満たす必要がありますので、作成する際にはご留意ください。
・インポートするファイルはCSVファイルに限る。
・CSVファイルのデータ構造（フィールド名・フィールド数・データ型等）が同じ。
やりたい事
「売上データ」フォルダに2024年の8月～10月の売上データがそれぞれCSVファイルで格納されており、この3つのCSVファイルは見出しの名前（フィールド名）・見出しの数（フィールド数）・セルの書式設定（データ型）といったデータ構造がすべて同一です。
特定の期間で集計を行いたい時は、この3つのCSVファイルをイメージのように一つのテーブルにインポートする必要があります。
一つのテーブルへのまとめ方としては、手動でCSVファイルを一つずつインポートして、追加クエリを使うといった方法で可能です。
ただ、この方法ではファイル数が多くなると、あまりにも手間がかかり非効率です。
そのため、次のようなツールを作成して簡易的にCSVファイルをインポートしてテーブルにまとめたいと思います。
【CSV集計ツール】
クリックで拡大表示
【初期化】→【ファイル名取得】→【インポート】とボタンをクリックするだけの操作が簡単なツールです。
ファイル名のコンボボックスは取得したファイル名を可視化するために便宜的に設定しています。
それでは作っていきましょう。
準備するもの
テーブル
ひと月のデータを格納するテーブル「T_月単位」
フォルダ内のCSVファイル名を格納するテーブル「T_取得ファイル名」
複数月のデータをまとめるテーブル「T_集計」
クエリ
「T_取得ファイル名」のレコードを削除する削除クエリ「Q_ファイル名削除」
「T_集計」のレコードを削除する削除クエリ「Q_集計削除」
テーブル「T_月単位」のレコードを「T_集計」に追加する追加クエリ「Q_集計へ追加」
フォーム
メインフォーム「F_CSV集計」
画面を参考にコントロールを配置してください。
コンボボックス「cmbファイル名」については、
クリックで拡大表示
プロパティで【値集合ソース】を「T_取得ファイル名」と設定してください。
「btn初期化」等の各ボタンについては、【名前】以外のオプション設定は不要です。
DAOの参照設定
今回のVBAではDAOを使用するため、VBE画面で参照設定を行ってください。
【ツール】→【参照設定】をクリックします。
「Microsoft Office 16.0 Access database engine Object Library」にチェックを付し、【OK】をクリックします。
これでAccessでDAOの使用ができるようになりました。
VBAを設定する
「初期化」ボタンに設定
「btn初期化」のクリック時イベントに次のVBAコードを設定します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Private Sub btn初期化_Click() DoCmd.SetWarnings False DoCmd.OpenQuery "Q_ファイル名削除" DoCmd.OpenQuery "Q_集計削除" MsgBox "初期化が完了しました。ファイル名を取得してください。" Me.cmbファイル名 = "" DoCmd.SetWarnings True End Sub |
「ファイル名取得」ボタンに設定
「btnファイル名取得」のクリック時イベントに次のコードを設定します。
|
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 |
Private Sub btnファイル名取得_Click() Dim MyPath As String Dim MyName As String Dim Table01 As dao.Recordset Set Table01 = CurrentDb().OpenRecordset("T_取得ファイル名") MyPath = "C:\Users\・・・\・・・\・・・\売上データ\" MyName = Dir(MyPath, vbNormal) Do While MyName <> "" Table01.AddNew Table01![ファイル名] = MyName Table01.Update MyName = Dir Loop Table01.Close: Set Table01 = Nothing MsgBox "ファイル名の取得が終了しました。インポートをしてください。" Me.cmbファイル名 = Me.cmbファイル名.ItemData(0) End Sub |
「インポート」ボタンに設定
「btnインポート」のクリック時イベントに次のコードを設定します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Private Sub btnインポート_Click() Dim i As Integer Dim MyPath As String DoCmd.SetWarnings False i = 0 Do Until i = DCount("ファイル名", "T_取得ファイル名") Me.cmbファイル名 = Me.cmbファイル名.ItemData(i) MyPath = "C:\Users\・・・\・・・\・・・\売上データ\" & Me.cmbファイル名 DoCmd.DeleteObject acTable, "T_月単位" DoCmd.TransferText acImportDelim, "Salesインポート定義", "T_月単位", MyPath, True DoCmd.OpenQuery "Q_集計へ追加" i = i + 1 Loop DoCmd.SetWarnings True MsgBox "集計が終了しました。" End Sub |
VBAコードを考える
初期化ボタンに設定したVBAコード
|
3 4 5 6 7 8 9 10 11 12 13 |
DoCmd.SetWarnings False DoCmd.OpenQuery "Q_ファイル名削除" DoCmd.OpenQuery "Q_集計削除" MsgBox "初期化が完了しました。ファイル名を取得してください。" Me.cmbファイル名 = "" DoCmd.SetWarnings True |
テーブルの中身を削除してからインポートを実行したいため、docmd.openqueryメソッドを用いて削除クエリを実行しています。
その際の実行メッセージが煩わしいので、docmd.setwarningsメソッドでパラメータをFalseにして消しています。
最後に、コンボボックスに空の文字列（””）を代入して取得したファイル名を消し、初期化が終了です。
・テーブル「T_取得ファイル名」の中身
・テーブル「T_集計」の中身
・コンボボックス「cmbファイル名」の表示
ファイル名取得ボタンに設定したVBAコード
|
3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Dim MyPath As String Dim MyName As String Dim Table01 As dao.Recordset Set Table01 = CurrentDb().OpenRecordset("T_取得ファイル名") MyPath = "C:\Users\・・・\・・・\・・・\売上データ\" MyName = Dir(MyPath, vbNormal) Do While MyName <> "" Table01.AddNew Table01![ファイル名] = MyName Table01.Update MyName = Dir Loop Table01.Close: Set Table01 = Nothing MsgBox "ファイル名の取得が終了しました。インポートをしてください。" Me.cmbファイル名 = Me.cmbファイル名.ItemData(0) |
DAOという機能を用いて、フォルダ内にあるファイル名を取得しテーブルに書き出しています。
詳しくはこちらの記事で解説しています。👇
こんにちはmasalifeです。 今回はAccessで「フォルダに格納されているファイル名を取得しテーブルに書き出す」方法を紹介します。 紹介する内容を整理すると次のとおりとなります。 クリックで拡大表示[…]
補足する内容としてはこのコードです。
|
25 |
Me.cmbファイル名 = Me.cmbファイル名.ItemData(0) |
取得したファイル名を確認できるようにコンボボックスにファイル名を表示しています。
このメソッドが実行される時には、テーブル「T_ファイル取得名」にはファイル名が書き出されているため、ComboBox.ItemData プロパティで値集合ソースに設定した「T_ファイル取得名」の値を取得して、コンボボックスに値を代入しているという内容になります。
規定で表示する値は一番上のファイル名とするため、インデックスに（0）を指定しています。
インポートボタンに設定したVBAコード
|
3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Dim i As Integer Dim MyPath As String DoCmd.SetWarnings False i = 0 Do Until i = DCount("ファイル名", "T_取得ファイル名") Me.cmbファイル名 = Me.cmbファイル名.ItemData(i) MyPath = "C:\Users\・・・\・・・\・・・\売上データ\" & Me.cmbファイル名 DoCmd.DeleteObject acTable, "T_月単位" DoCmd.TransferText acImportDelim, "Salesインポート定義", "T_月単位", MyPath, True DoCmd.OpenQuery "Q_集計へ追加" i = i + 1 Loop DoCmd.SetWarnings True MsgBox "集計が終了しました。" |
フローチャットを作成して考える
インポートの仕組みはDo Untilステートメントを使用した反復処理となっています。
条件を満たすまで処理を続けるといった反復処理はフローチャートを作成すると、全体像を把握出来て理解がしやすくなります。
今回の処理のフローチャートは次のようになります。
【フローチャート】
今からコードの説明をしていきますが、このフローチャートをイメージしながら考えてみてください。
カウンタ変数を宣言し、0（ゼロ）を代入
|
3 4 5 6 7 8 |
Dim i As Integer Dim MyPath As String DoCmd.SetWarnings False i = 0 |
黄色マーカーの部分です。
3 Dim i As Integerは数値型（Integer型）の変数iを宣言しています。この変数は反復処理の回数を数えるために使用されるもので、カウンタ変数と呼ばれています。
8 i = 0で変数iに0を代入しています。なぜ0を代入しているかの詳細は後述しますが、先頭のファイル名を取得するためです。
Do Untilステートメントの構造
|
10 11 12 13 14 15 16 17 |
Do Until i = DCount("ファイル名", "T_取得ファイル名") Me.cmbファイル名 = Me.cmbファイル名.ItemData(i) MyPath = "C:\Users\・・・\・・・\・・・\売上データ\" & Me.cmbファイル名 DoCmd.DeleteObject acTable, "T_月単位" DoCmd.TransferText acImportDelim, "Salesインポート定義", "T_月単位", MyPath, True DoCmd.OpenQuery "Q_集計へ追加" i = i + 1 Loop |
Do Untilステートメントは条件を満たすまでステートメントのブロック（命令）を実行します。
条件となるのは「 i = DCount(“ファイル名”, “T_取得ファイル名”)」の部分で、iがT_取得ファイル名（テーブル）のレコード数になったら、すなわち取得するファイルの数になったら、反復処理が終了する構造となっています。
レコード数の取得にはDCount関数を使用しています。
こんにちはmasalifeです。 今回はDCount関数を使った入力フォームの一例を紹介します。 作成する入力フォームのイメージは次のとおりです。 クリックで拡大表示 来店入力フォームで入力[…]
|
10 11 12 13 14 15 16 17 |
Do Until i = DCount("ファイル名", "T_取得ファイル名") Me.cmbファイル名 = Me.cmbファイル名.ItemData(i) MyPath = "C:\Users\・・・\・・・\・・・\売上データ\" & Me.cmbファイル名 DoCmd.DeleteObject acTable, "T_月単位" DoCmd.TransferText acImportDelim, "Salesインポート定義", "T_月単位", MyPath, True DoCmd.OpenQuery "Q_集計へ追加" i = i + 1 Loop |
ステートメントのブロック（命令）部分を確認します。
11 Me.cmbファイル名 = Me.cmbファイル名.ItemData(i)は、ComboBox.ItemData プロパティを使用して「cmbファイル名」の中身からインデックスがiの値のファイル名を取得し、「cmbファイル名」に代入しています。
内容としては次のイメージ図となります。
必ず覚えていただきたいのは、インデックスは0から始まるという事です。1から始まるわけではありません。
そのため、まず最初に変数iには0を代入（ i = 0）してから反復処理を行っています。
もし、i = 1としてしまうと、「sales_2024_08.csv」のファイル名は取得できなくなってしまうからです。
12 MyPath = “C:\Users\・・・\・・・\・・・\売上データ\” & Me.cmbファイル名は取得するcsvファイルが格納されているフルパスを変数MyPathに代入しています。
「Me.cmbファイル名」の値は11 Me.cmbファイル名 = Me.cmbファイル名.ItemData(i)のコードにより、反復処理1回目では「sales_2024_08.csv」、2回目では「sales_2024_09.csv」・・・・と変わっていきます。
13 DoCmd.DeleteObject acTable, “T_月単位”と14 DoCmd.TransferText acImportDelim, “Salesインポート定義”, “T_月単位”, MyPath, Trueは取得したファイルのデータが重複して集計のテーブルに存在しないように、テーブル（T_月単位）を削除してからインポートしています。
※日付のフィールドは日付／時刻型、その他のフィールドはテキスト型でインポートするために「Salesインポート定義」を事前に作成しています。
こんにちはmasalifeです 今回はフォームに設置したボタンをクリックするだけで、特定のテキストファイルをインポートする機能を紹介します。 使用するのはVBAのDoCmd.TransferTextメソッド […]
15 DoCmd.OpenQuery “Q_集計へ追加”はテーブル（T_集計）に追加するクエリを実行しています。
16 i = i + 1はカウンタ変数iに1を足しています。
理由は分かりますかね？
ComboBox.ItemData プロパティのインデックスを変えて、次のファイル名を取得するためです。
反復処理を整理する
最後に今回の反復処理のポイントを整理したいと思います。
・インデックスは0から始まるので、カウンタ変数iは0から始める。
・今回の取得するファイル数は3なのでiが3となった場合に反復処理が終わる。
・【iの値の変動】1回目は0、2回目は1、3回目は2、4回目は3（処理終了）
・その結果、すべてのファイルのデータが漏れや重複することなくインポートされる。
さいごに
時間短縮、人の手による誤りが無くなるといったメリット満載のツールだと思います。
こういったツールを誰でも安価に作れるAccessは便利なソフトだとつくづく実感します。
最後まで読んでいただきありがとうございました。
