VBAと日々のこぼれ話

  1. VBA

【VBA】テキストファイル(.csvや.txt)をExcelに高速で読込み・書き出ししたい

出来れば処理は早い方が良い

テキストファイルを読み込んで、Excelシートに書き出す方法はいくつかあります。
Openステートメントでファイルを開いて1行ずつシートに貼り付けるのが一番分かりやすいかな。他にもFSOオブジェクト、QueryTable、Workbooks.OpenText、この辺りがExcelVBAでのデータ読込み・書出しにおいてメジャーですよね。

ですが、厄介なことにループ処理はデータ量が多くなると時間がかかります。またそもそもシートの書式設定をしておかないと「001」という文字列が「1」になってしまう事も悩みの種です。

悩みの種
・処理時間
・貼り付け時の書式

私はAccessVBAをよく利用するのですが、データの取り込み先はシートではなくテーブルです。
その名残というか癖で、上記のメジャーな方法ではなくSchema.iniを利用した方法を利用しています。schema.iniの作成という事前作業が必要ですが、書出しにループ処理を実施していないため体感的に処理速度は速いです。また、「001」の文字列データは「001」と文字列としてセルに書き出してくれます。

Schema.iniとは

詳しい説明は省きますが「テーブル定義情報ファイル」のことだと思って頂ければ良いと思います。取込ファイルベースで記載すると「取込ファイルデータの定義情報ファイル」です。

Schema.iniには、テーブル名(取込ファイル名)、区切り文字、項目名、項目の型といった情報が記載されており、その情報に則って取込ファイルのデータを扱えます。もっと言うと、取り込みファイルを一つのテーブルとみなして操作することが出来るので、ExcelVBAでSQLが発行できます
ただ今回は、データの取り込みとExcelブックへのデータ書き出しなのでSQL云々には触れません。

事前準備

インプットデータ(test.txt)の中身

商品コード,商品名,単価,最終仕入日
0001,ピーナッツ(殻付き),1320.6,2018/5/14
1000,充電式電池,804,2018/3/20
0567,ビニール傘,520,2017/12/22

Schema.iniへの設定内容

[test.txt]
ColNameHeader=True
Format=CSVDelimited
Col1=商品コード Text
Col2=商品名 Text
Col3=単価 Double
Col4=最終仕入日 Date

記述する内容は、様々なパターンがあるので「Schema.ini」で検索してみてください。
記述ポイントとしては、項目の設定時には項目名と型の間等は『半角スペース』です。配置場所は、取込ファイルと同じ場所に必ず置いてください。またファイル名は「Schema.ini」固定です。

参照設定「Microsoft ActiveX Data Objects X.X Library」

今回はADO接続を行うので、ライブラリ「Microsoft ActiveX Data Objects X.X Library」の参照設定が必要です。
「X.X Library」の「X.X」は一番値が大きいもの(最新)だけにチェックを入れてあげれば良いです。
もちろん、参照設定を行わずにCreateObjectを利用する方法もあります。CreateObjectを使う方法は、おまけを参照してください。

テキストファイルの読み込み・書き出し

test.txtをschema.iniの定義通りにシート「出力」に書き出す処理のサンプルコードです。
シートに値を貼り付ける処理の前の部分については、フィルタの解除とシート内のデータ削除を行っています。
機会があれば、どういう意味合いの事をやっているのかの記事を書きたいと思います。

ADODB.Connectionのプロバイダ名

プロバイダ名(.Provider)は、使用しているExcelのバージョンやWindowsのシステムの種類(32ビット or 64ビット)によって異なります。今回のサンプルコードは、Excel2016の64ビット版利用です。

【スポンサーリンク】



おまけ:参照設定を行わないコード

【スポンサーリンク】



VBAの最近記事

  1. 【VBA】未だに苦手としていること

  2. 【VBA】モジュール先頭に記述する4種類のOption キーワード ステートメント

  3. 【VBA】同じ階層体系のフォルダを量産したい

  4. VBAの腕を知るための3つの短い質問を考える

  5. 【VBA】ExcelやテキストへDAO・ADO接続する際に使用するISAMフォーマット一覧…

関連記事

【スポンサーリンク】




PAGE TOP