Accessでデータベースを運用する場合に、CSVファイルとして出力されたデータを取り込んで、データ蓄積をする場面が多々見られます。
CSVとは、データ項目ごとにカンマを用いて区切ったテキストファイル形式のことで、Comma-Separated Valuesの略称です。テキストファイルということもあり、データ出力の際のプログラム実装が容易であり汎用的なソフトでも読み書きできるため、互換性の面でも優れた形式です。このため、あらゆる場面でCSVファイルを見かけるのではないでしょうか。
Accessには様々な形式のデータをインポートする機能があり、過去に本ホームページではCSVファイルのインポート処理について紹介をしました。
しかしながら、この方法では一括のデータ取り込みとなりデータに不備があった場合や、必要としないデータまでインポートすることとなります。このため、インポートする前に毎回データの下処理をする必要が出てしまいます。
こうなると、定型業務では煩雑な作業となり、またデータの下処理をすることでヒューマンエラーの要因にもなるおそれがあります。
そこで、今回はCSVファイルのインポートに際し、カンマ区切りで1行ずつデータを取り込み必要な情報だけを取り込む仕組みをVBAで構築する方法を紹介したいと思います。
ここで重要となるのは、CSVファイルをどのようにして1行ずつ取り込むかという点、カンマ区切りのデータをどのように仕分けするかという点になります。
一度、本記事で紹介する方法でデータ処理のルーチンを構築してしまえば、その後は単純なクリック操作だけでデータ処理ができるようになり業務効率の飛躍的な向上を期待することができますので、是非最後までご覧ください。
サンプルの用意
CSVデータを取り込むため、「T_従業員」テーブルを作成し、表1に示すフィールド名、データ型を設定します。
表1 サンプルテーブルのフィールド名とデータ型
| フィールド名 | データ型 |
| 氏名 | 短いテキスト |
| 入社日 | 日付/時刻型 |
| 年齢 | 数値型 |
続いて、サンプルとなるCSVファイルとして以下のファイルを用意しましたので、必要に応じてダウンロードをしてお使いください。
それでは、サンプルを一通り用意できましたので次からVBAコードを実装していきましょう。
VBAによる実装
早速ですが、VBA画面を開き標準モジュールを追加して以下のコードを入力します。
Private Sub CSV_Import()
On Error GoTo Err_CI
'ファイルパス取得用の変数宣言
Dim inttype As Integer
Dim varSelectedFile As Variant
'CSVファイル処理用の変数宣言
Dim strRec As String
Dim strSplit() As String '配列の変数宣言
Dim OpenFile As Variant
'ADO用の変数宣言
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'ファイル選択画面の表示
inttype = msoFileDialogFilePicker
'ファイル参照用の設定値をセットします。
With Application.FileDialog(inttype)
'ダイアログタイトル名
.Title = "CSVファイル選択"
'ファイルの種類を定義します。
.Filters.Add "CSVファイル", "*.csv"
'複数ファイル選択を可能にする場合はTrue、不可の場合はFalse。
.AllowMultiSelect = False
'最初に開くホルダーを当ファイルのフォルダーとします。
.InitialFileName = CurrentProject.Path
If .Show = -1 Then 'ファイルが選択されれば -1 を返します。
For Each varSelectedFile In .SelectedItems
'CSVファイルのパスを取得
OpenFile = varSelectedFile
Next
Else
Exit Sub
End If
End With
'ADOでCSVデータをインポートする「T_従業員」テーブルを開く
Set cn = CurrentProject.Connection
rs.Open "T_従業員", cn, adOpenKeyset, adLockOptimistic
'CSVファイルの処理
'指定ファイルをOPEN(入力モード)
Open OpenFile For Input As #1
'CSVファイルのEOF(End of File)まで繰り返す
Do Until EOF(1)
' レコード読込
Line Input #1, strRec
' カンマ区切りを配列に格納
strSplit = Split(strRec, ",")
'ADOで「T_従業員」テーブルにレコードを追加
'この際、年齢が40歳を超える人のみインポートする条件を加える。
If strSplit(2) > 40 Then
rs.AddNew
rs!氏名 = strSplit(0)
rs!入社日 = strSplit(1)
rs!年齢 = strSplit(2)
rs.Update
End If
Loop
Close #1
'ADOの接続を切断
rs.Close
cn.Close
Set cn = Nothing
MsgBox "CSVデータのインポートを完了しました。"
Exit_CI:
Exit Sub
Err_CI:
MsgBox Err.Description & "_" & Err.Number
Close #1
Resume Exit_CI
End Sub
上で示したコードは、CSVファイルを選択するためのダイアログボックスを表示させ、ファイル選択後にCSVファイルを1行ずつカンマ区切りでデータを取得し、さらに40歳を超える人のデータを「T_従業員」テーブルにインポートするという処理をしています。
それでは、それぞれのコードの役割を見ていきましょう。
最初の部分はこれからの処理で用いる変数を宣言しており、続くmsoFileDialogFilePickerについては、以下の記事でも紹介しているとおりですが、任意のファイルを選択し、そのファイルパスを取得するための機能です。
その次のコードについては、ADOを用いたデータベース接続をしており、これについても以下の記事で詳細を紹介していますのでご参考にしてください。
Openステートメント
ここからが本題のCSVファイルの処理に入ってきます。まずは、「Open OpenFile For Input As #1」というコードですが、これは「Open」ステートメントを用いて先にパスを取得していたCSVファイルを開く処理をします。
Openステートメントは次のように定義されます。
Open pathname For mode [Accessaccess] [lock] As [#] filenumber [reclength]
表2 Openステートメントの引数
| 引数 | 内容 |
| pathname | 必須。 ファイル名を指定する文字列式。ディレクトリまたはフォルダーとドライブを含めることもできる。 |
| mode | 必須。 ファイル モードを指定するキーワード (Append、Binary、Input、Output、または Random) 。 指定していない場合、ファイルは Random アクセス用に開かれる。 |
| access | 省略可能。 開いたファイルに対して許可される操作を指定するキーワード (Read、Write、または Read Write) 。 |
| lock | 省略可能。 開いたファイルに対する他のプロセスからの操作の制限を指定するキーワード (Shared、Lock Read、Lock Write 及び Lock Read Write) 。 |
| filenumber | 必須。 範囲が 1 以上 511 以下の有効なファイル番号。 次に使用可能なファイル番号を取得するには、FreeFile 関数を使用する。 |
| reclength | 省略可能。 32,767 (バイト) 以下の数値。ランダム アクセス用に開いたファイルの場合、この値がレコード長になる。 シーケンシャル ファイルの場合、この値はバッファーされる文字数になる。 |
ファイルに対するインプット、アウトプットの操作をする場合にOpenステートメントを用いることができ、pathname引数で指定したファイルが存在しない場合、mode引数にBinary、Output、または Randomを指定すると、新たにファイルを作成しますが、Inputの場合はエラーとなりますので注意してください。
開いたファイルはDo Loop文でCSVファイルのEOF（End Of File）になるまで繰り返しの処理をします。ここで、「EOF(1)」という記載をしていますが、「1」がOpenステートメントで指定したファイルナンバーに該当します。
Openステートメントで開いたファイルは、コードの後半に記載している「Close #1」で閉じる処理をしています。
Line Input #ステートメント
Do Loop文の中身を見ていきましょう。まず、「Line Input #1, strRec」という記載があります。ここで「Line Input #」ステートメントについて確認しましょう。
Line Input # ステートメントは次のように定義されます。
Line Input # filenumber, varname
表3 Line Input # ステートメントの引数
| 引数 | 内容 |
| filenumber | 必須。任意の有効なファイル番号で、1～511の範囲を使用します。 |
| varname | 必須。有効な変数または文字列の変数名。 |
Line Input # ステートメントは、文字列として「復帰 (Chr(13))」、または「復帰改行 (Chr(13) + Chr(10))」（VBAでは「vbCrLf」の方が馴染み深いかもしれません。） が出現するまで１文字ずつファイルから読み取ります。 復帰改行は、文字列に追加されるのではなくスキップされます。そして読み取った値をvarnameに代入します。
したがって、サンプルコードの場合は1行分（復帰改行まで）の文字列を読み取った後、「strRec」変数にその文字列が格納されます。
さて、strRec変数に格納された値はカンマを含む1行のデータとなっています。そこで、カンマ区切りのデータにするための処理が必要となってきます。
Split関数
カンマ区切りのデータにするために、Split関数と配列変数を用います。サンプルコードには「strSplit = Split(strRec, “,”)」という記載がありますが、これが意味していることを確認していきましょう。
まず、strSplit変数についてですが、コードの最初の方に「Dim strSplit() As String」として配列の変数宣言をしています。
続いて、Split関数についてその定義を見ていきましょう。
Split(expression, [ delimiter, [ limit, [ compare ]]])
表4 Split関数の引数
| 引数 | 内容 |
| expression | 必須。サブ文字列と区切り記号が含まれる文字列式。 expression が長さ 0 の文字列 (“”) の場合、Split関数は、空の配列、つまり、要素とデータのない配列を返す。 |
| delimiter | オプション。サブ文字列の制限を識別するために使用する文字列。省略すると、空白文字 (” “) が delimiterとみなされる。区切り記号が長さ0の文字列の場合は、式文字列全体を含む単一要素配列が返される。 |
| limit | オプション。返される部分文字列の数。-1は、すべての部分文字列が返される。 |
| compare | 省略可能。サブ文字列を評価するときに使用する比較の種類を示す数値。 |
Split関数は、指定された数のサブ文字列が含まれる0ベースの1次元配列を返します。
したがって、サンプルのコードでは「strRec」変数に格納されたCSVファイル1行分のカンマを含む文字列を、Split関数を用いてカンマで分割し、各値を「strSplit」配列変数に格納するという処理をしています。
サンプルのCSVファイル1行分の文字列には、1つ目のカンマ前までに「氏名」、2つ目のカンマ前までに「入社日」、その後ろに「年齢」の情報が記載されています。そのため、Split関数で分割された各値はそれぞれ、strSplit(0)に氏名、strSplit(1)に入社日、strSplit(2)に年齢が格納されることになります。
続く処理では年齢が40歳を超える人のみ「T_従業員」テーブルへデータをインポートするという条件を加えることとしており、If文の条件に「strSplit(2)：年齢フィールド」>40 を記載しています。
以降は、ADOを用いて「T_従業員」テーブルの各フィールドにstrSplit配列変数の各値を移行する処理をし、ADOにおける接続を切断処理をしています。
VBAの実行
実装した処理を実行してみましょう。
結果としては、図1に示すものが得られます。
このようにして、必要な条件を予め設定しておくことでそれに応じたデータのみをインポート処理することができました。今回の条件設定は単純なものとしましたが、より複雑な条件設定をすることもでき、一度VBAで処理ルーチンを実装すれば、それ以降はデータの前処理が不要となります。
まとめ
今回はCSVファイルのインポートに際し、カンマ区切りで1行ずつデータを取り込み、条件に応じたデータのみをAccessに取り込む仕組みをVBAで構築する方法を紹介しました。
CSVファイルによるデータのやりとりは様々な場面で見かけることがあり、今回紹介したようなVBAによる実装をすることで効率良くデータインポートを行えますので、是非本記事を参考にして実業務にも活用していただければと思います。
今の時代、VBAに限らずプログラミングができるということは、当然のスキルとして広く社会で認知されています。いまだプログラミングが十分にできないという人は、今からでも遅くありません。できるだけ早く基本的なプログラミングスキルを習得することをお勧めします。
ご自身にあった専門書を参考に、実践的なプログラミング・データベース・サーバ、データ分析・機械学習など、システムエンジニアや今後を担うDX人材に必要とされているスキル獲得に向けて基礎から学んでいくことが大切です。
最初につまずきがちな点も、書籍を読みながら試行錯誤して課題解決していくことで、自身のスキルアップを目指すことができます。思い立ったが吉日、是非業務効率の改善に向けてプログラミング学習に勤しんでください！
スポンサーリンク
コメント