エクセルVBAでQueryTableオブジェクトを使って高速にCSVを取り込む方法

CSV取り込みアイキャッチ
みなさん、こんにちは!
フジタニ(@libartweb)です。

CSVの取り込みをエクセルVBAで行う場合は、ループを使って1行ずつ読み込んでいくのが一般的な方法です。

今回は、そんなCSV取り込みを、ループを一切使わずに、一発で取り込むもう一つの方法を紹介します。

大きなCSVの取り込み処理が月に数回、もしくは1日に何回も…という高い頻度で実施される方、速度をもっと速くしたい!という方は必見です!

CSVの取り込みがもっとラクに、速くできるようになりますよ!

スポンサーリンク

通常のCSV取り込みの流れ

以下の記事でも紹介していますが、通常はCSVを、エクセルVBAでオープンした後、1行ずつセルに書き出していくのが一般的です。

100行や200行など小さなCSVだと処理速度の差は全く体感できないので、ループを使う方法で全く問題ありません。もし、大きなファイルを頻繁に扱う方は今回紹介する方法を検討してみてください!

エクセルVBAでCSVデータをカンマで区切ってワークシートに取り込む方法
エクセルVBAでCSVを取り込む方法です。今回は、Splitという命令と配列を使ってCSVのレコードをカンマで区切ってワークシートに転記をしていく方法について解説をしていきます。

過去の記事で紹介したソースコードは下記の通りです。

ループの中にループがあるので、CSVが大きいとどうしても処理速度で不利になってしまう場合があります。

'==================================
'CSVをループを使用して取り込むサンプル
'==================================
Sub getCSV()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Dim strPath As String
    strPath = "C:\Users\Noriaki\Dropbox\40_ブログ\vba-csv\test\ラーメン店アンケート.csv"
    
    Dim i As Long, j As Long
    Dim strLine As String
    Dim arrLine As Variant 'カンマでsplitして格納
    
    Open strPath For Input As #1 'CSVをオープン
    
    i = 1
    
    Do Until EOF(1)
       
        Line Input #1, strLine
        arrLine = Split(strLine, ",") 'strLineをカンマで区切りarrLineに格納
       
        For j = 0 To UBound(arrLine)
           
            ws.Cells(i, j + 1).Value = arrLine(j)
       
        Next j
        i = i + 1
    Loop
    
    Close #1

End Sub

QueryTableオブジェクトによるCSV取り込み

ループを使ったCSV取り込みが一般的であり、実際に使われている方もたくさんいらっしゃると思います。今回はあまり知られていない、QueryTableオブジェクトを使いますので、まずはその概要について解説します。

最初にサンプルコードを記載します。

Private Sub csvImport()
    Dim strPath As String
    Dim qtCsv   As QueryTable
    
    strPath = "C:\Users\hirom\Desktop\test.csv"
    Set qtCsv = Sheet1.QueryTables.Add(Connection:="TEXT;" & strPath, _
        Destination:=Sheet1.Range("A1")) '取り込むCSVパスと、取り込み先のシート、セルを指定
    
    With qtCsv
        .TextFileCommaDelimiter = True 'カンマ区切りの指定
        .TextFileParseType = xlDelimited   ' 区切り文字の形式
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) '項目(列)の型指定
        .TextFileStartRow = 1 '開始行の指定
        .TextFileTextQualifier = xlTextQualifierDoubleQuote '引用符の指定
        .TextFilePlatform = 932 '文字コード指定
        .Refresh 'QueryTablesオブジェクトを更新し、シート上に出力
        .Delete 'QueryTables.Addメソッドで取り込んだCSVとの接続を解除
    End With

End Sub

書き方がループを使用したCSV取り込みの方法とは大きく異なっており???となっていることでしょう。では解説していきます。

QueryTableオブジェクトとは

Microsoftの公式ページでは下記の通り解説されています。

SQL サーバーや Microsoft Access データベースなどの外部データ ソースから返されるデータに基づいて作成されるワークシート テーブルを表します。

QueryTable オブジェクト (Excel)
Office VBA リファレンス トピック

よくわからないですね・・・

カンタンに解説すると、データタブ→外部データの取り込みの機能を操作するオブジェクトです。

QueryTableオブジェクトでワークシート上に仮想のテーブルを作ってそこにCSVを取り込み、それをワークシート上に展開することができます。

CSV取り込み

文字コードはもちろん、区切り文字からダブルクォーテーションに至るまでプロパティ指定をすることが可能です。今回はCSVを対象に紹介しますが、通常のテキストファイルでも取り込み可能です。

各プロパティの解説は次回記事で実施予定です。

この方法がどのくらい速いか?

実際に上で紹介したサンプルコードを実行し、処理時間を計測してみました。
CSVの行数は54000行です。

★ループで取り込んだ場合
37秒

★QueryTableオブジェクトで取り込んだ場合
6秒

実に、魅力的な高速化ですね。10万行だと1分以上の差が開くことになります。

この方法は、CSVのサイズが大きくなればなるほど恩恵を受けられます。

そのCSVが1個だけでなく、複数あったら、更に差が開きます。

QueryTableオブジェクトが速い理由

通常のCSV取り込みは、ループを使って1行ずつ読み込んでいきます。それも1行ずつ物理的にWorksheet上に展開しています。

対してQueryTableオブジェクトは、いったん「クエリテーブル」上にCSVを展開。しかも1行ずつの取り込みではなく、一気に全行をまとめて取り込むイメージ。

これは例えるとスーパーで買い物をする際に、商品を手で1つずつレジに持って行くのが通常のCSV取り込み、カゴにたくさん入れて持っていくのがQueryTableです。

つまり、たくさんの商品を買う場合はカゴに入れた方が有利です。

普段取り込むCSVのサイズを見て、うまく使い分けましょう。

まとめ、次回予告

今回はQueryTableオブジェクトとは何か?といった概要を掴んでいただく内容でした。

・通常のCSV取り込みの流れ
・QueryTableオブジェクトによるCSV取り込み
・QueryTableオブジェクトとは
・QueryTableオブジェクトが速い理由

一般的な方法の他にもこんな取り込み方があるんだ!ということを知っていただけたと思います。

サイズの大きいCSVを取り込む際に大幅な時間短縮が可能なQueryTableオブジェクトを使用した方法に少し興味を持っていただいたところで次回予告とします。

次回はいよいよサンプルプログラムの解説と、QueryTableオブジェクトを活用してもらうための、メソッドやプロパティの解説を実施します。

使いこなすと、速いだけでなくソースコードもシンプルになり、とても便利なオブジェクトです。

お楽しみに!

連載目次:エクセルVBAのQueryTableで高速にCSVを取り込む

一般的なループを使用したCSV取り込みよりも、更に高速にCSVを取り込むことのできるQueryTableオブジェクトに関する連載です。サイズの大きなCSVを取り込む際に非常に便利です。
  1. エクセルVBAでQueryTableオブジェクトを使って高速にCSVを取り込む方法
  2. エクセルVBAのQueryTables.Addメソッドでクエリテーブルを作成する方法
  3. エクセルVBAのQueryTable.RefreshメソッドでCSVをシートに出力する方法
  4. エクセルVBAのQueryTableオブジェクトのプロパティの基本とCSVをカンマ区切りする方法
  5. エクセルVBAのQueryTableオブジェクトであらゆる種類のCSVを取り込む方法
  6. エクセルVBAで高速にCSVを取り込むQueryTableオブジェクトの最終処理
  7. エクセルVBAでCSVを高速に取り込むQueryTableオブジェクト使用時の注意点
タイトルとURLをコピーしました