エクセルVBA 大容量CSVファイルの読み込みが遅い

800万行、1.4GBのCSVファイルをLineにより100万行ずつシートに読み込み、処理してます。
100万行読み込み
コンマを区切り文字にして展開
データ処理
次の100万行読み込み
の手順です。

出だしは快調で1シート20秒程度で読み込みます。ところが、シートを重ねるごとにだんだん遅くなり、１シート５分、最後の方はメモリーオーバーでエラーとなり、最後まで行き着いてません。ウインドウ下枠に表示される「フィル」で時間がかかります。その後で表示される「区切り位置」以降、データ処理は普通です。おそらくLineによる読み込みで時間がかかっていると思います。なぜでしょうか？どうすればいいでしょうか？

エラー位置はDoの３行下です。
Sub Macro1()

Dim vFile As Variant
Dim WSdata As Worksheet
Dim ffn As Long
Dim vA() As Variant
Dim j As Long, k As Long

Const Half As Long = 500000 ' １回の書き出し行数
Const CROWSZ As Long = 1000000 '1シートへの書き出し数
vFile = ThisWorkbook.Path & "\CSVconverted.CSV"

ReDim vA(1 To UnitRead, 1 To 1)

'100万行を入力するワークシート
Set WSdata = ThisWorkbook.Worksheets("CSV")

'CSVファイルを開く
ffn = FreeFile()
Open vFile For Input As #ffn

j = 1
k = 1
Do
Line Input #ffn, vA(j, 1)　　　'1行ずつ読み込み
If j >= Half Or EOF(ffn) Then
WSdata.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(UBound(vA), 1).Value = vA 'ここで止まる。vAにマウスを当てると「メモリーオーバー」と表示
ReDim vA(1 To UnitRead, 1 To 1)
j = 0

If k >= CROWSZ Or EOF(ffn) Then
　　　　　　'コンマを区切り文字にして展開
With WSdata.Range(WSdata.Cells(2, "A"), WSdata.Cells(Rows.Count, "A").End(xlUp))
Application.DisplayAlerts = False
.TextToColumns .Cells(1), xlDelimited, Comma:=True
Application.DisplayAlerts = True
End With
k = 0


'データ処理がここに記載


WSdata.Cells.ClearContents
End If
End If
j = j + 1
k = k + 1
Loop While Not EOF(ffn)
Close #ffn 'CSV閉じる

End Sub

質問者からの補足コメント

• データ処理部分をコメントアウトしてもトラブル再現します。
  ReDim削除はまだ試してません。
  
  これでもダメな場合、Line1行ずつセル代入する位なら、100万行ずつファイル分割あるいはファイル作成時点でサイズダウンします。

  No.1の回答に寄せられた補足コメントです。 補足日時：2016/02/12 18:29

• StartRowが利かない件、解決しました。拡張子がCSVだとダメなようです。txtで動作確認できました。どこかで読んだ気がします。ダメ元で2010で試して、後はファイル分割になりそうです。

  No.4の回答に寄せられた補足コメントです。 補足日時：2016/02/13 22:23

No.6 ベストアンサー 回答者： ママチャリ 回答日時： 2016/02/13 22:58

No.4ママチャリです。

まず①の件ですが、確かに Office 2013 and later となっていますね。でも、私の環境もExcel2010ですが、ヘルプにも掲載されているし、実際、StartRowで指定した行から読み込めています。各種ホームページを見ても2013より前のバージョンでも話題になっているので、問題ないと思います。

②の方ですが、OverTheGalaxyさんが書いたコード（Workbooks.OpenText Filename:=vFile, DataType:=xlDelimited, startrow:=ReadRow, comma:=True）を実際に動かしたところ、正常に動きました。確かにstartrowは大文字になりませんでしたが･･･。

試しに、[ファイル]ｰ[開く]の手順をマクロに記録し、それをベースに実験してみてはいかがでしょうか。各種HPを見ていて、「省略可能なパラメータと書いてあるくせに、省略したら問題が発生した」みたいな記事がありましたので･･･。

