文字化けよさようなら!エクセルVBAでUTF-8のCSVを読み込む方法


stream

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルVBAで色々なタイプのCSVを取り込む方法をお伝えしています。

前回はこちらの記事でした。

エクセルVBAでデータにカンマが含まれてしまっているCSVを取り込む
エクセルVBAで様々なタイプのCSVを取り込んでいきます。今回は、意外と多いパターンであるデータの中にカンマが含まれている場合のCSVをエクセルVBAで取り込む方法についてお伝えしていきます。

エクセルVBAでCSVの取り扱いをしていると、必ず出くわすと言ってもよい「UTF-8問題」

これまでの方法ではUTF-8のCSVファイルは取り込むと文字化けを起こしてしまいます。

今回は、ADODB.Streamオブジェクトというものを使ってエクセルVBAでUTF-8のCSVファイルを取り込む方法についてお伝えします。

スポンサーリンク

前回のおさらい

前回のおさらいとしてこちらのプログラムをご覧ください。

Sub getCSV_camma()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "C:UsersNoriakiDropbox40_ブログvba-csvtestラーメン店アンケート_dq & comma.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(Replace(replaceColon(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

流れとしては

  1. OpenでCSVファイルをオープン
  2. CSVのレコードを1行ずつ取り込む
      1. データ区切りのカンマをコロンに置き換える
      2. ダブルクォーテーションを削除
      3. コロンで区切って配列に格納
  3. 配列内の要素をワークシートのセルに書き込む

という流れです。

replaceColonはデータ区切りのカンマをコロンに置き換える自作の関数です。詳細は前回の記事をご覧頂ければと思います。

UTF-8は文字化けしてしまう

こちらのプログラムでUTF-8形式のCSVを取り込もうとすると

エクセルVBAでUTF-8のCSVを読み込むと文字化け

…文字化けでぐちゃぐちゃになってしまいます。

Line Input命令では有無も言わさずShift-JISのつもりでデータを読み込んでしまうのです。

ADODB.StreamオブジェクトでUTF-8のCSVを読み込む

UTF-8のCSVを取り込むにはADODB.Streamオブジェクトを使います。

ストリーム?なんじゃそりゃ、という感じですよね…

IT用語辞典によりますと

ストリームとは、小川、流れ、連続などの意味を持つ英単語で、ITの分野では連続したデータの流れや、データの送受信や処理を連続的に行うことなどを意味する。
IT用語辞典:ストリーム

データの流れ…なるほど、ストリーミングのストリームと一緒ですね。

それでもわかりづらい感じもしますが…ADODB.StreamというタイプのオブジェクトにいったんCSVファイルを入れると文字コードがUTF-8でも取り扱いができるようになる、という認識で頂ければと思います。

ライブラリを追加

まず準備として、ADODB.Streamを使えるようにするためにライブラリを追加する必要があります。

Visual Basic Editor のメニューのメニューから「ツール」→「参照設定」を開いて、参照可能なライブラリファイルの中から「Microsoft ActiveX Data Objects x.x Library」を選んでください。執筆時点ではバージョンは「2.8」ですが、最新のものを選んで頂いて良いと思います。

VBEでADODBライブラリを追加

UTF-8のCSVを読み込むプログラム

プログラムはこちらになります。

Sub getCSV_utf8()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "C:\Users\Noriaki\Dropbox\40_ブログ\vba-csv\test\ラーメン店アンケート_utf8.csv"

Dim i As Long, j As Long
Dim strLine As String
Dim arrLine As Variant 'カンマでsplitして格納

'ADODB.Streamオブジェクトを生成
Dim adoSt As Object
Set adoSt = CreateObject("ADODB.Stream")

