ExcelマクロでCSV大量書き出しを高速化する「ADODB.Stream」

2020/01/13
この記事は約5分で読めます。

ExcelマクロでテキストファイルやCSVファイルを書き出すのは簡単です。

  1. Open でファイルを開く
  2. Print でファイルに1行書き出す
  3. Close でファイルを閉じる

が基本的な流れです。
複数行の書き出しは、2番を繰り返します。

 
しかし、書き出しが大量になるとネックは遅さです。
1行にどれくらいの文字数があるか、で異なりますが、1,000行で5分程度、10万行ならば数十分かかります。

テストデータを作成するときに、数十分も待てません。
（パソコンのCPUやメモリが占有状態になるので、パソコンで他の作業をしながら待つのも無理です）

 
そこで今回は、Excelマクロの大量書き出しを高速化する「ADODB.Stream」を紹介します。
この機能を使うと10万行が1分で書き出されます。
ExcelCSVでUTF-8を扱う場合にも、このADODB.Streamが使えます。

基本構文は次のとおりです。

  1. object.Open で、ADODBのstreamオブジェクトを開く
  2. object.WriteText で、stream内(メモリ内)にデータを溜める
  3. object.SaveToFile で、stream内のデータを外部ファイルに書き出す
  4. object.Close で、streamオブジェクトを閉じる

複数行の書き出しは、2番を繰り返します。

処理は、Print で一行ずつファイル書き出しするのとは異なり、WriteText（上記2番） でメモリ内に処理データを全て溜め込んでから SaveToFile（上記3番） で一気に書き出します。
処理速度のボトルネックは外部ストレージに対するI/O（Input/Output）なので、ここを一度で済ませれば速度改善になるのです。そのため、数行～数十行程度の処理では速度の差が分かりませんが、数万件とデータ量が多くなればなるほど顕著な差が出ます。

 
オプション指定も色々できます。
よく使われるのが、Charset プロパティ（文字コード指定）です。
デフォルトはUnicodeですが、それ以外にUTF-8、Shift_JIS、euc-jp、ISO-2022-JPなどが使えます。

ADODBを使うときは、マクロの参照設定が必要です。

このコードエディタのメニューの「ツール」⇒「参照設定」を開きます。

「Microsoft ActiveX Data Objects x.x Library」を選びます。（x.xの部分は一番大きな数字でOK）

サンプルコードを載せます。
私がよく使う「Excelで作成した（システム開発時の）テストデータを、CSVファイルとして書き出す」処理です。

A1⇒テーブル名　※処理中では使っていない
2行目⇒論理名　　※処理中では使っていない
3行目⇒属性　　　※処理中では使っていない
4行目⇒桁数　　　※処理中では使っていない
5行目⇒物理名
6行目以降で、テストデータ

50カラムある場合はA～AX列
1,000レコードある場合は6～1,006行
にテストデータが記載されているものと想定します。

Option Explicit

Public Sub BULK_INSERT用のCSV作成()

    'ADODB.Streamを使う
    Dim ado As Object
    Set ado = CreateObject("ADODB.Stream")
   
    '変数
    Dim i As Long               'データ開始行
    Dim j As Integer            '列
    Dim ColNRow As Integer      '何行目にカラム物理名を記載しているか
    Dim eCol As Integer         '一番右の列の位置（要素数が不明な場合でも処理可能）
    Dim eRow As Long            '一番下の行の位置（　〃　）
   
    '定数
    Const SaveF = "C:\test.csv"           '保存ファイル名

    'カラム物理名の行
    ColNRow = 5                 '5行目に物理名を書いている（右端を取得する基準として使用）
   
    '一番下の行の位置を取得する
    eRow = Cells(ColNRow, 1).End(xlDown).Row
   
    '一番右の列の位置を取得する
    eCol = Cells(ColNRow, 1).End(xlToRight).Column

   
    '処理開始
    ado.Open                    'ADODB.Streamを開く
    ado.Charset = "UTF-8"       '文字コード指定（UTF-8）
   
    'メイン処理（A列の値がある間はループする）
    For i = 6 To eRow
        'A列のみ（先頭にカンマを付けたくないから）
        j = 1
            ado.WriteText Cells(i, j)
       
        'B列より右側
        For j = 2 To eCol
            ado.WriteText "," & Cells(i, j)
        Next

        '行の最後で改行コード
        ado.WriteText "", adWriteLine
    Next
   
    'ADODB.Streamに保管されている内容を、ファイルに保存する
    ado.SaveToFile SaveF, 2
   
    'ADODB.Streamを閉じる
    ado.Close
   
    MsgBox "完了"

End Sub
タイトルとURLをコピーしました