[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『CSVファイルのデータをEXCELへ書き込み時の処理時間について』（maco）
いつもお世話になっております。 前回、[20071210103832](2007/12/10投稿)では中途半端な状態で終えてしまい、 半平太さんとseiyaさんには大変失礼いたしました。 今回また[20071210103832]の続きで投稿させていただきました。
seiyaさんに作成していただいたコードを使用させていただき、意味を調べながら 取り組んでいたのですが、うまくいかず断念しかけていました。 自分自身きちんと理解していなかったことや、難しく考えていたことがあったので、 もう一度初めから何をどうしたいのか整理してみました。
【行いたいこと】 約800万行分の波形データが入ったCSVファイルから条件に当てはまるデータ行だけを 新規エクセルファイルに書き込みたい。
(CSVファイル)
A B C D E F G H I J
1 *** \\\ +++ !!! %%% &&& <<< >>> ###
2 -100 1 1 0 0 0 1 1 1 0
3 -99 1 1 0 0 0 1 1 1 0
4 -98 1 1 0 0 0 1 1 1 0
5 -97 1 1 0 0 1 1 1 1 0
6 -96 1 1 0 0 0 1 1 1 0
7 -95 1 1 0 0 0 1 1 1 0
8 -94 1 1 0 0 0 1 1 1 0
9 -93 1 1 1 0 0 1 1 1 0
10 -92 1 1 0 0 0 1 1 1 0
・
・
・
★1行目は見出し ★A列は何番目のデータかという番号 ★データは800万行を超える
【条件】
①1行目は無条件で書き込む。
②1つ前のデータと比較し、変化点のある行のみ書き込む。
（例えば、-98番目のデータのB列～J列と、-99番目のデータのB列～J列を
比較すると、データ値に変化はないので書き込み不要。
-97番目のデータのB列～J列と、-98番目のデータのB列～J列を
比較すると、F列に変化があるので（0と1）書き込む。）
③2行目のデータ（-100番目のデータ)は最初の波形データであり、
比較の必要がないので無条件で書き込む。
以上をふまえ、なんとか下記コードを作成することができました。 ---------------------------------------------------------------------------- Sub CSV取り込み()
Dim mystring As String Dim myvar As Variant Dim myvar_old As Variant Dim iRow As Integer Dim bOutputFlag As Boolean Const myfile As String = "C:\ファイル名.csv"
iRow = 1
Open myfile For Input Access Read As #1
For iRow = 1 To 2
Line Input #1, mystring myvar = Split(mystring, ",")
ActiveSheet.Cells(iRow, 1) = myvar(0)
ActiveSheet.Cells(iRow, 2) = myvar(1)
ActiveSheet.Cells(iRow, 3) = myvar(2)
ActiveSheet.Cells(iRow, 4) = myvar(3)
ActiveSheet.Cells(iRow, 5) = myvar(4)
ActiveSheet.Cells(iRow, 6) = myvar(5)
ActiveSheet.Cells(iRow, 7) = myvar(6)
ActiveSheet.Cells(iRow, 8) = myvar(7)
ActiveSheet.Cells(iRow, 9) = myvar(8)
ActiveSheet.Cells(iRow, 10) = myvar(9)
myvar_old = myvar
Next iRow
Do While Not EOF(1)
Line Input #1, mystring myvar = Split(mystring, ",")
If (myvar(1) = myvar_old(1)) And (myvar(2) = myvar_old(2)) And _
(myvar(3) = myvar_old(3)) And (myvar(4) = myvar_old(4)) And _
(myvar(5) = myvar_old(5)) And (myvar(6) = myvar_old(6)) And _
(myvar(7) = myvar_old(7)) And (myvar(8) = myvar_old(8)) And _
(myvar(9) = myvar_old(9)) Then
bOutputFlag = False
Else
bOutputFlag = True
End If
If bOutputFlag = True Then
ActiveSheet.Cells(iRow, 1) = myvar(0)
ActiveSheet.Cells(iRow, 2) = myvar(1)
ActiveSheet.Cells(iRow, 3) = myvar(2)
ActiveSheet.Cells(iRow, 4) = myvar(3)
ActiveSheet.Cells(iRow, 5) = myvar(4)
ActiveSheet.Cells(iRow, 6) = myvar(5)
ActiveSheet.Cells(iRow, 7) = myvar(6)
ActiveSheet.Cells(iRow, 8) = myvar(7)
ActiveSheet.Cells(iRow, 9) = myvar(8)
ActiveSheet.Cells(iRow, 10) = myvar(9)
iRow = iRow + 1
End If
Loop
Close #1
End Sub ---------------------------------------------------------------------------- 上記マクロを65,536行分データの入ったCSVファイルで試したところ、 希望通りの結果が得られました。 しかし、65,536行分で処理時間が約1分かかったため、約800万行のデータが 入ったファイルで実行すると、約2時間はかかるということになってしまうので しょうか？ 実際に約800万行分のデータが入ったCSVファイルで実行したところ、 20分程待っても終わらず、かたまってしまったような状態になったので 強制的に中断しました。
VBA初心者なので、作成したコードにも余計な部分や足りない部分があると思います。 知識も薄いので、何かアドバイスやヒントがいただきたいです。 よろしくお願いします。 [エクセルのバージョン]Excel2003 [OSのバージョン]WindowsXP
[[20071210103832]]『不要行を削除しながら新規エクセルファイルに取り込むマクロ』（maco）
> ②1つ前のデータと比較し、変化点のある行のみ書き込む。
（例えば、-98番目のデータのB列～J列と、-99番目のデータのB列～J列を
比較すると、データ値に変化はないので書き込み不要。
-97番目のデータのB列～J列と、-98番目のデータのB列～J列を
比較すると、F列に変化があるので（0と1）書き込む。）
1) 最終行から見ていくということ？
2) 変化があった場合、どの行を残す？
3) その結果（行）とその上の行を検査？(常に最終行と検査？）
よくわかりません。 (seiya)
説明が分かりにくくてすみません;
先頭行から順番に見ていきます。 流れを説明させていただくと、
１行目を読み込む→無条件で書き込む
↓
２行目を読み込む→無条件で書き込む
↓
３行目を読み込む→２行目と３行目のデータを比較する→変化点があったら３行目のデータを書き込む
変化点がなければ３行目のデータは書き込まない
↓
４行目を読み込む→３行目と４行目のデータを比較する→変化点があったら４行目のデータを書き込む
変化点がなければ４行目のデータは書き込まない
↓
５行目を読み込む→４行目と５行目のデータを比較する→変化点があったら５行目のデータを書き込む
変化点がなければ５行目のデータは書き込まない
↓
・
・
・
最終行に到達するまで同じように、１行読み込み→読み込んだ行の１つ前に読み込んだ行と比較→
変化点があれば読み込んだ行を書き込み、変化点がなければ書き込まないという処理を繰り返す。
このような流れです<m(__)m> （maco)
もしかすると、FileSystemObjectではそのような量のデータは読み込めないかもしれませんが...
Sub test()
Dim txt As String, x, i As Long, b(), n As Long
txt = CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\ファイル名.csv").ReadAll
x = Split(txt,vbCrLf)
ReDim b(UBound(x))
For i = 0 To 2
b(i) = x(i)
Next
n = 2
For i = 3 To UBound(x) - 1
If x(i) <> x(i + 1) Then
n = n + 1
b(n) = x(i + 1)
End If
Next
With ThisWorkbook.Sheets(1).Range("a1")
For i = 0 To n
y = Split(x(i), ",")
.Offset(i).Resize(,UBound(y) + 1).Value = y
Next
End With
End Sub
(seiya)
seiyaさん：
ありがとうございます。 seiyaさんに作成していただいたコードを使用させていただいて、 65,536行分データの入ったCSVファイルで処理を試したところ、 処理時間がとても早くてびっくりしたのですが、全データが書き込まれてしまいます。 どこを変えたらよいでしょうか？ 自分で解決できなくて・・すみません。 （maco）
全て書き込まれてしまいますか... あ 2列目からでしたね
Sub test()
Dim txt As String, x, i As Long, b(), n As Long
txt = CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\ファイル名.csv").ReadAll
x = Split(txt,vbCrLf)
ReDim b(UBound(x))
For i = 0 To 2
b(i) = x(i)
Next
n = 2
For i = 2 To UBound(x) - 1
If Mid$(x(i), InStr(x(i), ",")) <> Mid$(x(i+1),InStr(x(i + 1),",")) Then
n = n + 1
b(n) = x(i + 1)
End If
Next
With ThisWorkbook.Sheets(1).Range("a1")
For i = 0 To n
y = Split(x(i), ",")
.Offset(i).Resize(,UBound(y) + 1).Value = y
Next
End With
End Sub
(seiya)
seiyaさん：
ありがとうございます<m(__)m> 遅くなり、すみません。
上記コードを使用させていただき試したところ、今度は、実行時エラー５「プロシージャの呼び出し、 または引数が不正です。」というメッセージが出ました。 If Mid$(x(i), InStr(x(i), ",")) <> Mid$(x(i+1),InStr(x(i + 1),",")) Then の部分が黄色くなっています。 Mid関数とInStr関数の知識がなかったので、調べながら意味を理解しているところです。 すみません・・・。 (maco)
こんにちは
横から失礼します。FSOは詳しくは知りませんので昔の話として、お読みください。
800万行ということですから、ファイルサイズはどれくらいになるのでしょう？ それを明示されたほうが、回答者さんの方法論が変化してくると思います。
2～30MBを超えるようなら、分割読み込みをする方が処理は早くなります。 （今は知りませんが、以前はそうでした。）
0：Openステートメント、バイナリモードで開いて、ファイルサイズを何分割か
して読み込み
1：１行すべて読み込めてない行は捨てる。
2：比較処理
3：シートに代入
4：Seek関数で次の読み込み位置まで移動
5：読み込み。何分割かした次の部分を読み込む
1～5を終わるまで繰り返し。 このやり方が、3：のシートに代入以外の処理では速いはずです。
もし、サンプルを必要とするなら、0～5のキーワードでWeb検索するとあるんじゃないかなと思います。 (neptune)
neptuneさん：
ファイルサイズは220MBです。 分割読み込みで処理した方がよいのですね； そういうことの知識もまだ足りませんでした。 貴重な情報をありがとうございます<m(__)m>
seiyaさん：
何度もお付き合いいただき感謝します。 もう少し勉強をしてから出直してきます。 ありがとうございました<m(__)m>
（maco）
> 実行時エラー５「プロシージャの呼び出し、または引数が不正です。」 ?? If Mid$(x(i), InStr(1, x(i), ","), 255) <> Mid$(x(i + 1), InStr(1, x(i + 1), ","), 255) で試してください。
それと、全て読み込めているので分割読み込みは必要ないはずです。 (seiya)
まず、ＶＢＡのLine Inputって、テキストファイルの読み込みに関してはかなり速い命令ですよ！！
例えば、新規ブックの標準モジュールに
'===================================================
Sub mk_sample()
Const csv = "samp8000000.csv"
Dim flno As Long
Dim cnt As Double
Dim g0 As Long
Dim myarray(1 To 10) As String
'On Error Resume Next
flno = FreeFile()
Open ThisWorkbook.Path & "\" & csv For Output As #flno
Print #flno, " ,***,\\\,+++,!!!,%%%,&&&,<<<,>>>,###"
For cnt = 1 To 8000000
myarray(1) = -5000000 + cnt - 1
If cnt Mod 10000 = 1 Then
For g0 = LBound(myarray()) + 1 To UBound(myarray())
myarray(g0) = Int(Rnd() * 2)
Next
End If
Print #flno, Join(myarray(), ",")
Next
Close #flno
End Sub
これでサンプルデータを作成してください。私の環境で5～6分で作成してくれました。
そんなに速いＰＣではありませんよ！！
作成されたsamp8000000.csvは、プロパティ調べで 208MBでした。
これをFsoのReadAllでは、私の環境では、メモリエラーになりました。
で、普通にLine Inputで読み込んで比較という手順で
'====================================================================
Sub main()
Const csv = "samp8000000.csv"
Dim flno As Long
Dim cnt As Double
Dim ret As Long
Dim g0 As Long
Dim dat As String
Dim dd As Variant
Dim ff As Variant
Dim myarray(1 To 10) As String
'On Error Resume Next
Application.ScreenUpdating = False
flno = FreeFile()
Open ThisWorkbook.Path & "\" & csv For Input As #flno
For g0 = 1 To 2
Line Input #flno, dat
dd = Split(dat, ",")
Range(Cells(cnt + 1, 1), Cells(cnt + 1, UBound(dd) + 1)).Value = dd
cnt = cnt + 1
Next
ff = dd
Do Until EOF(flno)
Line Input #flno, dat
dd = Split(dat, ",")
ret = 1
For g0 = LBound(dd) + 1 To UBound(dd)
If dd(g0) <> ff(g0) Then
ret = 0
Exit For
End If
Next
If ret = 0 Then
Range(Cells(cnt + 1, 1), Cells(cnt + 1, UBound(dd) + 1)).Value = dd
cnt = cnt + 1
ff = dd
End If
Loop
Close #flno
Application.ScreenUpdating = True
End Sub
これで5程度でしたよ！！
要は、csvファイルから、データを読み込む時間など知れているということです。
後は、シートに書き込む手段を配列に貯めておいて最後に一気に書き込む手法を使うと
もっと速いかもしれません。
まっ、それでもＣＳＶの読み込みが遅いと言うなら、neptuneさん手法もよいと思います。
私も昔は、よく使いました（これはＯＳのファイル管理が使っている手法ですけどね）
でも、確かLine Inputは、速かったですよ！！
ichinose@ヴァレンタインデイ-子供は私にチョコくれるつもりがないらしい
seiyaさん： ありがとうございます。 If Mid$(x(i), InStr(1, x(i), ","), 255) <> Mid$(x(i + 1), InStr(1, x(i + 1), ","), 255)にして 試したのですが、やはり同じように「プロシージャの呼び出し、または引数が不正です。」という メッセージが出てしまいました。
ichinoseさん： ありがとうございます。 サンプルデータを作り読み込んで比較を実行したところ、どちらも5分以内に処理できました。 驚きです。 ですが、実際に使用するファイルで試したところ、フリーズ状態になってしまいました。 サンプルデータを作るVBAまで作成していただき、ありがとうございます。 勉強させていただきます。
seiyaさん neptuneさん ichinoseさん： 「出直します」の書き込みをした後、皆さまの教えてくださったことや本などを参考に 色々試したのですが、解決せず、会社の同僚の力を借りてFileSystemObjectを使い解決することが できました。 初めて知ったことがほとんどで、知識の薄い私にお付き合いしていただいた皆様に心から感謝します。 本当にありがとうございました。 (maco)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.