概要
Excel VBAでCSVオープンするときのパフォーマンス比較です。
ついでにCSVオープン用のクラスモジュールも作ったので紹介してます。
比較対象
下記方法の実行時間を比較します。
- Workbooks.Openでファイルオープン + セル参照
- Workbooks.OpenTextでファイルオープン + セル参照
- Openで開いて1行ずつ読む + Splitで分割
- OpenAsTextStream.ReadAllで全部読む + 正規表現で解析
- QueryTablesを使ってファイルオープン + セル参照
結果
ちょうど環境があったExcel 2007とExcel 2013での計測結果です。
横軸は、10個のCSVファイルのオープンを3回行ったときの処理時間になってます。
- 遅い。読み込むだけなら使わない方がいい。
- 遅い。読み込むだけなら使わない方がいい。
- ダントツ早い。ただし、ダブルクォーテーション内にカンマがあると正しく読めない。
シンプルなCSVであればオススメ。 - そこそこ早い。CSVにダブルクォーテーションが入っている場合は、こちらがオススメ。
- そこそこ早い。読み込んだ結果をシートに貼り付けたい場合は、こちらがオススメ。
実際のコード
以下に置いてます。
https://github.com/minoru-nagasawa/ExcelVbaCsvReader/
https://github.com/minoru-nagasawa/ExcelVbaCsvReader/tree/master/PerformanceData
計測コードの中身
以下が計測に使用したコードです。
※ 各関数の呼び出し元でApplication.ScreenUpdating = Falseしてます。
1. Workbooks.Openでファイルオープン + セル参照
MainModule.vb
Private Sub BookOpen()
Dim count As Long
count = 0
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim folder, file
Set folder = fso.GetFolder(Range("FOLDER"))
For Each file In folder.Files
Dim wb As Workbook
Set wb = Workbooks.Open(file.Path)
Dim i As Long
i = 1
Do While wb.Worksheets(1).Cells(i, 1) <> ""
count = count + wb.Worksheets(1).Cells(i, 1)
i = i + 1
Loop
wb.Close
Set file = Nothing
Next
' 念のため解放
Set folder = Nothing
Set fso = Nothing
End Sub
2. Workbooks.OpenTextでファイルオープン + セル参照
MainModule.vb
Private Sub BookOpenText()
Dim count As Long
count = 0
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim folder, file
Set folder = fso.GetFolder(Range("FOLDER"))
For Each file In folder.Files
Call Workbooks.OpenText(file.Path)
Dim wb As Workbook
Set wb = Workbooks.Item(file.name)
Dim i As Long
i = 1
Do While wb.Worksheets(1).Cells(i, 1) <> ""
count = count + wb.Worksheets(1).Cells(i, 1)
i = i + 1
Loop
wb.Close
Set file = Nothing
Next
' 念のため解放
Set folder = Nothing
Set fso = Nothing
End Sub
3. Openで開いて1行ずつ読む + Splitで分割
MainModule.vb
Private Sub OpenSplit()
Dim count As Long
count = 0
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim folder, file
Set folder = fso.GetFolder(Range("FOLDER"))
For Each file In folder.Files
Dim num As Long
num = FreeFile
Open file.Path For Input As #num
Dim line As String
Do While Not EOF(num)
Line Input #num, line
Dim splitted
splitted = Split(line, ",")
count = count + Val(splitted(0))
Loop
Close #num
Set file = Nothing
Next
' 念のため解放
Set folder = Nothing
Set fso = Nothing
End Sub
4. OpenAsTextStream.ReadAllで全部読む + 正規表現で解析
MainModule.vb
Private Sub OpenAsTextStreamRegex()
Dim count As Long
count = 0
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim reader As CsvReader
Set reader = New CsvReader
Dim folder, file
Set folder = fso.GetFolder(Range("FOLDER"))
For Each file In folder.Files
Call reader.OpenCsv(file.Path)
Dim i As Long
For i = 1 To reader.RowCount
count = count + Val(reader.At(i, 1))
Next
Set file = Nothing
Next
' 念のため解放
Set folder = Nothing
Set fso = Nothing
End Sub
5. QueryTablesを使ってファイルオープン + セル参照
MainModule.vb
Private Sub BookQueryTables()
Dim count As Long
count = 0
' QueryTables.Addすると定義される名前
Dim tempQueryTableName As String
tempQueryTableName = "BookQueryTablesQueryTable"
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Temp")
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim folder, file
Set folder = fso.GetFolder(Range("FOLDER"))
For Each file In folder.Files
Dim qt As QueryTable
Set qt = ws.QueryTables.Add(Connection:="TEXT;" & file.Path, Destination:=ws.Range("A1"))
qt.name = tempQueryTableName
qt.TextFileCommaDelimiter = True
qt.RefreshStyle = xlOverwriteCells
qt.Refresh
qt.Delete
Dim i As Long
i = 1
Do While ws.Cells(i, 1) <> ""
count = count + ws.Cells(i, 1)
i = i + 1
Loop
ws.Cells.Clear
Set file = Nothing
Next
' 名前が定義されるので削除
Dim n
For Each n In ActiveWorkbook.Names
If InStr(1, n.name, tempQueryTableName) <> 0 Then
n.Delete
End If
Next
' 念のため解放
Set folder = Nothing
Set fso = Nothing
End Sub
CsvReaderクラスモジュールの紹介
計測のついでに、Workbooks.Openに近い感じで使えるCsvReaderクラスモジュールを作りました。
Workbooks.Openで開いて、Cells(row,column)で参照していた代わりに
CsvReaderを生成 + OpenCsvで開く + At(row,column)で参照すればいいので、容易に移行できるかと思います。
使い方は以下です。
CsvReaderを使ってCSVファイルを開くコード
' 生成
Dim reader As CsvReader
Set reader = New CsvReader
' 開く
Call reader.OpenCsv(ThisWorkbook.Path & "\CsvReaderTestCsv.csv")
' 参照
Debug.Print reader.At(1, 1)
参考：Workbooks.Openを使ってCSVファイルを開くコード
' 開く
Dim wb As Workbook
Set wb = Workbooks.Open(filePath)
' 参照
Debug.Print wb.Worksheets(1).Cells(1, 3)
' 閉じる
wb.Close
内部の動きとしては、OpenAsTextStream.ReadAllで全部読み込んで、正規表現で解析して、Dictionaryに格納してます。
https://github.com/minoru-nagasawa/ExcelVbaCsvReader/blob/master/CsvReader.cls
注意点：フィールドが数値の時、Workbooks.OpenだとExcelが勝手にTrimしてますが、CsvReaderは生値を返すため、結果が違う場合があります。