マクロを記録する際、列数が多いと FieldInfo パラメータがあふれることがあります。ただ、形式を指定する必要がなければ、不要なものなので捨てちゃってOKです。

では、ご検討をお祈りします。
それにしても、同じ環境（2010ではやっていないのでしたっけ？）で動作が異なるとは、Micr●s●ftらしいですね。

この回答へのお礼

ありがとうございます。
エクセル2010で動作確認出来ました。1900万行、14分。800万行は誤りでした。この方法のメリットは、データファイルの改行コードがLFであってもCRLFに変換する必要がない点です。

質問文のマクロ不具合理由が不明なままですが、こちらでいきます。

お礼日時：2016/02/15 18:52

No.5 回答者： 30246kiku 回答日時： 2016/02/13 08:14

＃３です

> ReDimが原因であっても、ReDim使えと書いてるように見えますが？

どのように解釈されても構いません


> ※ Do Loop 内で、上記を繰り返すのはやめた方が良い？
> 変数内部の値の初期化が目的？
> →どこまで埋めたかは j が持っているので不要
> ReDim することで、同じメモリ領域は使われず（？）メモリの取り合い？

この部分をかみ砕いてみたつもりでしたが・・・
そうなっていなかったようです

> 解決されるのかわかりません･･･が

失礼しました

この回答へのお礼

ありがとうございます。
ReDim有無の影響はありませんでした。原因は分からずじまいですが、別法で解決する目処が付いたので、そちらでいきます。

お礼日時：2016/02/15 18:37

No.4 回答者： ママチャリ 回答日時： 2016/02/12 23:06

「100万行ずつファイル分割」するような全面改修も考えているということなので、その際の参考になれはと思い書きました。

ずらずらと書いてしまいましたが、OverTheGalaxyさんなら理解していただけると思います。

パフォーマンスやメモリリークを考えると独自のロジックを駆使するよりも、Excelで用意されている機能を使って処理した方が効率が良いと考えます。

OverTheGalaxyさんは、CSVファイルを自力（Line Input）で読み込んだ後に、TextToColumnsメソッドで列分割をしようとしていますが、そもそもExcelには、CSVファイルをブックとして開くための OpenTextメソッドが用意されています。ただ、Excelでは、1048576行を超えるデータは読み込めないという制限があります。
しかし、OpenTextメソッドにはStartRowパラメータというのが用意されていて、CSVファイルの何行目から読み込むかを指定ができます。よって、1048576行超えた行を読み込めないという訳でもありません。要するに、1回目の OpenTextメソッドで1048576行目までを読み込み、2回目で1048577行目から読み込めば良いということです。（1回目で開いたブックを閉じてから、2回目を開く必要がありますが･･･）

ちなみに、通常の「開く」で1048576行を超えるCSVファイルを開くと「ファイル全体を読み込むことができませんでした。」のコーションが表示されますが、この操作をマクロに記録して実行すると何のコーションも表示されず、1048576行だけが読み込まれた状態となります。ということは、エラートラップはできないことになります。

上記のことを考慮してロジックを考えると次のような感じになります。
① OpenTextメソッドでCSVファイルを開く
②上記①で1048576行までフルに読み込まれた場合、1048575行目（Maxの１行前）までを有効として、データ処理を行う。フルに読み込まれていない場合は、読み込まれた行までのデータ処理を行い終了とする。
③上記②でフルに読み込まれていた場合（続きのデータがある場合）、StartRow=1048576行目から続きのデータを読み込み、②と同じ処理を行う。以降は②~③の繰り返し。
※1048576行目（Max行目）は、実際の処理には使わず、続きのデータがあるか否かの判断に使います。

この回答へのお礼

ありがとうございます。
別法で解決するものですね。早速試しましたが、36行→20行と非常にスッキリしました。しかし、問題が２個あります。
https://msdn.microsoft.com/ja-jp/library/office/ …
①エクセル2013以降です。実使用環境では2010なので使えないと思います。

