VBA：CSVファイルの読出し手法別処理速度に関して

Question

●背景
CSVファイル（9 MB/184列）程度の読出処理を行う「手法」を実行速度基準に選択したく、ダミーデータを読出/消去/読出・・・（Ｎ10）繰返すプログラムをネットで調べた物をベースに作成し、各「手法」別に処理時間を確認した結果、結局はA）エクセルが最短となりました。
勉強にはなりましたが、拍子抜けで・・・・

●質問
1)CSVファイル（9 MB/184列）程度の読出であれば「エクセルで素直に」が一番速いとの認識は当たっていますか？

２）下記Ｂ）Ｃ）でまだ、速度向上の余地はあるのでしょうか？

３）ADO/DAO選択に関して後発ADOを選択しましたが、DAOを使用した場合、速度向上の余地はあるのでしょうか？

４）ADO＋SQLに関しては勉強が追いつかず確認できていませんが、

５）FSOも結局は１行処理になるため試していませんが、速度向上の余地はあるのでしょうか？　


A）エクセル呼出
　　Dim writeSheet As Worksheet
　　Set writeSheet = Worksheets("●呼出Data")
　　Dim readBook As Workbook  
　　Set readBook = Workbooks.Open(対象保存先フォルダ & 呼出Sf内処理ログファイル名)
　　Dim readSheet As Worksheet
　　Set readSheet = readBook.Worksheets("Sheet1")
　　readSheet.UsedRange.Copy
　　writeSheet.Cells(1, 1). _
    　　　PasteSpecial Paste:=xlPasteValues, Ope・・・・・・・・・・・
　　Application.CutCopyMode = False
　　readBook.Close False
　　
B）１行処理
　　intFileNo = FreeFile
　　Open 対象ファイル For Input As intFileNo
    　　Do Until EOF(intFileNo)
        　　Line Input #intFileNo, strLine
        　　arrLine = Split(strLine, ",")
        　　　For cnt列 = 0 To UBound(arrLine)
            　　　Sheets("●呼出Data").Cells(cnt行, cnt列 + 1).Value = arrLine(cnt列)
            　　　DoEvents
        　　　Next cnt列
        　　cnt行 = cnt行 + 1
    　　Loop

C）ＡＤＯ
　　Dim CN As ADODB.Connection
　　Dim RS As ADODB.Recordset
　　Set CN = New ADODB.Connection
　　CN.Provider = "Microsoft.Jet.OLEDB.4.0"
　　CN.Properties("Extended Properties") = "Text;HDR=NO"
　　CN.ConnectionString = "C:\"
　　CN.Open
　　Set RS = CN.Execute("SELECT * FROM ftr4q_try_b2SHORT_csv.CSV")
    　　rowCnt = 0
    　　colCnt = 0
    　　Do Until RS.EOF
        　　rowCnt = rowCnt + 1
        　　colCnt = 0
            　　For Each dbCol In RS.Fields
                　　colCnt = colCnt + 1
                　　Cells(rowCnt, colCnt).Value = dbCol.Value
            　　Next dbCol
        　　DoEvents
        　　RS.MoveNext
    　　Loop

end-u · Accepted Answer

re:補足コメント
元々の(C)のコードでADOで取れてるわけですからAccess購入するまでもないとは思います
>１行目の不可解で迷走中
これはヘッダーがおかしくなるという意味でしょうか
>CN.Properties("Extended Properties") = "Text;HDR=NO"
HDR=NO　にしてデータ型の問題で読み込みがおかしくなるようでしたら
ヘッダーのみLoopして別途読み込むようにしても良いかもしれませんね

Sub test_ADO()
　　Const wkPath As String = "C:\TEST"
　　Const wkCSV　As String = "TEST.csv"
　　Dim wkCon　　As ADODB.Connection
　　Dim wkRst　　As ADODB.Recordset
　　Dim wkSQL　　As String
　　Dim ws　　　 As Worksheet
　　Dim x()　　　As String
　　Dim fc　　　 As Long
　　Dim i　　　　As Long

Set ws = Sheets.Add

On Error GoTo errHandr1
　　Set wkCon = New ADODB.Connection
　　With wkCon
　　　　.Provider = "Microsoft.Jet.OLEDB.4.0"
　　　　.Properties("Extended Properties") = "TEXT"
　　　　.Open wkPath
　　End With

On Error GoTo errHandr2
　　wkSQL = "SELECT * FROM [" & wkCSV & "]"

