HOME > VBA高速化 > 【第2位】CSVのシート取り込み

エクセルVBA高速化ランキング【第2位】


前回の第2号では、高速化ランキングの第1位ということで、初回の第1号で ご紹介した「画面更新の非表示」について、さらにもう一歩踏み込んだ「半 非表示」というテクニックをご紹介しました。

それでは早速、今回は続くランキングの第2位の発表です。


【CSVデータ】をエクセルシートへ取り込む際の高速化



データベースやその他のシステム等々から得たデータを、エクセルのシート に落として、集計やその他色々なデータ加工をするということは実に多いで すよね。読者の皆さんでそのようなことを頻繁にやられているという方も多 いことと思います。

そこで、今回の高速化ランキングの第2位としてご紹介するのは、大容量の CSVデータをVBAでエクセルシートへ取り込む際の高速化 についてです。


まずは、もっともポピュラーな方法(物理的にテキストファイルとしてCSV データを読み込んでからエクセルシートに落としていく)を用いた場合で (サンプル1)ですが、これはおのずと大変に時間が掛かります。


サンプル1(※宣言文や関数の中身等は省略しています。)
―――――――――――――――――――――――――+
    Open "C:\data.csv" For Input As #5
    j = 1
    Do While Not EOF(5)
        Line Input #5, bufline(j)
        j = j + 1
    Loop
    Close #5
    n = j - 1
    
    Application.ScreenUpdating = False
    For j = 1 To n
        Call CsvBuf1(bufline(j), a, m) 
        For i = 1 To m
            Cells(j, i).Select
            ActiveCell.FormulaR1C1 = a(i)
        Next i
    Next j
―――――――――――――――――――――――――+
このサンプル1での処理時間は 110秒です。

※今回のベンチマークでは、2万行程度のCSVデータ (ファイルサイズにして2.5MB程度のもの)を用いて 計測しています。


一般的に、言わばこの方法が王道でありますのでプログラマーとしてはどう してもこの方法でやりたいと思うのが人情であるわけなのですが、これをや ってしまっているが為に、ものすごーーく遅いエクセルのツールというのを 私はこれまでに何度も目にしたことがあります。

とにかく、シートへの書き出しに非常な時間がかかりますので、前回紹介し た「更新の非表示」を使った場合でも、この方法ではかなり重たくなってし まうことは避けられません。


なので、もっと早くなる方法としてまず次のサンプル2の方法があります。

このプログラムは、簡単に「マクロの記録」を使ってファイルを開く操作で CSVファイルを開いて自動記録させれば得られる最も簡単なプログラムです。


サンプル2
―――――――――――――――――――――――――+
    Workbooks.Open Filename:= "C:\data.csv"
―――――――――――――――――――――――――+
このサンプル2では 37秒でした。

これであれば、先ほどのサンプル1の場合に比べて約3倍早くなるという結 果が得られたことになります。

2万行のCSVデータをエクセルシートに取り込むのに37秒という数字は、(こ れが早いと言えるか、遅いと言うべきなのか?)実に微妙な数字ですね。


で、何とかもう少し早くする方法はないものか?と言うと、次のサンプル3 の様なファイル形式を変えてから取り込む方法というものがあります。


一文が長いので途中省略して書いていますが、このプログラムも「マクロの 記録」を使ってファイルを開く操作でtxtファイルを開き、区切り文字の設 定等して自動記録させれば簡単に得ることのできるプログラムです。


サンプル3
―――――――――――――――――――――――――+
    Workbooks.OpenText Filename:= "C:\data.txt" _
        , Origin:=932, StartRow:=1, DataType:=xlDelimited, ・・・ _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=T ・・・ _
        Comma:=True, Space:=False, Other:=False, FieldInfo ・・・ _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1) ・・・ _
        TrailingMinusNumbers:=True
―――――――――――――――――――――――――+
このサンプル3では 27秒でした。


この結果、先ほどのWorkbooks.OpenよりもOpenTextを使った方が30%程度 早くなるということが分かります。

これは、事前にCSVファイルの拡張子.csvを、ただ.txtに変えておきさえす ればよいだけなので、(FileCopy等を使えばプログラム内でも簡単にできま すし)至って簡単な話です。


実は、もう一つ、画期的に早くする裏わざというのがあります。
これは、まさしく裏わざというべきものなので、この方法を用いることを 特におすすめするものではありませんが、あくまで参考までにということ でご紹介します。


