ADODB.Streamでゴミデータ(Null文字)がある場合の挙動について

VBAでテキストデータをexcelに取り込むとき

基幹システムのデータを加工したり、他のシステムと連携のため、テキストデータをexcelに取り込む場面は多いと思います。

VBAでテキストデータを扱う代表的な方法に、Openステートメント、FileSystemObject.TextStremObject、ADODB.Streamなどがあります。

その中でもADODB.Streamは、テキストデータの文字コードを選択できたりと他の方法よりも柔軟性が高い点が特徴です。

今回はこのADODB.Streamに焦点を当て、特に私が遭遇した「テキストデータにゴミデータがある」問題について書いています。

ちなみに結論はこちら→##ADODB.Streamで変な挙動をする文字コードは？

そもそもADODB.Streamって？

そもそもADODBとは、何でしょうか。

こういうときは、公式ドキュメントを見てみます。

ADO はさまざまなソースのデータ アクセスに使用されるため、読者には、リレーショナル データベース管理システムの基本的な概念、オンライン分析処理 (OLAP) の概念、およびインターネットやインターネット プロトコルの基本的な知識も必要になります。 ADO は、Microsoft Data Access (UDA) ストラテジの一部です 。(中略)。そのため、OLE DB テクノロジと相互運用性があります。OLE DB は、Microsoft Component Object Model (COM) に基づいています。したがって、COM を理解していると、ADO のさらに高度な概念を理解するのに役立ちます。

『https://docs.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/prerequisites-ado-introduction』より引用

要するに「色々なソースにアクセスするためのオブジェクトだよ」ってことですね。

色々なソースの例として、リレーショナルデータベースやインターネットがあるのですね。

そしてテキストデータもそのひとつであり、ADODBの中に「Streamオブジェクト」というテキストファイル読み書き担当がいる、というイメージでしょうか。

ADODB.Stream#ReadLineでNull文字が含まれるファイルを扱うと

さて、話が脱線してしまいました。

まずは、私が遭遇した問題について紹介したいと思います。

それは、「基幹システムから抽出したテキストデータにゴミが入り、ADODB.StreamのreadTextで一行だけ読もうとしたら全部のデータを読んでしまった」というものです。

その時のVBAのソースは次のとおり。

 

   Const adReadLine = -2
   'Streamオブジェクトを生成する
   Dim stream As Object:Set stream = CreateObject("ADODB.Stream")
   stream.Open
   stream.LoadFromFile "C:\file.txt"
   '一行読み込む
   Dim line As String:line = stream.ReadText(adReadLine)'⇒一行読み込むはずが、全てのデータが読み込まれた

ソースの文量の問題で、ここに全文は載せられませんが、後続の処理はテキストデータから一行ずつ読み込むことを前提に書かれていたので正常に処理できませんでした。

なぜこんなことになってしまったのか・・・丸２日調査した結果、ずばり原因はテキストデータに入ったNull文字(コード0)でした。

問題のNull文字をスペース文字に置換したところ、問題は解決したのでした。

では、なぜNull文字が悪さをしたのでしょうか。

それを理解するためには、ReadTextメソッドがファイル終端をどのように判断しているかを知る必要があります。

Null文字がテキストファイルの途中にあるReadText(adReadLine)の挙動

では、実際にテキストファイル内にNull文字がある場合の挙動を確認するため、実験をしてみます。

どんな実験かというと、 ソースを一つ用意し、Nullの位置でReadTextの挙動がどう変わるかを確認するというものです。 - Nullの入っていないもの - Nullが先頭 - Nullが１行目の途中 - Nullが２行目の途中

用意したソース

 