Set wkRst = New ADODB.Recordset
　　With wkRst
　　　　.Open wkSQL, wkCon
　　　　On Error GoTo errHandr3
　　　　fc = .Fields.Count - 1
　　　　'フィールド名取得
　　　　ReDim x(0 To fc)
　　　　For i = 0 To fc
　　　　　　x(i) = .Fields(i).Name
　　　　Next
　　End With
　　ws.Range("A1").Resize(, fc + 1).Value = x
　　ws.Range("A2").CopyFromRecordset wkRst

errHandr3:
　　wkRst.Close
errHandr2:
　　wkCon.Close
errHandr1:
　　Set wkRst = Nothing
　　Set wkCon = Nothing
　　Set ws = Nothing
　　If Err.Number <> 0 Then Debug.Print Err.Number, Err.Description
End Sub

>現状ではやはり、エクセル直呼が一番評価です。
との事ですのであまりムリされなくても良いかと思いますが、一応サンプル２件

Sub sampleQT()
　　Const wkCSV　As String = "C:\TEST\TEST.csv"
　　Dim ws As Worksheet

Set ws = Sheets.Add
　　With ws.QueryTables.Add(Connection:="TEXT;" & wkCSV, Destination:=ws.Range("A1"))
　　　　.AdjustColumnWidth = False
　　　　.TextFilePlatform = xlWindows
　　　　.TextFileCommaDelimiter = True
　　　　.Refresh False
　　　　.Parent.Names(.Name).Delete
　　　　.Delete
　　End With
　　Set ws = Nothing
End Sub

Sub sampleCB()
　　Const wkCSV　As String = "C:\TEST\TEST.csv"
　　Const DLM　　As String = ","
　　Const REP　　As String = vbTab
　　Dim buf　　　As String
　　Dim reg　　　As Object
　　Dim n　　　　As Long

n = FreeFile
　　Open wkCSV For Input As #n
　　buf = StrConv(InputB(LOF(n), #n), vbUnicode)
　　Close #n

Set reg = CreateObject("VBScript.RegExp")
　　reg.Global = True
　　reg.Pattern = DLM

With GetObject("new:1C3B4210-F441-11CE-B9EA-00AA006B1A69")
　　　　.SetText reg.Replace(buf, REP)
　　　　.PutInClipboard
　　End With

With Sheets.Add
　　　　.Paste .Range("A1")
　　End With

Set reg = Nothing
End Sub

画面更新や再計算やイベントの停止解除は別途組み入れてくださいね

WindFaller · Answer

ひとつだけ、隠し技ではないのですが、ほんのちょっとしたことですが、このようにすると、繰り返しのマクロでは楽になります。これは、ADOでも同じです。
こうすれば、二度目からのオーバーヘッドの負担は軽減されます。

後は、どんな使い方をするかにもよります。
なおADOは、Excel の2007以降ですと、昔のコードとは変わるそうです。

'--------------
Private objFS As FileSystemObject　　'←ここでオブジェクトを確保します。

Sub FSOImport()
　Dim objFile As Object
　Dim objText As Object
　Dim myText As String
　If objFS Is Nothing Then　
　'もしもオブジェクトを確保していなかったら、インスタンスを作る。
　　Set objFS = New FileSystemObject
　End If
　・
　・

今後、また、新しい技術が増えるでしょうし、今までのものでも、クリアしていないものもあり、まだまだ、別に抱えている宿題が残っています。

WindFaller · Answer

これが今回、時間はかかりましたが、最後の宿題の答えになります。結論めいたことは言わないで置きますが、最後まで通して、思わぬ結果になったと思っています。ADOは、アーリー・バインディングで最高のタイムは出していますが、FileSystemObjectも同様の条件にしたら、変わるかもしれません。ただ、通常、FileSsytemObject は、参照設定はしないことにしています。 >５）FSOも結局は１行処理私は、前回も書いたとおり、スピード競争ではありませんから、そのテクニックだけを確立すればよいと思っています。以下、レイト・バインディングのFileSystemObject です。前回の郵便番号CSVを使い、結果としても6秒の好タイムで、終わることが出来ました。ストレスを感じるか感じないかは、ひとそれぞれですから、それ自体は、なんとも言えません。コードは、行き当たりばったりで、お世辞にもうまいとは言えませんが、ReadAll で、最後の行数を取る所がミソです。若干、行数のズレが存在しているかもしれませんが、エラーを出さない限りはよしとしています。 >補足日時：2016/11/16 11:38 >「Open FileName For Input As #1・・・Output と組み合わせると」とＢ）は同じ？同じかどうかは、細かいテクニックの違いがあります。 Excelの原則論としては、『スプレッドシート上の出入りを極力避けることでスピードを速くすることができる。』ということです。　For i = 0 To UBound(buf) 　 x(j, i) = buf(i) 　Next i それは、以下の部分でも生きています。私としては、Open FileName For ～は捨てがたいのは、プログラムが、構造的に単純だからです。 '// Const myFile As String = "C:\Temp\Test1\KEN_ALL.CSV" Sub FSOImport() 　Dim objFS As Object 　Dim objFile As Object 　Dim objText As Object 　Dim myText As String 　Set objFS = CreateObject("Scripting.FilesystemObject") 　Set objText = objFS.OpenTextFile(myFile) 　Dim rw As Long, cl As Long, arbuf, ar, i As Long, j　As Long 　Dim flg As Boolean, ea, buf 　myText = objText.ReadAll 　rw = objText.Line 　If rw < 2 Then 　　MsgBox "複数の行が存在しません。", vbExclamation 　　Exit Sub 　End If 　arbuf = Split(myText, vbCrLf) 　rw = UBound(arbuf) 　arbuf = Split(myText, vbLf)　'Unixタイプ　rw = UBound(arbuf) 　If rw < 1 Then 　　MsgBox "改行コードが見つかりません。", vbExclamation 　End If 　i = 0 　'ここでは、ヘッダーが存在しているとします。　buf = Split(arbuf(0), ",") 　cl = UBound(buf)　　'ヘッダーもしくは最初の行の列数を調べる　ReDim x(1 To rw, 1 To cl + 1) 　For i = 1 To rw - 1 　　buf = Split(arbuf(i - 1), ",") 　　For j = 1 To UBound(buf) + 1 　　　If j < (cl + 1) Then　'エラー防止　　　　 x(i, j) = buf(j - 1) 　　　End If 　　Next j 　Next i 　Application.ScreenUpdating = False 　 Cells(1, 1).Resize(rw, cl).Value = x 　Application.ScreenUpdating = True End Sub '//

WindFaller · Answer

こんにちは。

私も勉強しながらの回答ですから、解答の切れ味が悪くてすみませんです。

C)を試してみました。
なるほど、速いですね。長いあいだやったことがなかったもので、いろいろと変わっている部分もありました。

郵便番号辞書 CSV 2016年版で試してみました。
123799行, 15列

計測タイム
CSV_DirectConnect: 5103 /1000 秒　->5.1秒

残るは、「FSO」になりますね。「結局は１行処理になるため」ではないと思います。

以下、参照設定は、Microsoft ActiveX Data Objects 6.1 Library
Windows10 32bit, Excel 2013
今回は、計測の方法を公開しておきます。（これは、テンプレートに入れています。)