サンプル4
―――――――――――――――――――――――――+
    MyFile = "text;" & "C:\data.csv"
    With ActiveSheet.QueryTables.Add(Connection:=MyFile, ・・・
       .Name = "link1"
       .TextFileCommaDelimiter = True
       .TextFilePlatform = 932
       .Refresh
    End With
    
    ActiveWorkbook.Names("link1").Delete
―――――――――――――――――――――――――+
このサンプル4の結果は 6秒でした。


まぁ、2万行で6秒程度であれば、誰からも文句は出ない十分なスピードで はないかと思います。


ちょっと、サンプル4について解説しますと、
これは、「外部データの取り込み」や「クエリテーブル」といったExcelの 既存の機能を使ってやる方法なんですが、ちょっとこの難点は、これらの Excelの機能を使うと必ず外部リンクが張られてしまうという所にあります。

なので、サンプルの最後の行でわざわざその外部リンクを削除する処理を入 れてやる必要があります。
断っておきますが、これはExcelの既存の機能をその本来の目的とはまった く違う目的で、しかもプログラムで用いるということの危険性が大いにあり ますので、あくまでもご参考までです。



最後に、今回のベンチマークテストの結果をまとめますと、

Workbooks.Openで3倍早くなる。
Workbooks.OpenTextなら更に30%早くなる。
裏わざを使えば2~3万行が10秒以下も可能に。

ということです。


 

----------【サンプルテストの実行環境】----------

   PCスペック:Pentium4 1.8GHz
   OS    :Windows XP
   Excel   :Microsoft Excel2003 or 2002

----------------- r(-●_●-) -----------------



この記事は、「GoGo エクセルマクロをはじめよう!」を運営するWATエクセルカスタマイズセンターが実施した速度計測 ベンチマークテストによる結果を基にした高速化手法テクニックを順位付けして発表するものです。 (2007年発行の「エクセルVBA技術通信」のバックナンバー記事からの抜粋)




NEXT >>
大量のデータをソートする場合の高速化
【コラム】間違いだらけのマクロ高速化手順

目次へ戻る
 

 


初心者の皆さんへ

こんにちは、「Go!Go! エクセルマクロをはじめよう!」筆者の三太郎です。
私はこの道25年、現役バリバリのベテランSE(システムエンジニア)をしています。 なので、業界にどっぷり染まってしまった IT業界人 です。(笑)

マクロ(エクセルのプログラミング)がどれだけ便利なものなのか・・・ ということは、 皆さんもうよくお分かりいただけてる(!?)ものと思いますが、でも 「やっぱ、中々取っつき難い、ハードルが高い、素人には難しい。」 そう感じている方も多いかと思います。

エクセルをほぼ1日中使ってる人が多い職場であっても、マクロを使える人はほとんど居ません。 (30人の職場で精々1人か2人居ればいいほうかと思います。。) 私は、そのような現状(PC仕事の非効率)を改善して、日本にもっと効率良いIT化の機運を 高めて行きたい!そう願って、今から十数年前にこのようなメルマガ講座を始めました。

マクロを始める為の条件は、エクセル上級者ではありません! 頻繁にエクセルを使ってる人、 ただそれだけです。エクセルの操作レベルはまったく関係がありませんので、 エクセルユーザーの全ての皆さんが当メルマガ講座の参加対象者です。

パソコン仕事で この上なく便利なエクセルのマクロ というものを、もっと多くの人に知って欲しい、使って貰いたい。その想いだけで、長年メルマガの 無料配信を続けてきました。今では、この分野では異例とも言える1万人を超える大勢の皆さんに ご登録いただいているメルマガ講座に成長いたしました。 読者の皆さんからのご声援のお陰です。本当、ありがとうございます。

当メルマガ講座では、簡単に出来るマクロ作成法のコツとその手順を教えています。 とにかく作って、動かす。だから楽しくなってきます。 VBAのカタカナ用語や難しい仕組みの理解、構文暗記といった従来型の不必要な勉強は一切しません! なぜなら、エクセル作業の自動化にその必要は一切ないからです。 初心者がすぐに挫折するカタカナ用語羅列のマクロ勉強なんて、殆どの人には役に立ちません!

マクロとは、エクセルの作業を自動化する為の道具に過ぎません! マクロを組む為に難しいプログラムの仕組みや わけのわからないカタカナ専門用語を覚える必要など毛頭ないわけです。 私はこれまで十数年間、大勢の読者に教えてきて、Excel自動化に成功した沢山の人を生み出してきた 経験で、そう断言します! (本屋に並ぶVBA参考書のライターレベルの人の言うことを真に受けて、 あなたに必要のない”勉強の為の勉強”をしてしまわぬよう、 くれぐれもご注意ください!!

ここでは、「これからマクロを始めてみようかな?」と思ってる方や、 すでにどこかで勉強して「すぐに挫折してしまった・・(>_<)」という方に、 安心して勉強のできる方法とその実習環境とが用意されています。 もし、あなたが過去に挫折した経験者であれば、きっと私が常々言っている 勉強すべき事とすべきでない事 その違い(ここの初心者学習環境の素晴らしさ・・)というものがすぐに分かっていただけるだろうと思います。

マクロ(VBA)というのは多義に渡ります。アマチュアのサンデープログラマーから ベテランの上級者やプロに至るまで、実に幅が広いものです。 初心者には到底 必要のない 難しい部分まで勉強してしまうから当然、 必ずずぐに挫折する事になります。

残念ながら、「まだピカピカの小学一年生(初心者)に、いきなり掛け算や割り算はおろか、 三角関数や微分積分までも教えてしまうような痛ましい光景」を、ネットでも参考書でもセミナーでも、 VBAの世界ではたくさん目にします。

だから、「何を勉強するか」ではなく「何を勉強しないか」 初心者にはその正しい選択が重要なんです。勉強の範囲を 初心者の領域(Excel業務の効率化)だけに絞ってやりさえすれば 、さほど難しく考える必要はありません。それで、難しい、ちんぷんかんぷんだと思っていた マクロが、楽しく、 どんどん楽しく、勉強できるようになります。

→ エクセルマクロを10分で理解する!(YouTube動画)


あなたもこの三太郎式マクロ勉強法で、面倒なExcel仕事の自動化を ぜひ、この他にはない画期的な方法で実現してください。あなたの 参加を(下記無料メルマガへのご登録を)お待ちしてます!! (少々、力説し過ぎて話長くなりました。すみません。m(__)m)

 
最新号 は、下記にアドレス登録すると無料配信されます。

▼マクロ講座の登録はこちら((無料)) まぐまぐ
Go!Go! エクセルマクロをはじめよう! (マガジンID:0000135169)   
メルマガ登録
  メールアドレス:

ロジック編ランキング

  • プログラムのロジックを駆使したVBA高速化の王道テクニックを公開。 (現在準備中...)

 

読者数1万人達成しました!

エクセルで 最大級の
メールマガジン。
まぐまぐ殿堂入り

最新号は 今すぐ ↓↓↓ 登録

メールアドレス:

Powered by
まぐまぐ

完全まぐまぐ配信なので登録安心です!