Access VBA:TransferTextを使用せずにVBAでCSVなどの区切り記号つきテキストファイルを既存のテーブルにインポートするサンプルプログラム

Access VBA:TransferTextを使用せずにVBAでCSVなどの区切り記号つきテキストファイルを既存のテーブルにインポートするサンプルプログラム

AccessのVBAでCSVファイルなどの区切り記号付きテキストファイルをインポートするサンプルプログラムを覚書。

[ファイルを開く]ダイアログボックスの表示と組み合わせたサンプルプログラムも作成しておいた。

TransferTextについて

AccessでCSVファイルなどの区切り記号付きテキストファイルをインポートするには、通常”TransferText”を使用する。

しかし、TransferTextでは、一括してデータを読み込むため個々のデータをインポート時に加工したりデータ型を変換するなどの細かい処理を指定できない。

インポート時の定義を作成し、定義を指定することである程度はカバーできるが、インポートするデータに何かしらの加工処理を実行した結果を保存する場合には役に立たない。

以下に、VBAの標準I/Oである”Line Input #”ステートメントを使用して区切り記号付きテキストファイルを読み込んでテーブルにレコード追加するサンプルプログラムを記述する。

“Line Input #”ステートメントを使用した基本サンプルプログラム

以下のサンプルプログラムは、”Line Input #”ステートメントを使用した基本的なサンプルプログラム。

テキストファイルを1行ずつ読み込み、読み込んだデータをイミディエイトウィンドウに表示する。

※イミディエイト ウィンドウは、[表示] – [イミディエイト ウィンドウ]で表示されます。

サンプルプログラム1

csvファイル(カンマ区切り)ファイルを読み込むサンプルプログラム

以下のサンプルプログラムは、”,”カンマでデータを区切って変数に代入し、個々のデータをイミディエイト ウィンドウに表示するサンプルプログラム。

代入された変数は自動的に配列変数になる。

サンプルプログラム2

もし、テキストファイルの区切り記号で区切られた各データが(“)ダブルクォーテーションなどの引用符でくくられていて、その引用符削除したい場合は、以下のように変更する。

変更前

変更後

csvファイル(カンマ区切り)を読み込んでテーブルにレコードとして保存するサンプルプログラム

以下のサンプルプログラムは、CSVファイルを読み込み、1行分のデータを1レコードとしてテーブルに追加するサンプルプログラム。

サンプルプログラムを実行するデータベースとしてAccessに添付されているサンプルデータベース”Northwind.mdb” を使用している。

テーブルは、Northwind.mdbにある”運送会社”テーブルを使用。

読み込むデータは、カンマで区切られたCSVファイルで、ファイルの内容は以下のとおり。

サンプルcsvファイルの内容

1行目が項目名になっており、各データは引用符(“)でくくられてカンマで区切られている。

ファイル名を”運送会社.csv”とした。

サンプルプログラムを実行する際は、上記のCSVファイルを作成し、”C:\temp”フォルダ(tempフォルダがない場合は作成する)に保存し、サンプルプログラム自体は、Northwind.mdbに標準モジュールを追加して貼り付ければよい。

このCSVファイルをインポートする上で注意するべき点は、以下の3点。

  1. 1行目の項目名はテーブルに追加する必要がないので無視する。
  2. 追加先の”運送会社”テーブルの1列目は、オートナンバー型で定義されているためデータを代入することができない、したがってCSVファイルの1列目のデータは無視する。
  3. データを囲っている引用符(“)ダブルクォーテーションを省く。

上記の注意点を踏まえてサンプルプログラムを作成すると以下のようになる。

サンプルプログラム3

ポイントとしては、カンマで区切られた各データが配列として変数に代入される点。

インポート時に特定のデータに何かしらの加工処理をしたければ、そのデータが格納されている配列の要素を加工してからフィールドに代入すればよい。

トランザクション処理を使用したサンプルプログラム

サンプルプログラム3のプログラムをトランザクション処理に変更した。

トランザクション処理にすることにより、何らかのエラーがあった際にロールバック(巻き戻し)させ処理を行う前の状態に戻すことができる。

簡単に言うと実行された処理を「なかったことにする」ことができる。

データが途中まで入った状態でエラーとなって処理が止まってしまったりした場合、どこまで処理が進んでいたかなど考慮する必要がないため、余計な手間をかけないで済む。

また、以下のサンプルプログラムには、[ファイルを開く]ダイアログボックスを表示させ、ユーザーが任意のファイルを指定できるようにプログラムに追加し、より汎用性を高めた。

AccessのVBAを使用して[ファイルを開く]ダイアログボックスを表示するサンプルプログラムについての詳細は、以下の記事を参照。

AccessのVBAで”WizHook”オブジェクトを使用して[ファイルを開く]、[名前をつけて保存]ダイアログボックスを表示する方法について覚書。
AccessのVBAでWizHookオブジェクトを使用してもしくは、ダイアログボックスを表示する際に選択される初期フォルダを任意のフォル...
AccessのVBAで"FileDialog"オブジェクトを使用して[ファイルを開く]ダイアログボックスを表示させて利用するサンプルプログラムを覚書。
AccessのFileDialogオブジェクトを使用して複数のファイルを選択できるダイアログボックスを表示して利用する方法について覚書し...

サンプルプログラム4

“Line Input #”ステートメントを使用したサンプルプログラムまとめ

“Line Input #”ステートメントは、データを1行ずつ読み込むため基本的に処理速度は遅いけど細かなデータ加工ができたりするので未だによく利用する。

これまで作ったプログラムの中には、数十万件のcsvファイルの内容を不正なデータがないかチェックしつつデータベースに登録していくような処理に使ったことがあって、確かに処理には時間がかかったけど、ほったらかしでいいので特に気にならなかった。

当然デイリーで動作するバッチ処理なんかだと全体の処理時間を考慮する必要があるので注意しないとならないが、特に処理時間にシビアな状況でなければ十分使えると思う。

よく使うので自分のために覚書。

コメント

  1. 読ませていただきました。
    大変参考になりました。
    これからも、良い情報の発信をしていだければと思います。
    ありがとうございました。

  2. 880 より:

    社内的なデータ処理用のツールレベルで、繰り返し使うのでVBAで作成することを検討。そしてここのサンプル3にたどり着きました。ありがとうございます。参考にさせてもらいます。

  3. 新人2年目 より:

    大変参考になりました。
    また、同じ様にエクセル版のインポートもご教授願います