Sub testNull()
   '一行目の文字列
   Const firstString = "1行目だよ"
   'ReadText引数用
   Const adReadLine = -2
   'Streamの改行コード用
   Const adCRLF = -1
   'Nullが含まれたファイル
   Const IN_FILE As String = "C:\work\null.txt"
   'ADODB.Streamオブジェクトの生成
   Dim strm As Object: Set strm = CreateObject("ADODB.stream")
   strm.Open
   strm.charset = "Shift-JIS"
   strm.LineSeparator= adCRLF
   strm.LoadFromFile IN_FILE
   '一行を読み込む
   Dim line As String: line = strm.ReadText(adReadLine)
   '一行目がfirstStringと違う場合中断する
   Debug.Assert line = firstString
   strm.Close
   Set strm = Nothing
End Sub

Debug.Assertで一行目だけが正しく読み込まれなかった場合、停止します。

用意したファイル

ここにNullを入れて実験します。

Nullがはいっていない場合

→すんなりいきました。

当たり前ちゃあ当たり前ですね。

先頭にNullが入っている場合

バイナリエディタ(Stirling)で先頭にNull(00)を仕込みます。

何やら空白が入っていますね。

ではプログラムを実行しみます。

line変数にテキストファイル内の全ての文字列が入ってしまいました。

実はこれが、私が遭遇した問題と同じケースです。

途中にNull文字が入っている場合

「1行目だ Null よ」という感じに仕込んでみました。

こんな感じです。

では実行してみます。

やはり全データが読み込まれました。

2行目の途中にNull

画面は割愛しますが、1行目は正しく取得できました。

この実験で言えることは、Null文字が入っている行を読んだとき、ReadText(adReadLine)ではファイル内のすべてのデータが読み込まれるということです。

Null文字が入っている場合、どうする？

テキストファイル内にNull文字が入っている場合、誤動作の原因になるため、置換する必要があります。

てっとり早いのは、バイナリエディタで置換してしまうことでしょう。

しかし、場合によってはVBAの流れの中で置換して一気通貫に処理したいケースもあるかと思います。

その場合は、やはりADODB.Streamの機能をフル活用することで、ファイル内のNull文字を置換することができます。

ポイントは、これまで見てきたように、テキストファイルとして扱うと誤作動が起きることがわかっているので、StreamのTypeをバイナリにしてあげることです。

 

'引数 inFile=置換対象のテキストファイルパス、outFile=置換後のパス、replacement=Null文字の置き換え後の文字
Sub removeNull(inFile As String, outFile As String,replacement as Byte)
   Dim strm As Object: Set strm = CreateObject("ADODB.stream")
   strm.Open
   'ここでTypeをタイプをバイナリに設定する
   strm.Type = 1
   strm.LoadFromFile inFile
   'ファイルの内容をバイト配列として全て読み込む
   Dim bin() As Byte: bin = strm.read
   Dim i As Long
   '出力用のバイト配列を用意する
   Dim destBin() As Byte: ReDim destBin(UBound(bin))
   '読み込んだバイト配列を１つずつ検査して0(Null文字)ならreplacementと置き換える
   For i = 0 To UBound(bin)
      If bin(i) <> 0 Then
         destBin(i) = bin(i)
      Else
         destBin(i) =replacement
      End If
   Next i
   '書き出し用のストリームを用意する
   Dim out As Object: Set out = CreateObject("ADODB.stream")
   out.Open
   out.Type = 1
   out.write destBin
   out.SaveToFile outFile
   strm.Close
   out.Close
   Set strm = Nothing
   Set out = Nothing
End Sub

この処理をはさんであげれば一気通貫に置換しつつ処理ができるかと思います。

変な文字が入り込まないように注意しましょう

外部システムが作成したデータをADODB.Streamで処理したときに誤動作が見られる場合は、ゴミデータが疑われます。

そして、ここまで行きつくのに時間がかかることもあります（私はそうでした・・・）

一番の解決策は、変なゴミがデータに入り込まないようにすることですが・・・。

同じような問題を抱えている人にとって、この記事が少しでも役に立てば。

ここまでお読みいただき、ありがとうございました。