②2013のあるパソコンで試したところ、
Workbooks.OpenText Filename:=vFile, DataType:=xlDelimited, startrow:=ReadRow, comma:=True
なぜかstartrowを認識しません。上記の通りStartRow（大文字）に変換されません。ReadRowの値にかかわらず読込は１行目からです。

①はともかく②はどこか誤りありますか？

お礼日時：2016/02/13 18:36

No.3 回答者： 30246kiku 回答日時： 2016/02/12 21:08

＃２です

> 20万行×5で上手く行くのでしょうか？

どの段階のものを指して言われているのかわかりませんが
ある程度の領域でセルをまとめて触った方が確かに速くはなります
それも程度によったと思います（嘘かも）
5万 → 20万 １回なら４倍の量を扱えますが、
20万はデータが多い分それなりに遅くはなったと思います
同じ量を書き出す際に、
・分割した方が良いのか
・１回の方が良いのか
幾度も検証されたらと思います

> ReDimが原因だった場合、
> Erase vA
> ReDim vA
> で解決するでしょうか？

私的には、これでは解決しないと思ってます

ReDim する際に、今までの領域をいつ解放？するか･･･

例えば、
ReDim vA(10000)
指定していたものに、また ReDim vA(10000) したとすると、

vA の要素 x 10000 のメモリを確保できたところで、
切り替え（旧領域を解放？）？が起きるのでは？

切り替えた後、内容を維持する ReDim Preserve なら
内容をメモリ間コピーするのでは？
なので、配列の大きさをを変更できるのは、最終次元（？）だけ？
vA(10, 3) → vA(10, 5)
には拡張できるけど
vA(10, 3) → vA(20, 3) はできない･･･

なので、旧 vA(10000) と、新 vA(10000) の位置は異なる？
メモリの取り合い？
10000 が 500000 なら取り合える範囲が･･･そう多くない？

> 出だし快調
は、メモリの取り合いしていないから･･･？かも？


> 最終回は中途半端な要素数になるので、初期化してから代入が必須です。

これは嘘ですね
ReDim vA(10000) したものに 123 分入れたとして、書き出す時に

Range("A1").Resize(123).Value = vA

すれば、vA が 10000 であろうが 123 分までしか書き出されない
（124 以降は書き出されない）
よって、初期化は不要です。

※ 行方向に書き出す時には２次元配列ですね
（上記は、雰囲気の例ということで･･･）

この回答へのお礼

ReDimが原因であっても、ReDim使えと書いてるように見えますが？

お礼日時：2016/02/12 21:36

No.2 回答者： 30246kiku 回答日時： 2016/02/12 07:09

解決されるのかわかりません･･･が

提示あった記述のベースは以下だったでしょうか？

大容量CSVファイルから、任意の行範囲をエクセル
http://detail.chiebukuro.yahoo.co.jp/qa/question …

100万行を作る際
・5万行 x 20回
・50万行 x 2回
さほど処理の差はなかったような気がします
（嘘だったらごめんなさい）

> ReDim vA(1 To UnitRead, 1 To 1)

ここでの UnitRead は幾つなのでしょう？

※ Do Loop 内で、上記を繰り返すのはやめた方が良い？
変数内部の値の初期化が目的？
→どこまで埋めたかは j が持っているので不要
ReDim することで、同じメモリ領域は使われず（？）メモリの取り合い？

> WSdata.Cells.ClearContents

何処までセルを使っていたとか情報が残っている（？）かも（？）

WSdata.Cells.Delete　にしてみて、どうなりますか？

なお、他列に何もない状態の時の「区切り位置」展開では
> Application.DisplayAlerts = False
> .TextToColumns .Cells(1), xlDelimited, Comma:=True
> Application.DisplayAlerts = True
の様に、DisplayAlerts で挟まなくてもメッセージは出なかったと思いますが
挟まれたということは、展開先の B とか C 列にデータがある･･･という事？

