Excel VBA 高速でCSVファイルを読み込む「Open~LineInput」

今回は高速でCSVファイルを読み込む方法をご説明します。

この方法はタイトルにあるように、CSVを読み込む一般的な「Open~LineInput」の方法を高速化しています。

通常の読み込み方法で73秒かかる処理が、3秒くらいで完了します。

通常のCSVの読み込み方法と詳細は「CSVファイルを読み込む「Open~LineInput」をご覧ください。

また、手動で読み込む方法や、CSVとは？については「CSVファイルの正しい読み込み方」をご覧ください。

1.高速でCSVファイルを読み込むサンプルコード
3.通常のコード
- 全て都度出力する方法
- 1行ごとにまとめて出力する方法
4.処理速度比較とまとめ

1.高速でCSVファイルを読み込むサンプルコード

サンプルデータ

サンプルコードの検証用に次のような10万行のデータを用意しました。

また、実務では列数が特定できないことも考慮して、F~G列には1行置きにデータを入力しています。

これで各行で最終列が異なっても漏れなく読み込めるか確認出来ます。

サンプルコード

高速でCSVファイルを読み込むサンプルコードです。

処理速度は10万行読み込みで3.25秒でした。

Sub Sample1()

Dim FileNum     As Long
Dim i           As Long
Dim n           As Long
Dim myStr()     As String
Dim myRec       As String
Dim FSO         As Object
Dim TargetFile  As String
Dim FileRow     As Long
Dim csvArray()  As Variant
Dim MaxCol      As Long

TargetFile = "C:\Sample\Book1.csv"

Set FSO = CreateObject("Scripting.FileSystemObject")

With FSO.OpenTextFile(TargetFile, 8)

    FileRow = .Line
    .Close
    
End With

Set FSO = Nothing

FileNum = FreeFile

i = 0
MaxCol = 0

Open TargetFile For Input As #FileNum

Range("A:F").NumberFormat = "@"

Do While Not EOF(FileNum)
    
    Line Input #FileNum, myRec
    
    myStr = Split(myRec, ",")
    
    If MaxCol < UBound(myStr) Then MaxCol = UBound(myStr)
    
    ReDim Preserve csvArray(0 To FileRow, 0 To MaxCol)
    
    For n = 0 To UBound(myStr)
    
        csvArray(i, n) = myStr(n)
    
    Next n
    
    i = i + 1
    
Loop

Range(Cells(1, 1), Cells(FileRow + 1, MaxCol + 1)) = csvArray

Close #FileNum

End Sub

サンプルコードの説明

ロジックは次のようになっています。

特徴は出力までわからない列数を動的に対応できるところです。

対象CSVファイルの行数を取得(配列の範囲指定用)
Open~Inputで1行ずつファイルを読み込む
列数が動的に変更されても対応するように最大列数を更新する
全て配列に格納後、セルに一括出力する

行数を取得するには「FileSystemObject」を使用します。

「Set FSO = CreateObject(“Scripting.FileSystemObject”)」で格納します。

CSVファイルは追記で開くと「Line」プロパティが最終行となるため、Lineプロパティで行数を取得します。

「Open TargetFile For Input As #FileNum」のコードで対象ファイルのデータ取得しています。

「Range(“A:F”).NumberFormat = “@”」は数値を壊さないため文字列に書式を指定しています。

「Line Input #FileNum, myRec」で取得したデータを1行ずつ読み込んでいます。

「myStr = Split(myRec, “,”)」で読み込んだ行を「,」カンマで文字列区切りをしています。

ここからが一番の特徴で、列数が読み込むまで不明な場合に対応させます。

「If MaxCol < UBound(myStr) Then MaxCol = UBound(myStr)」で列数の最大値を更新する判定をしています。

「ReDim Preserve csvArray(0 To FileRow, 0 To MaxCol)」で事前に取得した行数と可変の列数で配列にしています。

事前に行数を取得する事で、データ格納用の配列の行数を固定に出来ます。

そのため、可変の列数を最終次元として動的に指定する事が可能です。

※2次元配列は最終次元しか範囲を変更出来ません。

配列の詳細については「配列の使い方(基礎編)」をご覧ください。

「For n = 0 To UBound(myStr) 」で区切られた文字列数分ループして配列に格納しています。

最後に一括でセルに出力して、「Close #FileNum」でファイルを閉じます。

3.通常のコード

比較対象として、通常のコードと検証結果です。

こちらは「Application.ScreenUpdating = False」で描画を停止しても速度はほとんど変わりません。

全て都度出力する方法

カンマ区切りで文字を区切り、すべて1つずつ出力しています。

処理時間は73.4秒でした。

Sub Sample2()

Dim FileNum As Long
Dim i       As Long
Dim n       As Long
Dim myStr() As String
Dim myRec   As String

FileNum = FreeFile

i = 0

Open "C:\Sample\Book1.csv" For Input As #FileNum

Columns(1).NumberFormat = "@"

Do While Not EOF(FileNum)
    
    i = i + 1
    
    Line Input #FileNum, myRec
    
    myStr = Split(myRec, ",")
    
    For n = 0 To UBound(myStr)
    
        Cells(i, n + 1) = myStr(n)
    
    Next n
    
Loop

Close #FileNum

End Sub

1行ごとにまとめて出力する方法

出力する「For n~Next n」の部分を「Range(Cells(i, 1), Cells(i, UBound(myStr) + 1)) = myStr」と書き換えて、1行1回の出力にしてみます。

処理時間は14.1秒でした。

Sub Sample3()

Dim FileNum As Long
Dim i       As Long
Dim n       As Long
Dim myStr() As String
Dim myRec   As String

FileNum = FreeFile

i = 0

Open "C:\Sample\Book1.csv" For Input As #FileNum

Columns(1).NumberFormat = "@"

Do While Not EOF(FileNum)
    
    i = i + 1
    
    Line Input #FileNum, myRec
    
    myStr = Split(myRec, ",")
    
    Range(Cells(i, 1), Cells(i, UBound(myStr) + 1)) = myStr
    
Loop

Close #FileNum

End Sub