'//
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Sub CSV_DirectConnect()
　 Dim myTime(1) As Long
　 myTime(0) = timeGetTime() 'Start
　
　Dim cnADO As ADODB.Connection
　Dim mySource As String
　Dim myPath As String
　Dim strQuery As String
　Set cnADO = New ADODB.Connection
　'On Error GoTo ErrHandler
　myPath = "C:\Temp\Test1\"
　mySource = "KEN_ALL.CSV"
　With cnADO
　 .Provider = "Microsoft.ACE.OLEDB.12.0"
　 .Properties("Extended Properties") = "Text;HDR=NO"
　 .Open myPath
　End With
　strQuery = "SELECT * FROM " & mySource & ";"
　Set rsADO = New ADODB.Recordset
　Set rsADO = cnADO.Execute(strQuery)

'空のシート
　ThisWorkbook.Activate
　ActiveSheet.Range("A1").CurrentRegion.ClearContents
　ActiveSheet.Range("A1").CopyFromRecordset rsADO

cnADO.Close
　Set rsADO = Nothing
　Set cnADO = Nothing

ErrHandler:
　If Err() <> 0 Then
　　MsgBox Err.Number & ": " & Err.Description
　End If
　myTime(1) = timeGetTime() 'Finish
　Debug.Print "CSV_DirectConnect:"; myTime(1) - myTime(0)
End Sub
'//

WindFaller · Answer

まず、QueryTableだけは苦手ですね。それは、お仕着せで、コントロールがほとんど効かないし、後々、変な痕跡を残します。「,」 + 「/」とかの切り分けができるという人もいますが、それすら、一般のコードでも出来ますからね。

それはともかくとして、

B)
#2さんのご指摘にもありましたが、わたし流ですと、最初に、大きな配列変数を作っておいて、そこに入れてしまいます。
x(10,10000) とか。郵便番号辞書のCSV 123,733行　16列　12.4M　14秒ぐらい。
大きさが分からない時は、FileSystemObjectで、調べれば取れます。

