【VBA】UTF-8 CSV の読み込みを、ADODB.Recordset と ADODB.Stream で比べてみた・・・第１回 １行ずつ読み込みしてみる
初めに
以前、ADODB.Recordset を使って、CSV の読み込みについてまとめたのだけれど、
世間一般では、UTF-8 のCSVに限って言えば、ADODB.Stream を使うのがメジャーなようです。
というか、「ADODB.Recordset の記事あるの？」って感じです。
自分が紹介した ADODB.Recordset による方法のマイナー感が拭えなくて、ちょっと悔しかったこともあり、２つを比べてみました。
また、後からまとめていた際に、分かってきたこともあり、追加調査分も含めて２回に分けて書いていこうと思います。
データ
まずは、使うデータ。
自分で作るのは面倒なので、公開されているものを探したら
以下のサイトが見つかったので、使わせてもらうことにした。
使ったのは、その中の東京のデータ（13_tokyo_all_20190930.csv）
ADODB.Streamを使う場合には改行コードが、CRLFではないので、指定が必要です。
先頭２行抜粋したのが以下
1,1000011000005,01,1,2018-04-02,2015-10-05,"国立国会図書館",,101,"東京都","千代田区","永田町１丁目１０－１",,13,101,1000014,,,,,,,2015-10-05,1,"National Diet Library","Tokyo","1-10-1,Nagatacho, Chiyoda ku",,"コクリツコッカイトショカン",0 2,1000012010003,01,1,2018-04-02,2015-10-05,"内閣法制局",,101,"東京都","千代田区","霞が関３丁目１－１中央合同庁舎第４号館",,13,101,1000013,,,,,,,2015-10-05,1,"Cabinet Legislation Bureau","Tokyo","3-1-1,Kasumigaseki, Chiyoda ku",,"ナイカクホウセイキョク",0
ダウンロード元
t.co
処理概要
ADODB.Recordset
ADODBでデータベースに接続する場合と同様の処理です。
接続文字列を設定して、SQL発行して、Recordset を取得し、EOFまでループします。
ADODB.Stream
一般的に公開されているサンプルと同様の処理です。
Stream を開いて、LoadFromFile でロードして、
ReadText(adReadLine)で１行ずつ読み込み、Splitして、
EOFまでループします。
コード
共通部分
Option Explicit Private Const TARGET_FOLDER As String = "C:\Datas\CSV\法人情報\" Private Const TARGET_NAME As String = "13_tokyo_all_20190930.csv"
ADODB.Recordset
Public Sub readByAdoRecordset() Dim cn As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset Dim lRecords As Long Dim i As Long Dim sgStart As Single Dim sgStop As Single Dim v As Variant sgStart = Timer On Error GoTo ERR_EXIT Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source=" & TARGET_FOLDER & ";" _ & "Extended Properties=""Text;" _ & "HDR=No;" _ & "FMT=Delimited""" Set cmd = New ADODB.Command Set cmd.ActiveConnection = cn cmd.CommandType = adCmdText cmd.CommandText = "SELECT * FROM [" & TARGET_NAME & "]" Set rs = New ADODB.Recordset rs.CursorLocation = adUseServer ' rs.CursorLocation = adUseClient rs.CursorType = adOpenForwardOnly rs.LockType = adLockReadOnly rs.Open cmd lRecords = 0 Do Until rs.EOF lRecords = lRecords + 1 v = rs.GetRows(1) Loop ERR_EXIT: If Err.Number <> 0 Then Debug.Print "[" & Err.Source & "]" & "[" & CStr(Err.Number) & "] " & Err.Description End If If Not rs Is Nothing Then If rs.State = adStateOpen Then rs.Close End If Set rs = Nothing End If If Not cmd Is Nothing Then Set cmd.ActiveConnection = Nothing Set cmd = Nothing End If If Not cn Is Nothing Then If cn.State = adStateOpen Then cn.Close End If Set cn = Nothing End If sgStop = Timer Debug.Print "Done. [ " & Format$(sgStop - sgStart, "0.00") & " sec.][ " & CStr(lRecords) & " records.]" End Sub
Scema.ini
[13_tokyo_all_20190930.csv] Format=CSVDelimited CharacterSet=65001 ColNameHeader=False
ADODB.Stream（１行ずつ読み込み）
Public Sub readByAdoStream() Dim strm As ADODB.Stream Dim sLine As String Dim vItems As Variant Dim lRecords As Long Dim i As Long Dim sgStart As Single Dim sgStop As Single sgStart = Timer On Error GoTo ERR_EXIT Set strm = New ADODB.Stream With strm .Type = adTypeText .Charset = "UTF-8" '今回使用したファイルの改行コードは、LF ' .LineSeparator = adCRLF .LineSeparator = adLF .Open .LoadFromFile TARGET_FOLDER & TARGET_NAME Do Until .EOS sLine = .ReadText(adReadLine) vItems = Split(sLine, ",") lRecords = lRecords + 1 Loop .Close End With ERR_EXIT: If Err.Number <> 0 Then Debug.Print "[" & Err.Source & "]" & "[" & CStr(Err.Number) & "] " & Err.Description End If If Not strm Is Nothing Then If strm.State = adStateOpen Then strm.Close End If Set strm = Nothing End If sgStop = Timer Debug.Print "Done. [ " & Format$(sgStop - sgStart, "0.00") & " sec.][ " & CStr(lRecords) & " records.]" End Sub
結果
今回の方法では、結果は以下の通り、
ADODB.Recordset を使った方が圧倒的に速い。
Split 無しの場合で、約20倍
Split 有りの場合で、約6倍
の速度となりました。
あくまで、今回の方法ではです。（重要！）
| 種類 | カーソル ロケーション | Split有無 | 時間（秒） | 備考 |
|---|---|---|---|---|
| Recordset | Server | 無 | 6.76 | |
| 有 | 24.11 | |||
| Client | 無 | 24.10 | ||
| 有 | 34.97 | |||
| Stream | - | 無 | 134.88 | １回目 |
| 340.79 | ２回目 | |||
| - | 有 | 145.61 | １回目 | |
| 353.99 | ２回目 |
但し、上記の結果は全てをそのまま鵜呑みには出来ない部分もあります。
- ADODB.Recordset の各フィールドデータと、Splitによる分割をした結果が明らかに異なっている。
これは処理したコードの問題。（こちらにサンプル） - ADODB.Stream の読み込みが、２回目以降が極端に遅くなること。
１項は、１行のデータを要素に分割する手段として、単純に Split しただけのため、
- 「"」で囲まれた要素は、「"」が残る。
- 「"」で囲まれた要素の中に、「,」がある場合、本来１つの要素が複数に分割されてしまった。
ことが原因です。
こちらにもう少し詳しくまとめてあります。
Split 有りのデータについては、参考程度に御覧ください。
２項は根本原因はわかりません。
（予想される要因はありますが、あくまで予想であり、コードで対応できる問題ではなさそうなので・・・）
ただ、改善策はあるので、それは次回とします。
要素分割が正しく行われていなかった・・・
元になるCSVの１行に、こんなデータがありました。1,1000011000005,01,1,2018-04-02,2015-10-05,"国立国会図書館",,101,"東京都","千代田区","永田町１丁目１０－１",,13,101,1000014,,,,,,,2015-10-05,1,"National Diet Library","Tokyo","1-10-1,Nagatacho, Chiyoda ku",,"コクリツコッカイトショカン",0
この中に、
"1-10-1,Nagatacho, Chiyoda ku"
「"」で括られた中に、「,」があるので、以下の様になってしまいました。
| ADODB.Recodeset | 1-10-1,Nagatacho, Chiyoda ku | ||
|---|---|---|---|
| ADODB.Stream | "1-10-1 | Nagatacho | Chiyoda ku" |
ADODB.Recorset は、望んだ値が得られています。
一方、ADODB.Stream の方は、何も考えず、Split しただけなので、当然残念な結果になっています。
分割もおかしいし、要素の前後の「"」も残っています。
今回は、読み込み速度の比較がメインなので、このままとします。
上記の対応をしたところで、現時点での速度差が圧倒的なので、無視できる範囲内と想定できるので。
それに、対応すると、差が広がる一方なので・・・
まとめ
それぞれの方法の比較
| 項目 | Recordset | Stream | 備考 |
|---|---|---|---|
| 速度 | 速い | 遅い ２回目以降は特に遅い |
|
| 要素を分割する処理コード | 不要 | 必要 | |
| 改行コード指定 | 不要 | 必要（CRLF以外の場合） | |
| Schema.ini | 必要 | 不要 | |
| 接続文字列 | 必要 少し複雑 |
不要 |
上にも書いたのですが、要素の分割の手間を考えると、
個人的には、ADODB.Recordset の使用がオススメです。
処理速度の点もありますが
- Schema.ini を書く手間
- Split処理を書く手間
「どちらを選択するか？」と言われれば、私は、Schema.ini 書きます。
必要なら、Schema.ini のテンプレート作るツール作ります。（たぶん・・・）
使うデータにも依存するので、どちらを選ぶかは、使う方の判断で・・・
次回予告
今回の結果だけを見ると「ADODB.Recrdset の圧勝」となってしまったのだけれど、色々調べていると
1行読み込みが遅い時の対処法
というのを見つけたので、これについて検証してみます。
また、この対応をしたコードで同じファイルを使って速度を比較してみます。
www.mussyu1204.com
2019/10/23 追記
続編はこちら
z1000s.hatenablog.com
