[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『2つのcsvファイルを比較し、不一致を抽出したい』(ピアノ)
二つのcsvファイルを比較し、不一致レコードを抽出したいです。
細かいコードは後々ゆっくり考えますが、大まかな仕様について、アドバイスを頂きたいです。
条件はこちらです。
・二つのcsvファイルは、同じ内容の情報ではあるが、書式が異なる。
例)ファイルA:車種,型番,氏名,住所,購入年月日,…
ファイルB:氏,名,都道府県,市町村名,番地,車種,年代,型番,…
・csvファイルのレコード数はそれぞれ20万前後。
・不一致レコードは、それぞれ3万ずつ程度の見込み。
これらの条件から、文字列操作もしながら、二つのファイルを比較する必要があり、またレコード数もそこそこあるため、ExcelVBAを使って処理しようと考えています。
現在、私が考えている仕様は次のとおりです。
<案1>
1、ファイルA.csvをExcelに落とす。(シートAとします。)
2、vbaでファイルB.csvを一行ずつ読み込み、文字列操作しつつ、シートAから該当データを探す。
3、該当があった場合、シートAの最終列に該当フラグをつける。
4、該当が無かった場合、ファイルBのレコードを別シートに不一致として記録する。
5、シートAの該当フラグが無いレコードと、別シートに記録されているレコードが不一致レコードとなる。
もう1つ考えている仕様は、次のとおりです。
<案2>
1、ファイルAとファイルBの全レコードを、二つのvba配列に落とす。
2、配列操作で不一致を抽出し、結果をExcelシートに落とす。
(比較方法は、<案1>の2〜4と同じような方法を考えています。)
案1では、Excelのファイルサイズが大きくなることが予想され、
案2では、vba実行時のメモリ使用量が大きくなることが予想されますが、
どちらがより良い方法でしょうか。
もしくは、もっと良い仕様がありますでしょうか。
パソコンのスペックがそれほど良くないので、出来るだけ軽い動作で処理できるような仕様にしたいです。
細かなコードは後々考えていきますが、とりあえず方向性を定めたく、アドバイスをお願い致します。
< 使用 Excel:Excel2013、使用 OS:Windows7 >
20万件 VS 20万件 ですから、どのような処理をしても、重い処理になるだろうなと思います。 βは横着ものですので、
・ファイルAの20万件のデータのマッチングキー部分のみを、適切な連結文字(タブ 等)で連結したものを Dictionaryに格納。 ・ファイルBについても同様に、「別の」Dictionaryに格納。
で、ファイルAのデータを格納したDictionary からデータを抽出しながら、ファイルBのデータを格納したDictionaryにあるかどうかを判定。 次に、ファイルBのデータを格納したDictionaryからデータを抽出しながら、ファイルAのデータを格納したDictionaryにあるかどうかを判定。
それぞれで【なかったもの】が アンマッチレコードだと、そんな処理にするでしょうね。
なかったものは、また別のDictionaryやArrayListに格納しておいて、最後に、それを、一括で、どこかのシートにどさっと書きこめばよろしいかと。
追記 もし、アンマッチ件数が 65536 超になることが予想されるなら、別のDictionaryないしはArrayListから 2次元配列に落とし込んで それをシートに書きこむ といったことも必要になると思いますが。
(β) 2016/10/22(土) 00:12
やはり、重たい処理にはなってしまいますかね…。
おっしゃる通り、全レコード全項目の読み込みは避けて、マッチングキーを工夫して比較したほうが良いでしょうね…。
まだまだvba勉強中で、知識も経験も不足の身です。
配列とDictionaryとArrayListについて、どんな違いがあるのか、まだ理解できていないので、そこら辺勉強してみます。
不一致件数は、やってみないと分からないのですが、6万5千越えはないと思います。
(ピアノ) 2016/10/22(土) 00:43
ちょっとサンプルを書いてみました。(あくまでサンプルです)
サンプルコードで相手にしているファイルは以下のレイアウトです。 これらのファイルはマクロブックと同じフォルダにしてあります。
ファイルA
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] [1] |項目1|項目2|品名|項目4|産地 |項目6|LOTNO|項目8 [2] |a1 |b1 |com1|d1 |area1|f1 |X0001|h1 [3] |a2 |b2 |com2|d2 |area2|f2 |X0002|h2 [4] |a3 |b3 |com3|d3 |area3|f3 |X0003|h3 [5] |a4 |b4 |com4|d4 |area1|f4 |X0004|h4 [6] |a5 |b5 |com1|d5 |area2|f5 |X0005|h5 [7] |a6 |b6 |com2|d6 |area3|f6 |X0006|h6 [8] |a7 |b7 |com3|d7 |area1|f7 |X0007|h7 [9] |a8 |b8 |com4|d8 |area2|f8 |X0008|h8 [10]|a9 |b9 |com1|d9 |area3|f9 |X0009|h9 [11]|a10 |b10 |com2|d10 |area1|f10 |X0010|h10 [12]|a11 |b11 |com3|d11 |area2|f11 |X0011|h11 [13]|a12 |b12 |com4|d12 |area3|f12 |X0012|h12 [14]|a13 |b13 |com1|d13 |area1|f13 |X0013|h13 [15]|a14 |b14 |com2|d14 |area2|f14 |X0014|h14
ファイルB
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] [1] |品名|項目2|LOTNO|産地 |項目5|項目6|項目7|項目8 [2] |com1|b1 |X0001|area1|e1 |f1 |g1 |h1 [3] |comx|b2 |Z1234|areaZ|e2 |f2 |g2 |h2 [4] |comx|b3 |Z1235|areaZ|e3 |f3 |g3 |h3 [5] |comx|b4 |Z1236|areaZ|e4 |f4 |g4 |h4 [6] |com4|b5 |X0004|area1|e5 |f5 |g5 |h5 [7] |comx|b6 |X0007|areaZ|e6 |f6 |g6 |h6 [8] |com4|b8 |X0006|area3|e8 |f8 |g7 |h8 [9] |comx|b9 |X0010|areaZ|e9 |f9 |g8 |h9 [10]|comx|b10 |Z9999|areaZ|e10 |f10 |g9 |h10 [11]|com3|b11 |X0012|area3|e11 |f11 |g10 |h11 [12]|comx|b12 |X0006|areaZ|e12 |f12 |g11 |h12 [13]|com2|b13 |X0002|areaZ|e13 |f13 |g12 |h13
とりあえず、csvファイルをブックとして開いています。 もっと効率のいい開き方はありますが、2つのファイルのみですので、全体として見れば、差異はわずかです。 それよりも、やはり重いですね。少ないデータ件数のテストでも、どっこらしょ という感じですから これが20万件になると使い物にならないかもしれません。
別案として、基本は同じなんですが、判定列を、それぞれ固定で与えて、キーセット時間を少し減らし アンマッチ情報も最初から2次元配列に入れて、Transposeの手間を省いたSample2 も。
こちらのほうは、アンマッチのデータの行番号も表示しています。SampleのほうはファイルAとファイルBの処理を 共通プロシジャで行っていますが、Sample2 は、とりあえず、ベタベタと両方のコードを書いています。 でも、Sample2 も遅いです。
抜本的には、ファイルA、ファイルBをそれぞれ並び替えをして、データ順をあわせ 突合せ処理を行ったほうがいいかもしれません。
Sub Sample() Dim shT As Worksheet Dim dicA As Object Dim dicB As Object Dim arl As Object Dim c As Range
Application.ScreenUpdating = False
Set shT = ThisWorkbook.Sheets("Sheet1") Set dicA = CreateObject("Scripting.Dictionary") Set dicB = CreateObject("Scripting.Dictionary") Set arl = CreateObject("System.Collections.ArrayList")
GetData ThisWorkbook.Path & "\ファイルA.csv", dicA, "C", "E", "G" GetData ThisWorkbook.Path & "\ファイルB.csv", dicB, "A", "D", "C"
shT.Cells.ClearContents shT.Range("A1").Value = "Aのみ" shT.Range("C1").Value = "Bのみ"
SetData dicA, dicB, arl If arl.Count > 0 Then shT.Range("A2").Resize(arl.Count).Value = WorksheetFunction.Transpose(arl.ToArray)
SetData dicB, dicA, arl If arl.Count > 0 Then shT.Range("C2").Resize(arl.Count).Value = WorksheetFunction.Transpose(arl.ToArray)
shT.Columns("A").AutoFit shT.Columns("C").AutoFit
End Sub
Private Sub GetData(f As String, dic As Object, ParamArray col()) Dim sh As Worksheet Dim c As Range Dim d As String Dim k As Variant Dim w As Variant Dim x As Long
Set sh = Workbooks.Open(f).Sheets(1) ReDim w(LBound(col) To UBound(col)) For Each c In sh.Range("A2", sh.Range("A" & sh.Rows.Count).End(xlUp)) x = LBound(col) For Each k In col w(x) = c.Columns(k).Value x = x + 1 Next dic(Join(w, "-")) = True Next sh.Parent.Close False
End Sub
Private Sub SetData(dic1 As Object, dic2 As Object, arl As Object) Dim k As Variant
arl.Clear
For Each k In dic1 If Not dic2.exists(k) Then arl.Add k End If Next
End Sub
Sub Sample2() Dim shT As Worksheet Dim shF As Worksheet Dim dicA As Object Dim dicB As Object Dim arl As Object Dim c As Range Dim k As Variant Dim x As Long Dim w As Variant
Application.ScreenUpdating = False
Set shT = ThisWorkbook.Sheets("Sheet1") Set dicA = CreateObject("Scripting.Dictionary") Set dicB = CreateObject("Scripting.Dictionary") Set arl = CreateObject("System.Collections.ArrayList")
'ファイルA Set shF = Workbooks.Open(ThisWorkbook.Path & "\ファイルA.csv").Sheets(1) For Each c In shF.Range("A2", shF.Range("A" & shF.Rows.Count).End(xlUp)) dicA(Join(Array(c.Columns("C").Value, c.Columns("E").Value, c.Columns("G").Value), "-")) = c.Row Next shF.Parent.Close False
'ファイルB Set shF = Workbooks.Open(ThisWorkbook.Path & "\ファイルB.csv").Sheets(1) For Each c In shF.Range("A2", shF.Range("A" & shF.Rows.Count).End(xlUp)) dicB(Join(Array(c.Columns("A").Value, c.Columns("D").Value, c.Columns("C").Value), "-")) = c.Row Next shF.Parent.Close False
shT.Cells.ClearContents shT.Range("A1").Value = "Aのみ" shT.Range("C1").Value = "Bのみ" shT.Range("A2:B2").Value = Array("項目", "行番号") shT.Range("C2:D2").Value = Array("項目", "行番号")
'Aファイルから見た差異 x = 0 ReDim w(1 To dicA.Count, 1 To 2)
For Each k In dicA If Not dicB.exists(k) Then x = x + 1 w(x, 1) = k w(x, 2) = dicA(k) End If Next
Range("A3").Resize(x, 2).Value = w
'Bファイルから見た差異 x = 0 ReDim w(1 To dicB.Count, 1 To 2)
For Each k In dicB If Not dicA.exists(k) Then x = x + 1 w(x, 1) = k w(x, 2) = dicB(k) End If Next
Range("C3").Resize(x, 2).Value = w
shT.Columns("A:D").AutoFit
End Sub
(β) 2016/10/22(土) 12:21
なるほど、DictionaryオブジェクトとExistsメソッドというのが、今回は使いやすそうですね!
とても勉強になります。
なお、ArrayListはVBAで使うためには.NETが必要とのこと。
使う端末での.NETインストール状況未確認ですが、ちょっと避けたいです・・・。
抜本的には、ファイルA、ファイルBをそれぞれ並び替えをして、データ順をあわせ 突合せ処理を行ったほうがいいかもしれません。
こちらも、なるほど!と思いました。
この場合は、Dictionaryではなく・・・?・・・
・・・えっと、ファイルオープンで一行ずつ読み込んで・・・?
マッチしたときは、それぞれ次行を読み込んで・・・
アンマッチのときは・・・?・・・?
アンマッチのときは、最終行まで比較して・・・?という流れになるのでしょうか。
(ピアノ) 2016/10/22(土) 22:38
まず、アップしたコードでは。結果を格納するのに ArrayList を使いましたが Dictionary でもいいんです。 ただ、Dictionary は キー/データ という構造で、今回は、結果のイメージだけが必要だったので、データだけを持つ ArrayList にしただけです。
Sample も Sample2 も、ほんのちょっとの変更で Dictionary化ができますので、明日になりますけど その部分をアップします。
しかし、いかんせん Sample も Sample2 も遅いです。
本格的な突合せ処理は、物臭なβには、ちょっと面倒ですので、簡易版を書いてみようかなと思っています。 これも、書き上げればアップしますが、さて、早くなるかどうか。 簡易版ですので書いて実行してみなければわからないといったシロモノかも・・・です。
(β) 2016/10/22(土) 23:46
とりあえず、SampleのArrayListをDictionaryに変更する部分。(Sample2では、そもそもArrayListを使っていませんでしたね)
Set arl = CreateObject("System.Collections.ArrayList") これを Set arl = CreateObject("Scripting.Dictionary")
If arl.Count > 0 Then shT.Range("A2").Resize(arl.Count).Value = WorksheetFunction.Transpose(arl.ToArray) これを If arl.Count > 0 Then shT.Range("A2").Resize(arl.Count).Value = WorksheetFunction.Transpose(arl.Items)
If arl.Count > 0 Then shT.Range("C2").Resize(arl.Count).Value = WorksheetFunction.Transpose(arl.ToArray) これを If arl.Count > 0 Then shT.Range("C2").Resize(arl.Count).Value = WorksheetFunction.Transpose(arl.Items)
サブプロシジャ SetData の
arl.Add k これを arl(arl.Count) = k
以上、修正箇所は4箇所のみです。
なお、arl(arl.Count) = k 、キー部分を arl.Count にしていますが、ここは、書き込みごとにユニークな値であれば なんでもいいので、書き込み前の arl内件数 を使っています。(結果として 0〜 の連番になっています。意味はありません)
(β) 2016/10/23(日) 09:09
並び替え版です。
データ件数が少なければ、ほとんど、アップ済みのものと処理時間は変わりません。 誤差の範囲のような縮小幅です。 かつ、ちょっとインチキというか、今回アップするものは、シート上の比較元を、いったん配列に格納して 相手ファイルと比較しています。もし、アップ済みのコードにもその手当てをいれると、処理時間の差異は ほとんどないかも。
ただ、データ件数が 20万件同士 ということになると、もしかしたら、アップ済みのものは 使い物にならない遅さ、 今回のものは、遅いけど、耐えられる といった程度にはなるのかなと期待はしているのですが・・・・
Sub Sample3() Dim shT As Worksheet Dim shA As Worksheet Dim shB As Worksheet Dim v As Variant
Dim colA As Long Dim colB As Long Dim mxa As Long Dim mxb As Long Dim rA As Range Dim rB As Range Dim edA As Range Dim edB As Range Dim LvalA As String Dim HvalA As String Dim LvalB As String Dim HvalB As String Dim vA As Variant Dim vB As Variant
Application.ScreenUpdating = False
Set shT = ThisWorkbook.Sheets("Sheet1")
setAB ThisWorkbook.Path & "\ファイルA.csv", shA, colA, mxa, rA, edA, LvalA, HvalA, Array("C", "E", "G") setAB ThisWorkbook.Path & "\ファイルB.csv", shB, colB, mxb, rB, edB, LvalB, HvalB, Array("A", "D", "C")
ReDim v(1 To WorksheetFunction.Max(mxa, mxb), 1 To 4)
vA = CheckAB(rA, rB, edB, HvalB, LvalB, mxa) vB = CheckAB(rB, rA, edA, HvalA, LvalA, mxb)
shA.Parent.Close False shB.Parent.Close False
shT.Cells.ClearContents shT.Range("A1").Value = "Aのみ" shT.Range("C1").Value = "Bのみ" shT.Range("A2").Resize(UBound(vA)).Value = vA shT.Range("C2").Resize(UBound(vB)).Value = vB shT.Columns("A").AutoFit shT.Columns("B").AutoFit
End Sub
Private Sub setAB(fPath As String, sh As Worksheet, col As Long, mx As Long, r As Range, ed As Range, Lval As String, Hval As String, keys As Variant)
Set sh = Workbooks.Open(fPath).Sheets(1) col = sh.UsedRange.Columns.Count + 1 mx = sh.UsedRange.Rows.Count Set r = sh.Cells(2, col).Resize(mx - 1) r.Formula = "=" & Join(keys, "2&""-""&") & "2" r.Value = r.Value sh.UsedRange.Sort Order1:=xlAscending, Key1:=sh.Columns(col), Header:=xlYes Set ed = sh.Cells(mx, col) Lval = r.Cells(1).Value Hval = ed.Value
End Sub
Private Function CheckAB(r1 As Range, r2 As Range, ed As Range, Hval As String, Lval As String, x) As Variant Dim yrR As Range Dim y As Long Dim z As Variant Dim ok As Boolean Dim v As Variant Dim w As Variant Dim d As Variant
Set yrR = r2 ReDim v(1 To x, 1 To 1) w = r1.Value y = 0 For Each d In w ok = False If d >= Lval Then If d <= Hval Then z = Application.Match(d, yrR, 1) If IsNumeric(z) Then If d = yrR.Cells(z).Value Then ok = True Set yrR = Range(yrR.Cells(z), ed) End If End If End If End If
If Not ok Then y = y + 1 v(y, 1) = d End If Next
CheckAB = v
End Function
(β) 2016/10/23(日) 17:50
よく考えると、逆に 並び替え版は使い物にならないかもしれません・・・・
20万件同士で、不一致が多ければ、それなりに効果はあると思いますが、ほとんど同じで、ごく一部に 不一致があるという場合は、逆に、足を引っ張るような気がしてきました。 (簡易版ですので)
実際のデータで、Sample,Sample2,Sample3 の形で、実装して、動かしてみて、その結果がどうなるか? 教えていただきたいですね。
(β) 2016/10/23(日) 18:17
もう1つ、マッチング手法ではないのですが、最後に、シートに結果を落とし込む際に
Sample,Sample2 では、不一致の件数だけの行数の一括転記 にしていたのに Sample3 では手を抜いて ファイルA、ファイルB の行数分(それぞれ20万件)の配列に納めたものを一括転記していました。
データ件数が少なければどうってことはないのですが 20万件となれば、それなりに書き込み時間がかかりますので Sample3 にたいしても、不一致件数に絞った行数分の書き込みにしたものを、手元で Sample4 として準備しています。 (ファイルA,ファイルB の制御用の変数も、ペアで、ぞろぞろあって、見にくいので、ユーザ定義型にしたもので少しすっきりさせています)
必要があれば、アップしますが、あわせて、開く時間は、そんなにかわらないと申し上げたものの やはり、20万件のファイルを2つ開くわけで、このあたりにも手を入れたほうがいいのかなと思ったりしています。
(β) 2016/10/24(月) 07:49
車体番号で照合できませんか?
そしたら複雑な文字列操作はとりあえず要らないかと。。。
書いてないからAの方には無さそうかなぁ。。。
(まっつわん) 2016/10/24(月) 08:41
やはり、20万件というのはvbaをもってしても「多い」という事なんですね。
いろいろ試してみる必要がありそうですね。
ちょっと時間に制限があるため、どこまでできるか自信がないところもありますが、頑張ってみます。
また、ご相談させてください。
(ピアノ) 2016/10/24(月) 08:43
車体番号のようなキーとなるべき情報があるにはあるのですが、
複数項目に分かれているうえ、文字列の桁数が一致しないんです。
また、それが数字のみではなく、アルファベット等を含む文字列なんです。
例)
ファイルA: 車体番号1,車体番号2,車体番号3,氏名,住所,…
※車体番号はすべて頭0なしのため、桁数が異なる。
ファイルB: 車番1-車番2,車番3,氏,名,都道府県,市町村名,…
※車番は頭0あり。全て同じ桁数。
車体番号1〜3全て組み合わせることで、ユニークとなる。
と、こんな感じです。
文字列操作は、回避困難なんです…(T_T)
(ピアノ) 2016/10/24(月) 08:53
または、ExcelはADOを使ってシートをDB扱いすると、勝手に数字だけのデータは前ゼロを無視してしまうので、ADOを利用してデータを比較するコーディングにするという手もあります。
ところで、そこまでいろいろ頑張ってExcelマクロで比較するより、ちゃんとビルドされた有名処のファイル比較アプリを使うだけで一発ではないかと思うのですが、いかがでしょうか? どんなに頑張っても、インタプリンタ言語でコンパイル言語に太刀打ちできないので、マクロを書く気にならないのです。
http://www.geocities.co.jp/SiliconValley-SanJose/8165/winmerge.html
(???) 2016/10/24(月) 11:01
「ファイル比較アプリ」というものの存在を初めて知りました!
こんなアプリがあるのですね!
ご紹介いただいたWinMergeでは、テキストの比較が可能なようですが、
これは、事前にcsvファイルのレイアウトや桁数をそろえてから使用するものでしょうか。
もし、csvファイルのレイアウトをそろえなくても二つを比較できるようなアプリがあるならば、
是非それを使ってみたいとは思いますが、もしレイアウトをそろえてから使用する必要があるならば、
やはりVBA等を使った方法で検討したいです。
なぜならば、この20万件の比較という作業を、ファイルを変えて複数回行う必要があるためです。
毎回レイアウトを手作業で直してからアプリで比較…という作業では、他の人間に作業をお願いし辛いので…。
csvファイルを直接編集するという作業は極力少なくしたいのです。
…どうやら、自分が思っていた以上に難しい作業に思えてきました(T_T)
(ピアノ) 2016/10/24(月) 13:30
>>そこまでいろいろ頑張ってExcelマクロで比較するより、ちゃんとビルドされた有名処のファイル比較アプリを使うだけで一発ではないかと思うのですが
賛成です。たとえば、20万行10列程度のCSVファイルをつくってブックとして開くだけで、7〜8秒かかります。 それが2つ。どんなに、我々が手を出せるVBAの表技を使っても、その比較等々、膨大な時間がかかると思います。
餅は餅屋ではないでしょうか。
(β) 2016/10/24(月) 13:35
とりあえず、前ゼロは先に書いたように、一旦Excelで開いてから別名保存でもすれば、数字だけのデータは前ゼロ無しになります。
これを利用して、比較する2つを揃えてください。後は、どれだけ違いがでるか? もう全然違っていると判定されてしまうようならば、合っている列だけ比較するようなマクロ案にするか、比較する列だけ抜き出して別名保存してから比較するか、でしょうか。
(???) 2016/10/24(月) 14:17
たくさんのご意見、本当にありがとうございます。
教えていただいたことをもとに、今回の作業の方針を、再検討してみました。
1)CSVファイルのレイアウトをVBAを使ってそろえ、比較用の新しいファイルをそれぞれ作成する。
(手作業によるファイル更新を避ける為。)
2)既存のファイル比較アプリを活用し、不一致を抽出する。
こんな流れでやってみようと思います。
この掲示板のおかげで、
20万件というデータ数が、私が思っていたより、「多い」ということが認識できました。
また、ファイル比較アプリというものの存在を知ることができました。
サンプルコードも、とても勉強になりました。
今後、別の機会に、活かしていきたいです!
無闇に挑戦する前にご相談して本当に良かったです。
心より、ありがとうございました!
(ピアノ) 2016/10/24(月) 20:58
おはようございます。
久しぶりに覗いて、ちょっと今更ですけど、
csvファイルを2つのシートに転記した後の処理ですけど、 読込はADOが一番早いし、FSOでも十分早いですし簡単です。
比較したいキー列を文字結合する作業列をつくって、(仮にA列に作ったとして) 下のような式を挿入すれば20万件程度なら一瞬で計算してくれますよ。
=IFERROR(IF(INDEX(Sheet1!A:A,MATCH(A2,Sheet1!A:A))=A2,"一致","不一致"),"不一致")
この手順をマクロ化すれば数秒くらいで出来ると思います。
追記、作業列での並べ替えは必須です(7:19)
(sy) 2016/10/26(水) 07:16
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.