Line InputとQueryTableの比較・csvファイルを高速で読込む

大量データのcsvファイルを配列に格納する方法のうち、次の２つを比較します。

速いのは「Line Input」です。

csvファイルのサンプル

次のscvを使って比較します。

csvファイル

• 保存先：F:\sample
• ファイル名：test.csv
• 行：24,001、列：401
• データは英数字のみ

このファイルをただ開くのにかかる時間は約7.4秒です。

比較内容

• やりたいこと：読み込むCSVファイルのうち、条件にあうデータをシートに書き出したい。

このような処理をする場合、一度配列にデータを格納した方が速いです。

参考：2次元配列に連続で格納されてる値を一括でコピーしシートへ貼り付ける

比較するのは、配列に格納する際にLine InputとQueryTableではどちらが速いかです。

比較・サンプルコード

Line InputとQueryTable の比較前に、単純にcsvデータを開き配列に格納するとかかる時間を計りました。

処理時間は、10.9375秒でした。

Sub csvOpen1()
    Dim ws As Worksheet   
    Dim fPath As String: fPath = "F:\sample\test.csv"
    Dim wb As Workbook: Set wb = Workbooks.Open(fPath)
    Set ws = ActiveSheet
    Dim tmp As Variant: tmp = ws.Range("A1").CurrentRegion
    wb.Close    
End Sub

Line Input

処理時間：7.78125秒

Sub csvOpen2()
　  Dim fName As String
    Dim lastR As Long, lastC As Long
    Dim buf As String, tmp As Variant, ary() As Variant
    Dim r As Long, c As Long, i As Long, cnt As Long
  
    fName = "F:\sample\test.csv"
    lastR = CreateObject("Scripting.FileSystemObject").OpenTextFile(fName, 8).Line
  
    Open fName For Input As #1
    Line Input #1, buf
    tmp = Split(buf, ",")
    lastC = UBound(tmp)
    ReDim ary(lastR - 2, lastC) As Variant
    
      For c = 0 To UBound(tmp)
        ary(r, c) = tmp(c)
      Next c
      r = r + 1
  
    Do Until EOF(1)
      Line Input #1, buf
      tmp = Split(buf, ",")
      For c = 0 To UBound(tmp)
        ary(r, c) = tmp(c)
      Next c
      r = r + 1
    Loop
    Close #1
End Sub

QueryTable

処理時間：23.87109375秒

Sub csvOpen3()
    Dim fName As String: fName = "F:\sample\test.csv"
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim imData As QueryTable
    
    Set imData = ws.QueryTables.Add(Connection:="TEXT;" & fName, _
                    Destination:=ws.Range("A1"))
    With imData
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh
        .Delete
    End With
    Dim tmp As Variant: tmp = ws.Range("A1").CurrentRegion
    ws.Range("A1").CurrentRegion.Clear
End Sub

csvファイルの文字コードをUTFで保存した場合は、次のプロパティを追記します。

.TextFilePlatform = 65001

CSVデータを読込後に条件に合うデータをシートに書き出す

大量データのCSVファイルを読み込み後、2次元配列に連続で格納されてる値を一括でコピーしシートへ貼り付ける方法 です。

「2次元配列に連続で格納されてる値を一括でコピーしシートへ貼り付ける」の応用です。

Sub sample()   
    Dim fName As String
    Dim lastR As Long, lastC As Long
    Dim buf As String, tmp As Variant, ary() As Variant
    Dim r As Long, c As Long, i As Long, cnt As Long
    
    Dim ws1 As Worksheet: Set ws1 = Worksheets("設定")
    Dim ws3 As Worksheet: Set ws3 = Worksheets("出力")
    
    Dim joken As Variant: joken = ws1.Range("A1").CurrentRegion
  
    fName = "F:\sample\test.csv"
    lastR = CreateObject("Scripting.FileSystemObject").OpenTextFile(fName, 8).Line
  
    Open fName For Input As #1
    Line Input #1, buf
    tmp = Split(buf, ",")
    lastC = UBound(tmp)
    ReDim ary(lastR - 2, lastC) As Variant
    
      For c = 0 To UBound(tmp)
        ary(r, c) = tmp(c)
      Next c
      r = r + 1
  
    Do Until EOF(1)
      Line Input #1, buf
      tmp = Split(buf, ",")
      For c = 0 To UBound(tmp)
        ary(r, c) = tmp(c)
      Next c
      r = r + 1
    Loop
    Close #1
    
    ReDim tmp(LBound(ary) To UBound(ary)) As Variant
    
    For r = LBound(tmp) To UBound(tmp)
       tmp(r) = ary(r, 0)
    Next r
    
    With ws3
       .Range(.Cells(1, 1), .Cells(UBound(ary) + 1, 1)) = _
                             WorksheetFunction.Transpose(tmp)
    End With
    
    cnt = 2
    For i = 2 To UBound(joken)
        For c = 1 To UBound(ary, 2)
             If joken(cnt, 1) = ary(0, c) Then
                For r = LBound(ary) To UBound(ary)
                    tmp(r) = ary(r, c)
                Next r
                With ws3
                    .Range(.Cells(1, cnt), .Cells(UBound(ary) + 1, cnt)) = _
                                            WorksheetFunction.Transpose(tmp)
                End With
                cnt = cnt + 1
                Exit For
            End If
        Next c
    Next i
End Sub