この回答へのお礼

ありがとうございます。
元が見つからないと思ったら、知恵袋でしたね。それを参考にしてます。

20万行×5で上手く行くのでしょうか？最初はエラー内容から考えて私もHalf=50万行×2を使いました。しかし今思うと「出だし快調、後半で固まる」と合わないかも知れません。刻みが原因ならば出だしで固まる筈です。

ReDimが原因だった場合、
Erase vA
ReDim vA
で解決するでしょうか？最終回は中途半端な要素数になるので、初期化してから代入が必須です。

区切り位置
初期段階で入れたので、大した考慮してません。挟まなくても問題ありません。

お礼日時：2016/02/12 20:01

No.1 回答者： kmee 回答日時： 2016/02/11 07:22

Excelの中で実際にどうやっているかはわかりませんが、

一般的には、大量にメモリを使う場合には次のようなことが起り得ます。

○スワッピング
Windowsには仮想メモリという機能があって、物理メモリで足りない部分をハードディスクに退避させ、必要に応じて物理メモリに戻す、ということを行なっています。
例えば、100MB足りなくなったので仮想メモリで補う、としたときには、100MBのファイルの読み書きが発生する、ということです。
これがしょっちゅう発生していたら、それだけで遅くなる、ということがわかるかと思います。

○メモリの確保と解放、フラグメント
新しいシートを作成したとき、シートの使用セルが増えたとき、redimを使ったとき、等、新しいメモリ領域を確保しようとします。
シートを削除したとき、redimを使ったとき、等、使わなくなったメモリ領域を解放します(おそらく)

確保と解放を繰り返すと、領域がバラバラになるフラグメントという現象が発生しやすくなります。
確保だけして解放されなければ、メモリ使用量はどんどん増えます。
増えればスワッピングが発生しやすくなります。


・ReDim vA(1 To UnitRead, 1 To 1) って必要ですか?
例えば、Fillを使わず
Dim l as Variant
...
Line Input #ffn, l　　　'1行ずつ読み込み
WSdata.Cells(j+1, "A").Value=l
とすれば、vAのような大きな配列は不要になります。

※ さらに、データ処理が1行ずつやるものなら、1行読み込み→分割→データ処理とすれば、CSVシートで使うのは1行だけで済みます。
※ さらに、分割をVBAの中で処理すれば、CSVシート自体が不要です
※ さらに、データ処理結果を直接ファイルに書いてもいいなら、結果用シートも不要です。というか、Excel自体が不要です。


> データ処理がここに記載

とありますが、ここでは実際にどんなことをしてますか?
・毎回出力シートを作成して、ということなら、それがメモリ不足の原因かもしれません。
　データの入ったシートをコピーして枚数増やしていったら、予想される枚数以上に増やせますか?
・そうでなくても、出力用シートにデータが追加されていくなら、やはりメモリ使用量がどんどん増えていきます
・この中で、解放しわすれているものはありませんか?
　例えば Set A=〜 としたものは Set A=Nothing をしないと、解放されないことがあります。

この回答へのお礼

ありがとうございます。
個別に説明します。
ReDim vA(1 To UnitRead, 1 To 1)
トラブルシューティングの過程で、多少なりともメモリー減るかと思い入れました。回答内容からすると不要の様ですね。

フィル
何を意味するのか分かりませんでしたが、配列をセルに一気に代入する操作の様ですね。一行ずつ代入すると、かなり時間が掛かりそうですが、それで解決するなら試します。ただ、これが原因となると、最初は快調なのがよく分かりません。

データ処理
シート数の増減ありません。データは貯まりますが、精々500行です。この処理でオブジェクトは確かに使ってますが、EndSubで開放されると聞いてるので特にnothingは使ってません。一番上の宣言領域？でも勿論宣言してません。ただ、やったことは無いので試してみます。

シート
100万行のシートは1枚だけです。ここに毎回上書きしてます。

お礼日時：2016/02/11 08:44