Line Inputは遅くない
私のセミナーを受講された方から質問されました。VBAでCSVを読み込むときの話です。
CSVを読み込むとき、Line Inputを使っているんですけど、CSVの行数が多いと時間がかかります。ネットで調べたら、Line InputよりQueryTableを使った方が速いって書いてあったんですけど、どうなんでしょう？
こうした誤解をしている方がとても多いので、ここに書いておきます。
まず、読み込むCSVをご覧ください。
こんな感じのCSVです。これ100,000行あります。まずはLine Inputで読み込んでみましょう。ここでは、次のコードでやってみます。
Sub Macro1()
Dim buf As String, A As Variant, i As Long, j As Long
Open "C:\Data\Work\sample.csv" For Input As #1
Do Until EOF(1)
i = i + 1
Line Input #1, buf
A = Split(buf, ",")
For j = 0 To UBound(A)
Cells(i, j + 1) = A(j)
Next j
Loop
Close #1
End Sub
4分20秒くらいですか。さすがに100,000行ともなれば、このくらいの時間はかかるでしょう。ちなみに検証している環境は「CPU:i7-7500, メモリ:16GB, Windows 10 Pro, Excel 2016(1901)」です。では、速いと言われているQueryTableで、やってみましょう。
Sub Macro2()
With ActiveSheet.QueryTables.Add("TEXT;C:\Data\Work\sample.csv", Range("A1"))
.TextFileCommaDelimiter = True
.Refresh
End With
End Sub
おお！速いじゃないですか。なるほど、確かにこの結果だけを見たら「そっか、Line Inputを使うと遅いのか」と誤解するかもしれませんね。
やってることが違う
速度の前に、これ、そもそもやっていることが違うと理解していますか？OpenとLine Inputは、CSVのデータを直接読み込んで、そのデータをセルに代入しています。CSVとセルはリンクしていません。だから、代入したあとでCSVを変更しても、その変更はセルに反映されません。
対してQueryTableは、データ接続です。元のソースファイル(ここではCSVファイル)への接続を作って、ソースファイルとセルをリンクさせているんです。手動でやるなら、これですね。
ブック内のデータ接続を確認してみれば一目瞭然です。
ちなみに、ソースファイルの名前を変更したり、別のフォルダに移動したりすると、当たり前ですがリンクは途切れます。その状態で更新すると、
まぁ、そうなりますわな。よろしいですか？どっちがイイとかじゃなくて、そもそもやっていることが違うんです。やっていることが違うんですから、本来なら「どっちが速いか」と比べるような話ではありません。われわれは、VBAの速度コンテストに出るんじゃないんです。業務でExcelを使ってるんです。であれば「ここはリンクしよう」とか「ここはデータを読み込もう」など、業務として必要な処理があるはずです。それに従ってマクロを作ります。単純に、マクロのことだけを考えればいいのではありません。
Line Inputは遅くない
でもまぁ、確かに両者の速度差は大きいです。でも、ちょっと待ってください。この結果をもって
Line Input → 遅い
QueryTable → 速い
と考えるのは早計です。
安易に考えれば、両者は「Line Inputを使ったマクロ」と「QueryTableを使ったマクロ」です。でも「Line Inputを使ったマクロ」は、次のことをしているんです。
多くの人は、この(1)～(3)のうち「100,000回も、1行ずつ読み込んでいるんだから、きっとこれが犯人(遅い)だろう」って思うようですね。じゃ、やってみましょう。100,000行のCSVをLine Inputで読み込むだけで、どれくらい時間がかかるのか。
Sub Macro1()
Dim buf As String, A As Variant, i As Long, j As Long
Open "C:\Data\Work\sample.csv" For Input As #1
Do Until EOF(1)
'' i = i + 1
Line Input #1, buf
'' A = Split(buf, ",")
'' For j = 0 To UBound(A)
'' Cells(i, j + 1) = A(j)
'' Next j
Loop
Close #1
End Sub
0.141秒です。Line Inputで100,000行読み込むのは、264.516秒のうち、0.141秒しかかかっていないんです。現在のパソコンは高性能です。CPUもメモリもバスも、めちゃくちゃ速いです。Windowsだって進化しています。Windowsのファイルシステムは、100,000行くらいのテキストファイルだったら、1行ずつ読んだってこの程度です。これで(1)は犯人ではないと分かりました。じゃ、もしかしたら(2)が遅いのでしょうか？やってみましょう。
Sub Macro1()
Dim buf As String, A As Variant, i As Long, j As Long
Open "C:\Data\Work\sample.csv" For Input As #1
Do Until EOF(1)
'' i = i + 1
Line Input #1, buf
A = Split(buf, ",")
'' For j = 0 To UBound(A)
'' Cells(i, j + 1) = A(j)
'' Next j
Loop
Close #1
End Sub
ご覧のとおりです。Split関数で文字列を配列に変換するのなんて、100,000回やったって0.1秒程度しかかかりません。これで分かりましたよね。遅いのは(1)のLine Inputではなく、(3)の「セルへの代入」なんです。犯人はこいつです。「Line Inputを使うと遅い」ってのは"えん罪"です。Line Inputが泣きながら訴えてますよ「俺じゃない！遅いのは俺じゃないんだ！」って。
まぁ、考えてみれば当然です。これ、セルへの代入が500,000回行われているのですから。
ちなみに私だったら、配列の要素を1つずつセルに代入するなんてことはしません。そんなもの、やる前から遅いと分かっています。一次元配列を横方向のセル範囲に代入するんだったら、次のようにします。
Sub Macro3()
Dim buf As String, i As Long
Open "C:\Data\Work\sample.csv" For Input As #1
Do Until EOF(1)
i = i + 1
Line Input #1, buf
Cells(i, 1).Resize(, 5) = Split(buf, ",")
Loop
Close #1
End Sub
59.903秒でした。これ、当初の264.516秒から見れば、だいたい「4.5分の1」です。誤差も考慮に入れて、ここは「約5分の1」と考えるとわかりやすいです。そりゃそうです。セルへの代入が5分の1になったのですから。
ちなみに、もっと速くやるなら、データを一度二次元配列に入れて、セルへの代入は1回で済ませます。このへんの考え方は、
を、ご覧ください。
Sub Macro4()
Dim buf As String, A As Variant, i As Long, j As Long
ReDim B(99999, 4)
Open "C:\Data\Work\sample.csv" For Input As #1
Do Until EOF(1)
Line Input #1, buf
A = Split(buf, ",")
For j = 0 To UBound(A)
B(i, j) = A(j)
Next j
i = i + 1
Loop
Close #1
Range("A1").Resize(100000, 5) = B ''1回だけ代入
End Sub
おやおや、QueryTableより速くなっちゃいましたw
マクロの速度を気にするのでしたら、「マクロが遅い」とか「マクロが速い」みたいに"マクロ全体"の実行速度を見てはいけません。そのマクロの中でどんな処理をしているのか。その処理のうち、遅いのはどこかを検証してください。そうでないと今回のように「Line Inputを使っているから遅い」とか「QueryTableを使ったから速い」のように、的外れな判断になってしまいます。