VBAで2つのCSVファイルを比較する

## 概要
本記事では、Excelファイル上で、2つのCSVファイルの差分を比較する作業を効率化する方法について紹介します。

## 前提
以下を満たすxlsmファイルが用意できる。
- マクロが有効化されている
- 開発タブがある

## 完成イメージ
後の手順に従って、シートを準備してコードを書くと以下のような3シートのExcelファイルが完成します。
それぞれのボタンを押して実行します。

![マクロ画面.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3771290/7b5093fd-1cf0-c361-225e-244c09486e28.png)

## 作成手順
### シートの準備
完成イメージのように、「result」「csv_1」「csv_2」という名前のシートを作成します。それぞれ、
- result：マクロの操作と実行結果の表示
- csv_1：比較対象1のcsvファイルを取り込むシート
- csv_2：比較対象2のcsvファイルを取り込むシート

という役割です。

### コーディング
ここでは手順のみを示して、コードの中身については[解説](#解説)で説明します。  
まず、「開発」→「Visual Basic」を押して、エディタ画面を開きます。その画面で「挿入」→「標準モジュール」を押して、Moduleを追加します。

![マクロ作成_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3771290/76b36831-cbeb-c87c-d170-e176c348e470.png)

追加したModule1に以下のコードを記述します。
```vb
Option Explicit

Public Sub Main1()
    '変数の宣言
    Dim filepath_1 As String, filepath_2 As String
    Dim wb As Workbook
    Dim ws_result As Worksheet, ws_csv_1 As Worksheet, ws_csv_2 As Worksheet
    Dim code_1 As String, code_2 As String

    'ワークブック、シートオブジェクトをセット
    Set wb = ThisWorkbook
    Set ws_result = wb.Worksheets("result")
    Set ws_csv_1 = wb.Worksheets("csv_1")
    Set ws_csv_2 = wb.Worksheets("csv_2")

    'csvファイルのパスと文字コードを指定
    filepath_1 = ws_result.Cells(2, 8)
    code_1 = ws_result.Cells(2, 9)
    filepath_2 = ws_result.Cells(3, 8)
    code_2 = ws_result.Cells(3, 9)

    'csvファイルの読み込み
    LoadCSV filepath_1, ws_csv_1, code_1
    LoadCSV filepath_2, ws_csv_2, code_2
End Sub

Public Sub Main2()
    '変数の宣言
    Dim wb As Workbook
    Dim ws_result As Worksheet, ws_csv_1 As Worksheet, ws_csv_2 As Worksheet

    'ワークブック、シートオブジェクトをセット
    Set wb = ThisWorkbook
    Set ws_result = wb.Worksheets("result")
    Set ws_csv_1 = wb.Worksheets("csv_1")
    Set ws_csv_2 = wb.Worksheets("csv_2")

    'シートを比較
    CompareSheets ws_result, ws_csv_1, ws_csv_2
End Sub

'filepathのcsvファイルをシートwsに出力する
Private Function LoadCSV(filepath As String, ws As Worksheet, code As String)
    '変数の宣言
    Dim buf As String
    Dim i As Long, j As Long
    Dim rows As Variant, row As Variant
    Dim num_rows As Long, num_cols As Long
    Dim data_array As Variant

    'シート初期化
    ws.UsedRange.Delete

    With CreateObject("ADODB.Stream")
        .Charset = code
        .Open
        .LoadFromFile filepath
        buf = .ReadText
        .Close
    End With
    'Windows環境とLinux環境の改行コードの違いを解消
    buf = Replace(buf, vbCrLf, vbLf)
    buf = Replace(buf, vbLf, vbCrLf)
    '改行で分割して配列化
    rows = Split(buf, vbCrLf)
    '行数取得
    num_rows = UBound(rows) + 1
    '列数取得
    num_cols = UBound(Split(rows(0), ",")) + 1
    
    'csvの行数x列数の2次元配列で再宣言
    ReDim data_array(1 To num_rows, 1 To num_cols)
    '全ての要素をdata_arrayに格納、要素数に注意
    For i = 1 To num_rows:
        row = Split(rows(i - 1), ",")
        If UBound(row) > 1 Then
            For j = 1 To num_cols
                data_array(i, j) = row(j - 1)
            Next j
        End If
    Next i

    'csvの全要素をシートに出力
    ws.Range("A1").Resize(num_rows, num_cols).Value = data_array

End Function

Private Function CompareSheets(ws_result As Worksheet, ws_csv_1 As Worksheet, ws_csv_2 As Worksheet)
    '変数の宣言
    Dim cells_1 As Variant, cells_2 As Variant
    Dim num_rows_1 As Long, num_cols_1 As Long
    Dim num_rows_2 As Long, num_cols_2 As Long
    Dim i As Long, j As Long
    Dim index As Long, res As Long

    '結果一覧をクリア
    ws_result.Range("A:C").ClearContents

    'csvデータを配列に格納
    cells_1 = ws_csv_1.UsedRange
    cells_2 = ws_csv_2.UsedRange

    '大きさを確認
    num_rows_1 = UBound(cells_1, 1)
    num_cols_1 = UBound(cells_1, 2)
    num_rows_2 = UBound(cells_2, 1)
    num_cols_2 = UBound(cells_2, 2)

    If num_rows_1 <> num_rows_2 Then
        MsgBox "行数が一致しません、終了します。"
        Exit Function
    End If
    If num_cols_1 <> num_cols_2 Then
        MsgBox "列数が一致しません、終了します。"
        Exit Function
    End If
    
    '結果一覧の準備
    ws_result.Cells(1, 1) = "番号"
    ws_result.Cells(1, 2) = "列名"
    ws_result.Cells(1, 3) = "行番号"

    '2つのシートを比較して、一致していないセルを黄色に塗り、対象セルの一覧を出力する
    index = 1
    For i = 1 To num_cols_1
        For j = 1 To num_rows_1
            If cells_1(j, i) <> cells_2(j, i) Then
                ws_csv_1.Cells(j, i).Interior.ColorIndex = 6
                ws_csv_2.Cells(j, i).Interior.ColorIndex = 6
                ws_result.Cells(index + 1, 1) = index
                ws_result.Cells(index + 1, 2) = ws_csv_1.Cells(1, i) '列名
                ws_result.Cells(index + 1, 3) = j '行番号
                index = index + 1
                If index Mod 1000 = 0 Then
                    res = MsgBox("不一致が" & index & "/" & i * j & "件見つかっています。処理を続けますか？", vbYesNo)
                    If res = vbNo Then
                        Exit Function
                    End If
                End If
            End If
        Next j
    Next i

    If index = 1 Then
        MsgBox "全要素が一致しました！"
    End If
End Function

```
もう一つModuleを追加して、Module2には以下のコードを記述します。
```vb
Option Explicit

Public Sub Button1()
    '変数の宣言
    Dim row As Long, col As Long

    row = 2
    col = 8
    GetFilePath row, col
End Sub

Public Sub Button2()
    '変数の宣言
    Dim row As Long, col As Long
    
    row = 3
    col = 8
    GetFilePath row, col
End Sub

Private Function GetFilePath(row As Long, col As Long)
    '変数の宣言
    Dim file_type As String
    Dim dialog As String
    Dim filepath As Variant
    Dim ws As Worksheet

    'ワークシートをセット
    Set ws = ThisWorkbook.Worksheets("result")

    '選択できるファイルの種類をcsvに限定
    file_type = "csvファイル,*.csv"
    'ダイアログのタイトルを指定
    dialog = "csvファイルを選択してください"
    'ファイル参照ダイアログの表示
    filepath = Application.GetOpenFilename(file_type, 1, dialog)

    'ダイアログでキャンセルが押された場合は終了
    If filepath = False Then
        Exit Function
    End If

    'G2セルにファイル名をセット
    ws.Cells(row, col).Value = filepath
End Function
```
コーディング作業は以上です。
エディタは以下のような画面になっているはずです。

![マクロ作成_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3771290/e92a1444-2707-7bd8-bd16-8380de13872f.png)

### 画面作成
#### ボタン配置
コーディングが終わったので、シート「result」に戻って画面を作成します。
まず、「開発」→「挿入」→「ボタン」から4つの実行ボタンを配置します。
配置すると次のような画面が表示されるので、それぞれ4つのマクロを割り当てます。

![マクロ作成_3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3771290/25e86b23-6820-28d8-58fa-71f01240faf9.png)

|マクロ名|ボタン名|
|:-|:-|
|Button1|ファイル選択1|
|Button2|ファイル選択2|
|Main1|CSV読み込み|
|Main2|比較開始|

#### セル準備
ボタン配置後、G1:I3を以下のようにします。(色付けは分かりやすさのためにしているので必須ではありません)

![マクロ作成_4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3771290/19fb6103-b6f8-d7d7-1c5f-db39e2d88073.png)

I2:I3は、「UTF-8」または「shift_jis」のみ入力できるドロップダウンリストを使っています。
ここでは読み込むcsvファイルの文字コードを正しく指定する必要があるので、注意してください。

## 実行
### 実行手順
作成が完了したので実行します。
実行手順は以下の通りです。
1. 「ファイル選択1」を押してcsvファイル1を選択し、文字コードを選択する
2. 「ファイル選択2」を押してcsvファイル2を選択し、文字コードを選択する
3. 「CSV読み込み」を押して2つのcsvファイルをそれぞれのシートに読み込む
4. 一致比較のために、手を加える必要がある場合はここで行う(例えば並び替えや不要な列の削除など)
5. 「比較開始」を押して2つのシートを比較する
このとき、不一致件数が1000,2000,...件毎にポップアップが表示され、「いいえ」を選ぶことで実行を中止できます。
不一致件数が多すぎる場合は途中で中止し、ファイルを修正した後に実行することを推奨します。
6. 「result」のA列からC列に不一致セルの位置が出力され、「csv_1」「csv_2」の該当のセルが黄色になる
### 実行結果
- シート「csv_1」(適当な乱数をベースにしたcsv2に対して、いくつか不一致要素に変更)：不一致セルが黄色

![実行結果_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3771290/dabce0c4-5807-0d9d-e75a-a4d1f4420a6f.png)

- シート「csv_2」：不一致セルが黄色

![実行結果_3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3771290/4a92a253-72eb-b200-cc13-cdb30359dc06.png)

- シート「result」：2つのシートで黄色い不一致セルの箇所が、一覧化されている

![実行結果_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3771290/e9fa2f61-ec8a-677a-45dc-4e42750ac2bc.png)

## 解説
ここでは[コーディング](#コーディング)で作成したマクロの解説を行います。

### ファイル選択
```vb
Private Function GetFilePath(row As Long, col As Long)
    '変数の宣言
    Dim file_type As String
    Dim dialog As String
    Dim filepath As Variant
    Dim ws As Worksheet

    'ワークシートをセット
    Set ws = ThisWorkbook.Worksheets("result")

    '選択できるファイルの種類をcsvに限定
    file_type = "csvファイル,*.csv"
    'ダイアログのタイトルを指定
    dialog = "csvファイルを選択してください"
    'ファイル参照ダイアログの表示
    filepath = Application.GetOpenFilename(file_type, 1, dialog)

    'ダイアログでキャンセルが押された場合は終了
    If filepath = False Then
        Exit Function
    End If

    'G2セルにファイル名をセット
    ws.Cells(row, col).Value = filepath
End Function
```
このコードでは、
```vb
filepath = Application.GetOpenFilename(file_type, 1, dialog)
```
で選択されたファイルパスを、シート「result」のセル(row, col)に代入します。

### csv読み込み
```vb
Private Function LoadCSV(filepath As String, ws As Worksheet, code As String)
    '変数の宣言
    Dim buf As String
    Dim i As Long, j As Long
    Dim rows As Variant, row As Variant
    Dim num_rows As Long, num_cols As Long
    Dim data_array As Variant

    'シート初期化
    ws.UsedRange.Delete

    With CreateObject("ADODB.Stream")
        .Charset = code
        .Open
        .LoadFromFile filepath
        buf = .ReadText
        .Close
    End With
    'Windows環境とLinux環境の改行コードの違いを解消
    buf = Replace(buf, vbCrLf, vbLf)
    buf = Replace(buf, vbLf, vbCrLf)
    '改行で分割して配列化
    rows = Split(buf, vbCrLf)
    '行数取得
    num_rows = UBound(rows) + 1
    '列数取得
    num_cols = UBound(Split(rows(0), ",")) + 1
    
    'csvの行数x列数の2次元配列で再宣言
    ReDim data_array(1 To num_rows, 1 To num_cols)
    '全ての要素をdata_arrayに格納、要素数に注意
    For i = 1 To num_rows:
        row = Split(rows(i - 1), ",")
        If UBound(row) > 1 Then
            For j = 1 To num_cols
                data_array(i, j) = row(j - 1)
            Next j
        End If
    Next i

    'csvの全要素をシートに出力
    ws.Range("A1").Resize(num_rows, num_cols).Value = data_array

End Function
```
このコードでは、`filepath`で指定された、文字コードが`code`のcsvファイルを、`ws`で指定されたシートに読み込みます。
```vb
    With CreateObject("ADODB.Stream")
        .Charset = code
        .Open
        .LoadFromFile filepath
        buf = .ReadText
        .Close
    End With
```
まず、ここで`buf`にcsvファイルの中身を文字列として代入します。
```vb
    '改行で分割して配列化
    rows = Split(buf, vbCrLf)
    '行数取得
    num_rows = UBound(rows) + 1
    '列数取得
    num_cols = UBound(Split(rows(0), ",")) + 1
```
次に、2次元配列に代入するためにcsvの行数・列数を取得します。このとき、`Split()`で分割された文字列は`0`始まりの配列になるので、行数・列数としては`+1`しています。

```vb
  'csvの行数x列数の2次元配列で再宣言
    ReDim data_array(1 To num_rows, 1 To num_cols)
    '全ての要素をdata_arrayに格納、要素数に注意
    For i = 1 To num_rows:
        row = Split(rows(i - 1), ",")
        If UBound(row) > 1 Then
            For j = 1 To num_cols
                data_array(i, j) = row(j - 1)
            Next j
        End If
    Next i
```
csv文字列を行ごとに分割した配列を、さらに「,」で分割して1つの2次元配列`data_array`に代入します。
csvの最後に改行コードがある場合は、最終行が空になってしまうので、2次元配列代入時に`If`文で除外しています。

```vb
    'csvの全要素をシートに出力
    ws.Range("A1").Resize(num_rows, num_cols).Value = data_array
```
最後に、シート`ws`に、`data_array`の中身を全て書き込んで読み込み完了です。

### 2シート間の比較
```vb
Private Function CompareSheets(ws_result As Worksheet, ws_csv_1 As Worksheet, ws_csv_2 As Worksheet)
    '変数の宣言
    Dim cells_1 As Variant, cells_2 As Variant
    Dim num_rows_1 As Long, num_cols_1 As Long
    Dim num_rows_2 As Long, num_cols_2 As Long
    Dim i As Long, j As Long
    Dim index As Long, res As Long

    '結果一覧をクリア
    ws_result.Range("A:C").ClearContents

    'csvデータを配列に格納
    cells_1 = ws_csv_1.UsedRange
    cells_2 = ws_csv_2.UsedRange

    '大きさを確認
    num_rows_1 = UBound(cells_1, 1)
    num_cols_1 = UBound(cells_1, 2)
    num_rows_2 = UBound(cells_2, 1)
    num_cols_2 = UBound(cells_2, 2)

    If num_rows_1 <> num_rows_2 Then
        MsgBox "行数が一致しません、終了します。"
        Exit Function
    End If
    If num_cols_1 <> num_cols_2 Then
        MsgBox "列数が一致しません、終了します。"
        Exit Function
    End If
    
    '結果一覧の準備
    ws_result.Cells(1, 1) = "番号"
    ws_result.Cells(1, 2) = "列名"
    ws_result.Cells(1, 3) = "行番号"

    '2つのシートを比較して、一致していないセルを黄色に塗り、対象セルの一覧を出力する
    index = 1
    For i = 1 To num_cols_1
        For j = 1 To num_rows_1
            If cells_1(j, i) <> cells_2(j, i) Then
                ws_csv_1.Cells(j, i).Interior.ColorIndex = 6
                ws_csv_2.Cells(j, i).Interior.ColorIndex = 6
                ws_result.Cells(index + 1, 1) = index
                ws_result.Cells(index + 1, 2) = ws_csv_1.Cells(1, i) '列名
                ws_result.Cells(index + 1, 3) = j '行番号
                index = index + 1
                If index Mod 1000 = 0 Then
                    res = MsgBox("不一致が" & index & "/" & i * j & "件見つかっています。処理を続けますか？", vbYesNo)
                    If res = vbNo Then
                        Exit Function
                    End If
                End If
            End If
        Next j
    Next i

    If index = 1 Then
        MsgBox "全要素が一致しました！"
    End If
End Function
```
このコードでは、シート`ws_csv_1`と`ws_csv_2`で同じ場所にあるセルを比較し、不一致セルには色を付け、シート`ws_result`に一覧を出力します。

```vb
    'csvデータを配列に格納
    cells_1 = ws_csv_1.UsedRange
    cells_2 = ws_csv_2.UsedRange

    '大きさを確認
    num_rows_1 = UBound(cells_1, 1)
    num_cols_1 = UBound(cells_1, 2)
    num_rows_2 = UBound(cells_2, 1)
    num_cols_2 = UBound(cells_2, 2)

    If num_rows_1 <> num_rows_2 Then
        MsgBox "行数が一致しません、終了します。"
        Exit Function
    End If
    If num_cols_1 <> num_cols_2 Then
        MsgBox "列数が一致しません、終了します。"
        Exit Function
    End If
```
まず、シート`ws_csv_1`と`ws_csv_2`全体を配列に代入します。
それぞれの大きさ(行数・列数)が一致していない場合は、セル同士の比較を行う以前に一致しないので、メッセージを出して処理を終了します。

```vb
    '2つのシートを比較して、一致していないセルを黄色に塗り、対象セルの一覧を出力する
    index = 1
    For i = 1 To num_cols_1
        For j = 1 To num_rows_1
            If cells_1(j, i) <> cells_2(j, i) Then
                ws_csv_1.Cells(j, i).Interior.ColorIndex = 6
                ws_csv_2.Cells(j, i).Interior.ColorIndex = 6
                ws_result.Cells(index + 1, 1) = index
                ws_result.Cells(index + 1, 2) = ws_csv_1.Cells(1, i) '列名
                ws_result.Cells(index + 1, 3) = j '行番号
                index = index + 1
                If index Mod 1000 = 0 Then
                    res = MsgBox("不一致が" & index & "/" & i * j & "件見つかっています。処理を続けますか？", vbYesNo)
                    If res = vbNo Then
                        Exit Function
                    End If
                End If
            End If
        Next j
    Next i
```
次に、それぞれのシートの1列目1行目→1列目N行目→2列目1行目→2列目N行目→...→M列目N行目の順番でセルの一致を確認していきます。
不一致セルの場合、それぞれのシートの該当セルを黄色(インデックス6)に塗り、シート「result」に記録します。

あまりに不一致セルが多い場合(例えば、数万行×数万行で全く違うファイル)、一気に出力しようとするとExcelが「応答なし」でフリーズする恐れがあるので、不一致件数が1000毎に確認メッセージを出しています。メッセージで「いいえ」を選ぶと処理を終了します。

```vb
    If index = 1 Then
        MsgBox "全要素が一致しました！"
    End If
```
最後に、全てのセル確認後も`index`が変わらない、つまり全セルが一致していた場合のみメッセージを出して完了です。

## 参考URL
- https://scodebank.com/?p=1357
- https://excel-ubara.com/excelvba5/EXCELVBA257.html