数万行を超えるCSVをVBAでシートに読み込むと、数分かかったり、Excelが応答しなくなったりすることがあります。主な原因は、読み込んだ値をシートへ1セルずつ書いていることです。この記事では、ファイル全体を一度に読み込み、Splitで2次元配列に分け、シートへ1回で書き込む方法を説明します。処理時間の測り方と、先頭の0を消さずに貼る方法も扱います。
このコードが前提にしているCSV
| 前提 | 内容 | 当てはまらない場合 |
|---|---|---|
| 文字コード | Shift_JIS（CP932）。日本語版Windowsの標準の文字コードとして読む | UTF-8のCSVは、この読み方では文字化けする。文字コードを指定できる方法で文字列にした後、同じ分割・書き込みの手順を使う |
| 区切り | カンマ区切りで、値の中にカンマや改行がない | ダブルクォーテーションで囲まれた値の中にカンマや改行があると、Splitでは列や行がずれる。引用符を考慮して分割する処理が必要 |
| 列数 | 1行目が見出しで、すべての行の列数が同じ | 列が多すぎる行は数えて知らせる（後述） |
| 行数 | シートの最大行数（1,048,576行）以内 | ファイルを分割してから読み込む |
遅い書き方と速い書き方の違い
よく見かけるのは、1行ずつ読んで、値を1セルずつ書き込む書き方です（変数の宣言などは省いています）。
Do Until EOF(fileNo)
Line Input #fileNo, lineText
fields = Split(lineText, ",")
r = r + 1
For c = 0 To UBound(fields)
ws.Cells(r, c + 1).Value = fields(c)
Next c
Loop
この書き方では、ws.Cells(r, c + 1).Value = ... を実行するたびに、VBAからExcelへの書き込みが1回発生します。10万行×20列なら200万回です。そのうえ、書き込みのたびに画面の描画や再計算、イベント処理が動くことがあり、その負担が積み重なります。一般に、1行ずつ読む部分よりも、セルへ書き込む回数のほうが処理時間に大きく効きます。
速くするには、Excelとのやり取りをできるだけ減らします。
- ファイル全体を1回で読み込む
- 行と列への分割は、VBAのメモリ上（配列）で行う
- シートには、配列を範囲へ1回で書き込む
速く読み込むマクロ
標準モジュールに貼り付け、先頭の3つの定数を自分の環境に合わせて変えてから実行します。貼り付け先のシートは実行のたびに中身がすべて消えるので、取り込み専用のシートを用意してください。
Option Explicit
Sub ReadLargeCsvFast()
Const CSV_PATH As String = "C:\work\data\sales.csv" '読み込むCSV
Const SHEET_NAME As String = "取込" '貼り付け先（取り込み専用のシート）
Const TEXT_COLS As String = "1,3" '文字列のまま残す列番号（先頭0を保つ列）
Dim ws As Worksheet
Dim fileNo As Integer
Dim bytes() As Byte
Dim buf As String
Dim lines() As String
Dim fields() As String
Dim data() As Variant
Dim rowCount As Long, colCount As Long
Dim r As Long, c As Long, lastC As Long
Dim overCount As Long
Dim col As Variant
Dim calcMode As XlCalculation
Dim t0 As Double, t1 As Double, t2 As Double, t3 As Double
Dim errMsg As String
Set ws = ThisWorkbook.Worksheets(SHEET_NAME)
calcMode = Application.Calculation
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
On Error GoTo ErrHandler
t0 = Timer
'1. ファイル全体をバイト列で一度に読み、Shift_JISとして文字列にする
fileNo = FreeFile
Open CSV_PATH For Binary Access Read As #fileNo
If LOF(fileNo) = 0 Then Err.Raise vbObjectError + 1, , "ファイルが空です。"
ReDim bytes(0 To LOF(fileNo) - 1)
Get #fileNo, , bytes
Close #fileNo
fileNo = 0
buf = StrConv(bytes, vbUnicode)
Erase bytes
t1 = Timer
'2. 行に分け、1行ずつ列に分けて2次元配列へ入れる
buf = Replace(buf, vbCrLf, vbLf)
lines = Split(buf, vbLf)
buf = vbNullString
rowCount = UBound(lines) + 1
If lines(UBound(lines)) = "" Then rowCount = rowCount - 1 '末尾の改行でできた空の要素を除く
If rowCount < 1 Then Err.Raise vbObjectError + 2, , "読み込む行がありません。"
If rowCount > ws.Rows.Count Then Err.Raise vbObjectError + 3, , "行数がシートの上限を超えています: " & rowCount
colCount = UBound(Split(lines(0), ",")) + 1 '1行目（見出し）の列数を基準にする
If colCount < 1 Then Err.Raise vbObjectError + 4, , "1行目が空です。"
ReDim data(1 To rowCount, 1 To colCount)
For r = 1 To rowCount
fields = Split(lines(r - 1), ",")
lastC = UBound(fields) + 1
If lastC > colCount Then
overCount = overCount + 1
lastC = colCount
End If
For c = 1 To lastC
data(r, c) = StripQuotes(fields(c - 1))
Next c
Next r
Erase lines
t2 = Timer
'3. 貼り付け先を空にし、文字列で残す列を先に設定してから一括で書き込む
ws.Cells.Clear
For Each col In Split(TEXT_COLS, ",")
ws.Cells(1, CLng(col)).Resize(rowCount, 1).NumberFormat = "@"
Next col
ws.Range("A1").Resize(rowCount, colCount).Value = data
t3 = Timer
CleanUp:
On Error Resume Next
If fileNo > 0 Then Close #fileNo
Application.Calculation = calcMode
Application.EnableEvents = True
Application.ScreenUpdating = True
On Error GoTo 0
If errMsg <> "" Then
MsgBox "読み込みを中止しました: " & errMsg, vbExclamation
Else
MsgBox "行数: " & rowCount & " / 列数: " & colCount & vbLf & _
"列数が多すぎた行: " & overCount & vbLf & _
"読込: " & Format(t1 - t0, "0.00") & "秒 / 分割: " & Format(t2 - t1, "0.00") & _
"秒 / 書込: " & Format(t3 - t2, "0.00") & "秒", vbInformation
End If
Exit Sub
ErrHandler:
errMsg = Err.Description
Resume CleanUp
End Sub
'値の前後を囲むダブルクォーテーションだけを外す（値の中のカンマには対応しない）
Private Function StripQuotes(ByVal s As String) As String
If Len(s) >= 2 Then
If Left$(s, 1) = """" And Right$(s, 1) = """" Then
s = Replace(Mid$(s, 2, Len(s) - 2), """""", """")
End If
End If
StripQuotes = s
End Function
文字列として残す列がない場合は、For Each col In ... から Next col までの3行を削除します。
コードのポイント
ファイル全体をバイト列で一度に読む
Open ... For Binary でファイルを開き、ファイルサイズ（LOF）と同じ大きさのバイト配列へ Get で一度に読み込みます。そのあと StrConv(bytes, vbUnicode) で、Windowsの既定の文字コード（日本語版ではShift_JIS）の文字列に変換します。
Input$(LOF(fileNo), #fileNo) で読む書き方もあります。ただ、Input$ は文字数で読むのに対し、LOF はバイト数を返します。日本語を含むファイルでは、ファイルの終わりを超えて読もうとしてエラー（エラー番号62）になることがあります。バイト配列で読めば、このずれは起きません。
行に分けてから、1行ずつ列に分ける
改行コードをLFにそろえてから Split(buf, vbLf) で行の配列にします。各行は Split(..., ",") で列に分け、2次元配列 data(行, 列) に入れます。ファイルが改行で終わっていると最後に空の要素ができるので、その分を行数から引いています。
列数は1行目（見出し）を基準にしています。それより列の多い行は、はみ出した値を貼らずに overCount で数えます。この数が0でなければ、値の中にカンマを含む行がある可能性が高いので、元のCSVを確認してください。
StripQuotes は、値全体がダブルクォーテーションで囲まれているときに、その囲みだけを外す補助の関数です。値の中のカンマを正しく扱うものではありません。
先頭の0を残す列は、書き込む前に文字列に設定する
文字列の配列をセルへ書き込むと、Excelは手で入力したときと同じように値を解釈します。そのため 001234 は数値の 1234 に、2024/01/05 は日付に変わります。
コードでは、書き込む前に、TEXT_COLS で指定した列の表示形式を "@"（文字列）にしています。表示形式が文字列のセルに書き込んだ値は、文字列のまま入ります。商品コード、郵便番号、電話番号、社員番号など、先頭の0や桁数を保ちたい列の番号を指定してください。
金額や数量など、計算に使う列は指定しません。指定すると文字列として入るので、SUMなどの関数で合計できなくなります。
シートへの書き込みは1回だけ
ws.Range("A1").Resize(rowCount, colCount).Value = data で、配列と同じ大きさの範囲へ、配列全体を1回で書き込んでいます。配列の添字を 1 To 行数, 1 To 列数 にしておくと、範囲の行・列とそのまま対応します。
画面更新・イベント・再計算を止め、必ず元に戻す
実行中は、画面の描画（ScreenUpdating）、イベント（EnableEvents）、自動再計算（Calculation）を止めています。シートに数式やイベント処理があると、書き込みのたびに動いて遅くなるためです。
途中でエラーが起きても設定が止まったまま残らないように、エラーのときは ErrHandler から CleanUp へ移ります。そこで開いているファイルを閉じ、設定を元に戻してから終了します。計算方法は、実行前の設定を calcMode に保存しておき、その値に戻しています。
処理時間を測る
コードでは Timer 関数で、読み込み・分割・書き込みの3つの区間にかかった時間を測り、最後にメッセージで表示します。Timer は午前0時からの経過秒数を返す関数で、小数点以下まで取れます。
測るときは、次の点に気を付けます。
- 同じファイルで比べる：書き方を変える前と後で、同じCSVを使って測ります。
- 何回か測る：同じ処理でも、1回目と2回目以降で時間が変わることがあります。数回測って傾向を見ます。
- 区間ごとに見る：合計だけでなく、どの区間に時間がかかっているかを見ると、次に手を入れる場所が分かります。書き込みの区間が長い場合は、シート上の数式や条件付き書式、ほかに開いているブックの影響も確認します。
- 午前0時をまたがない：
Timerは0時に0へ戻るので、日付をまたぐと結果がマイナスになります。その場合は測り直します。
読み込んだ後に確認すること
速く読めても、中身がずれていては使えません。実行したら、次の点を確認します。
- 行数：メッセージの行数が、元のCSVの行数（見出しを含む）と同じか。A列に空白がなければ、シート上でも
=COUNTA(A:A)で数えられます。 - 列数が多すぎた行：0になっているか。0でなければ、値の中のカンマや、列数の違う行が混ざっていないか、元のファイルを確認します。
- 先頭の0：指定した列に、先頭の0や桁数が残っているか。
=ISTEXT(A2)がTRUEになるかでも確かめられます。 - 数値の合計：金額などの列の合計が、元のシステムの画面や帳票の合計と同じか。
- 文字化け：日本語の列が読める状態か。文字化けしている場合は、CSVの文字コードがShift_JISではない可能性があります。
もっと大きなファイルで止まるとき
この方法では、ファイルの中身を文字列、行の配列、2次元配列の形でメモリに持つため、ファイルサイズの数倍のメモリを使います。特に32ビット版のExcelでは、数百MBのCSVでメモリ不足のエラーになることがあります。
その場合は、次の対処を検討します。
- 使い終わった変数を早めに空にする（コードでは、
bufやlinesを使い終わった時点で解放しています） - 配列を数万行ずつに分けて作り、範囲を区切って複数回に分けて書き込む
- CSVを決まった行数ごとに分割してから読み込む
- 必要な列だけを配列に入れ、使わない列は貼らない
