概要
本記事では、Excelファイル上で、2つのCSVファイルの差分を比較する作業を効率化する方法について紹介します。
前提
以下を満たすxlsmファイルが用意できる。
- マクロが有効化されている
- 開発タブがある
完成イメージ
後の手順に従って、シートを準備してコードを書くと以下のような3シートのExcelファイルが完成します。
それぞれのボタンを押して実行します。
作成手順
シートの準備
完成イメージのように、「result」「csv_1」「csv_2」という名前のシートを作成します。それぞれ、
- result：マクロの操作と実行結果の表示
- csv_1：比較対象1のcsvファイルを取り込むシート
- csv_2：比較対象2のcsvファイルを取り込むシート
という役割です。
コーディング
ここでは手順のみを示して、コードの中身については解説で説明します。
まず、「開発」→「Visual Basic」を押して、エディタ画面を開きます。その画面で「挿入」→「標準モジュール」を押して、Moduleを追加します。
追加したModule1に以下のコードを記述します。
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には以下のコードを記述します。
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
コーディング作業は以上です。
エディタは以下のような画面になっているはずです。
画面作成
ボタン配置
コーディングが終わったので、シート「result」に戻って画面を作成します。
まず、「開発」→「挿入」→「ボタン」から4つの実行ボタンを配置します。
配置すると次のような画面が表示されるので、それぞれ4つのマクロを割り当てます。
| マクロ名 | ボタン名 |
|---|---|
| Button1 | ファイル選択1 |
| Button2 | ファイル選択2 |
| Main1 | CSV読み込み |
| Main2 | 比較開始 |
セル準備
ボタン配置後、G1:I3を以下のようにします。(色付けは分かりやすさのためにしているので必須ではありません)
I2:I3は、「UTF-8」または「shift_jis」のみ入力できるドロップダウンリストを使っています。
ここでは読み込むcsvファイルの文字コードを正しく指定する必要があるので、注意してください。
実行
実行手順
作成が完了したので実行します。
実行手順は以下の通りです。
- 「ファイル選択1」を押してcsvファイル1を選択し、文字コードを選択する
- 「ファイル選択2」を押してcsvファイル2を選択し、文字コードを選択する
- 「CSV読み込み」を押して2つのcsvファイルをそれぞれのシートに読み込む
- 一致比較のために、手を加える必要がある場合はここで行う(例えば並び替えや不要な列の削除など)
- 「比較開始」を押して2つのシートを比較する
このとき、不一致件数が1000,2000,...件毎にポップアップが表示され、「いいえ」を選ぶことで実行を中止できます。
不一致件数が多すぎる場合は途中で中止し、ファイルを修正した後に実行することを推奨します。 - 「result」のA列からC列に不一致セルの位置が出力され、「csv_1」「csv_2」の該当のセルが黄色になる
実行結果
- シート「csv_1」(適当な乱数をベースにしたcsv2に対して、いくつか不一致要素に変更)：不一致セルが黄色
- シート「csv_2」：不一致セルが黄色
- シート「result」：2つのシートで黄色い不一致セルの箇所が、一覧化されている
解説
ここではコーディングで作成したマクロの解説を行います。
ファイル選択
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
このコードでは、
filepath = Application.GetOpenFilename(file_type, 1, dialog)
で選択されたファイルパスを、シート「result」のセル(row, col)に代入します。
csv読み込み
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で指定されたシートに読み込みます。
With CreateObject("ADODB.Stream")
.Charset = code
.Open
.LoadFromFile filepath
buf = .ReadText
.Close
End With
まず、ここでbufにcsvファイルの中身を文字列として代入します。
'改行で分割して配列化
rows = Split(buf, vbCrLf)
'行数取得
num_rows = UBound(rows) + 1
'列数取得
num_cols = UBound(Split(rows(0), ",")) + 1
次に、2次元配列に代入するためにcsvの行数・列数を取得します。このとき、Split()で分割された文字列は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文字列を行ごとに分割した配列を、さらに「,」で分割して1つの2次元配列data_arrayに代入します。
csvの最後に改行コードがある場合は、最終行が空になってしまうので、2次元配列代入時にIf文で除外しています。
'csvの全要素をシートに出力
ws.Range("A1").Resize(num_rows, num_cols).Value = data_array
最後に、シートwsに、data_arrayの中身を全て書き込んで読み込み完了です。
2シート間の比較
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に一覧を出力します。
'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全体を配列に代入します。
それぞれの大きさ(行数・列数)が一致していない場合は、セル同士の比較を行う以前に一致しないので、メッセージを出して処理を終了します。
'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毎に確認メッセージを出しています。メッセージで「いいえ」を選ぶと処理を終了します。
If index = 1 Then
MsgBox "全要素が一致しました！"
End If
最後に、全てのセル確認後もindexが変わらない、つまり全セルが一致していた場合のみメッセージを出して完了です。