i = 1
With adoSt
    .Charset = "UTF-8"        'Streamで扱う文字コートをutf-8に設定
    .Open                             'Streamをオープン
    .LoadFromFile (strPath) 'ファイルからStreamにデータを読み込む

    Do Until .EOS           'Streamの末尾まで繰り返す

        strLine = .Readtext(adReadLine) 'Streamから1行取り込み

        arrLine = Split(Replace(replaceColon(strLine), """", ""), ":") 'strLineをカンマで区切りarrLineに格納

        For j = 0 To UBound(arrLine)

            ws.Cells(i, j + 1).Value = arrLine(j)

        Next j
        i = i + 1
    Loop

    .Close
End With

End Sub

色々と新しい命令が出てきているので難しく感じるかも知れませんが、基本的な流れはLine Inputのそれとあまり変わりません。

  1. ADODB.Streamオブジェクトを生成
  2. ADODB.Streamオブジェクトの文字コードの設定
  3. ADODB.StreamオブジェクトをオープンしCSVファイルを取り込む
  4. ADODB.Streamオブジェクトからレコードを1行ずつ読み込む
      1. データ区切りのカンマをコロンに置き換える
      2. ダブルクォーテーションを削除
      3. コロンで区切って配列に格納
  5. 配列内の要素をワークシートのセルに書き込む

以下、解説をしていきますね。

ADODB.Streamオブジェクトの生成

14行目と15行目でADODB.Streamオブジェクトを生成してadoStという変数で取り扱えるようにしています。

これは決まり文句ですので、深く考えずにコピペでOKです。

ADODB.Streamオブジェクトのプロパティとメソッド

本プログラムでは、以下ADODB.Streamオブジェクトのプロパティまたはメソッドを使用しています。

Charset:Streamオブジェクトで取り扱う文字コードを指定。
Open:Streamオブジェクトを開く。
LoadFromFile:Streamオブジェクトに指定のファイルのデータを読み込む。
EOS:Streamの末尾かどうかを返す(End Of Stream)。末尾だとTrueを返す。
Readtext:Streamオブジェクトから指定した文字数のデータを読み取る。1行ずつならadReadLine。既定値はadReadAll。
Close:Streamオブジェクトを閉じる。

…どうでしょう?一つ一つはそれほど難しくないとは思うのですが。

Line Inputによる方法にはない概念のものはCharsetくらいだと思います。

あとEOSはご注意下さい。EOFではありませんので。

実行結果

上記プログラムを実行しますと

エクセルVBAでUTF-8のCSVを読み込んだ結果

このように無事にUTF-8のCSVファイルを読み込むことができました。

まとめ

以上、エクセルVBAでUTF-8のCSVファイルを読み込む方法についてお伝えしました。

ADODB.Streamオブジェクトですが、パッと見では難しいように思えますが、一個一個のプロパティやメソッドの意味さえわかってしまえばそれほど難しいものではないと思います。

別記事でエクセルVBAからCSVファイルを書き出す方法も書ければと思います。

どうぞお楽しみに!

連載目次:エクセルVBAで色々なCSVファイルを取り扱う

様々なソフトウェア間のデータ交換の際に一般的に多く活用されているCSVファイル。エクセルで取り込んで集計や加工することもたくさんあることと思います。このシリーズでは、CSVファイルをエクセルVBAで取り込むためのテクニックについてお伝えしています。
  1. CSVとは何か?そしてエクセルでCSVファイルを扱うときの注意点
  2. エクセルVBAでCSVを読み取る初心者向けの最も簡単なプログラム
  3. エクセルVBAでCSVデータをカンマで区切ってワークシートに取り込む方法
  4. エクセルVBAでCSV取込み~改行がされないパターンの対処法
  5. エクセルVBAでダブルクォーテーションで囲まれているCSVファイルを取り込む
  6. エクセルVBAでデータにカンマが含まれてしまっているCSVを取り込む
  7. 文字化けよさようなら!エクセルVBAでUTF-8のCSVを読み込む方法

コメント

  1. ぶひ より:

    エクセルエンドユーザーです。(単なる事務職)

    ここ最近 職場で大量データを扱うことが多くなり
    それに伴い扱うテキストファイル(CSVやTXTカンマ区切りのもの)に
    ダブルクォーテーションまじり かつUTF-8ものの対象が多くなり
    悩んでいたところでした。
    色々検索してさがしたのですが 中々納得したものがなく
    あえてメモ帳でANSIで保存したり(これだと変換できないものも)
    エクセル上で手動インポート対応で諦めていたところ ここにヒットしました
    非常にわかりやすい 私が求めていたことズバリで助かりました
    本当にありがとうございます。
    会社ですとWEB閲覧規制にひっかかってしまい・・・ 自宅からお礼です

    • ぶひさん

      コメントありがとうございます!
      お役に立てて光栄です。

      今後も頑張って更新して参りますので、ご活用いただければ幸いです。

  2. 一笑 より:

    お世話になります、大変勉強になります。
    Excel2010では「SubまたはFunctionが定義されていません。」 のコンパイルエラーがreplacecolonに出てしまい先に進めません。助けて戴ければ幸いです。

  3. 一笑 より:

    ご多忙のところ早速のご対応を戴きありがとうございます。Subプロシージャの後にFunctionプロシージャを並べてます一行目だけ文字化けで表示して後はなにも出て来ません。32bitExcel2010を使用しております。お騒がせして申し訳ありません。

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