Excel

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

2021-08-22

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

  1. Line Input
  2. QueryTable

速いのは「Line Input」です。

csvファイルのサンプル

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

csvファイル

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

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

csvファイルを開く

比較内容

  • やりたいこと:読み込む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

-Excel
-

© 2021 オフィスのQ&A