Dim Fname As String
　Dim FNo As Integer
　Dim TextLine As String
　Dim i As Long, j As Long
　Dim x As Variant
　Fname = "C:\Temp\Yubin.csv"
　FNo = FreeFile()
　ReDim x(130000, 16)
　Open Fname For Input As #FNo
　Do While Not EOF(FNo)
　　Line Input #FNo, TextLine
　　buf = Split(TextLine, ",")
　　For i = 0 To UBound(buf)
　　 x(j, i) = buf(i)
　　Next i
　　j = j + 1
　Loop
　Close #FNo
　Range("A1").Resize(j , 16).Value = x　　'ここらへんは適当
'セルに貼り付ける大きさは、配列に従ってはいません。

これで遅いと感じたことはありません。得意不得意はあるかもしれませんが、ハング状態になっているわけでもありません。

end-u · Answer

最近verのVBAはあまり弄ってないのですが
>1)
ちょい前までは「エクセルで素直に」開くのが一番遅い、って認識を持ってました
（CSVファイルのデータ量やデータ形式にもよりますが）
(B)や(C)、つまりはOpenステートメントやADOを使った手法の方が速い

>２）
(A)はPasteSpecialメソッドを使ってるからシートへの書き込みは１回です
(B)(C)は１セルずつ書き込んでますからそれはそれは遅くなります

(B)はデータ読み込み用に配列を準備して、Loopしながら一旦配列にデータをセット
その配列からあとでまとめてシートに書き込めば１回です

(C)せっかくRecordsetオブジェクトを使っているのですから、
CopyFromRecordsetメソッドを使えば書き込みは１回です

#あとまあ、一昔前は、QueryTableを使うのもコード簡単でかつ速いですよ、なんて回答してましたが
#今はどうなんでしょうね

WindFaller · Answer

こんにちは。

そのご相談というのは、特に、他人が口を挟む話ではないとは思いますが、自分の得意なもので処理すればよいのであって、処理時間について際立って、大きな違いが生じることはないはずです。

また、処理時間の優劣というのは、外部のオブジェクトを使う限りは、オーバーヘッド（間接的な時間のロス)を考慮しないというわけには行かず、内部コマンドのほうが、使っていて有利になるのは自明だと思います。

「奇を衒（てら)って」とは言いませんが、自分が得意ならともかく、マニュアル片手に、ADOを使うぐらいなら、Open FileName For Input As #1 を使えばよいと思います。

>速度向上の余地はあるのでしょうか？
この前の宮﨑駿さんのアニメの中で、主人公の豚さんが「レースじゃねえんだからな」という言葉がありましたが、数秒の差で優劣を付けるようなものでもないと思います。私のマクロデビューでは、退社時間にマクロを掛けて、朝出来上がっているが、それを縮められないか、という相談で、20分で出来たということで、大変に喜んでもらった覚えがあります。まだ、Lotus123もExcelも、市場には存在しない時代でしたが。

最近、CSVそのものが、Excelから発行されたものでないもので、文字コードはUnicodeだったり、UTF-8だったりして、CSVのインポートが失敗するという相談も見かけます。

私は、それをあえて手動で変換する方法をお勧めしてまいますが、プログラムで変更をする場合もあります。そういう時に、後で連結する場合は、自分で分かりやすい方のプログラムを使ったほうがよいのは言うまでもないことです。

Open FileName For Input As #1
は、データそのものの加工に優れています。Output と組み合わせると、Excelの出力することもなく、メモリー上で処理することも可能です。

DAO は一時代前のものですが、なかなか使い勝手は良かったと思います。いつの間にか、ADOに変わりました。今はもう、手をつける必要はないと思います。

>FSOも結局は・・・速度向上の余地
方法がないわけではないものの、Excel等がない状態で使うなら別ですが、選択の余地はあっても、ふつうは使わないはずです。

これらは、あくまでも私の個人的な印象で、それぞれの状況を正確に把握して、全体的な視点に立ってい言っているものではありません。

VBA：CSVファイルの読出し手法別処理速度に関して

re:補足コメント

ひとつだけ、隠し技ではないのですが、ほんのちょっとしたことですが、このようにすると、繰り返しのマクロでは楽になります。

これが今回、時間はかかりましたが、最後の宿題の答えになります。

こんにちは。

まず、QueryTableだけは苦手ですね。

最近verのVBAはあまり弄ってないのですが

こんにちは。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング