【ExcelVBA】UTF-8形式のCSVファイルのデータをRecordsetで取得して文字化けせずにExcelのシートに貼り付ける方法について

この記事では、UTF-8形式のCSVファイルのデータをRecordsetで取得して文字化けせずにExcelのシートに貼り付ける方法についてご説明します。

【動画】UTF-8形式のCSVファイルのデータをRecordsetで取得して文字化けせずにExcelのシートに貼り付ける実際の動き

本題に入る前に、まずは次の動画をご覧ください。


UTF-8形式のCSVファイルのデータをRecordsetで取得して文字化けせずにExcelのシートに貼り付けています。

【前提】UTF-8形式のCSVファイルのデータをRecordsetで取得してExcelのシートに貼り付けると文字化けを起こす

ExcelのマクロがUTF-8形式のCSVファイルのデータをRecordsetで取得しExcelのシートに貼り付けようとすると文字化けします。

なぜ文字化けするのかというと、ExcelはCSVファイルのデータをShift-JISで開く仕様となっており、Shift-JIS以外のUTF-8形式のデータだと文字化けを起こすからです。

そこで、UTF-8形式のデータを文字化けせずに正常に読み込みたいのですが、マクロでは文字化けしないようにExcelのシートに貼り付ける方法がないんです。

ではRecordsetを使ってUTF-8形式のデータを正常にExcelのシートに貼り付けることは不可能なの?というと、一つ解決方法があります。

それは、「schema.ini」という外部のファイルを使う方法です。

【解決策】「schema.ini」でUTF-8形式のCSVファイルのデータを正常に読み込む

「schema.ini」というファイルを使えば、UTF-8形式のCSVファイルのデータをRecordsetで取得して文字化けせずにExcelのシートに貼り付けることができます。

「schema.ini」とはいったいどんなファイルなのかと言うと、テーブル定義情報を定義するファイルです。

補足

テーブル定義というと「CSVファイルのデータで話を進めているのになんでテーブル?データベースじゃないのに?」と思われるかもしれませんが、CSVファイルをデータベースに、CSVファイルのデータをデータベースのテーブルデータに置き換えてイメージしてみてください。

この「schema.ini」に、CSVファイル内のデータに関するスキーマ情報を記述します。

スキーマ情報とは何かというと、CSVファイル名やファイル形式、文字コード(Shift-JISやUTF-8など)といった情報(値)です。

今回は文字コードにUTF-8の設定値(65001)を設定します。

この文字コードにUTF-8の設定値(65001)を設定することで、UTF-8形式のCSVファイルのデータをRecordsetで取得してExcelのシートに正常に貼り付けることができるようになります。

ではどのように設定するのかというと、次のように「schema.ini」にスキーマ情報を記述します。(サンプル)

[data.csv]

;【ファイル形式】CSVファイル形式
Format=CSVDelimited

;【文字コード】UTF-8(65001)に設定
CharacterSet=65001

;【読み込む行数】0はファイル全体を対象
MaxScanRows=0

;【ヘッダ有無】ヘッダなしのCSVファイルはFALSE
ColNameHeader=FALSE
補足

スキーマ情報はVBA上で設定することができません。

UTF-8形式のCSVファイルのデータをRecordsetで取得する際、VBA上では文字コードの指定ができないので、「schema.ini」で定義しておきます。

UTF-8のCSVファイルのデータを正常にExcelのシートに貼り付けるCharacterSetの設定

7行目でCharacterSetの値を「65001」に設定しています。

;【文字コード】UTF-8(65001)に設定
CharacterSet=65001

「65001」はUTF-8の設定値で、この値をマクロが認識してUTF-8形式のデータを正常に(文字化けせずに)Excelのシートに貼り付けてくれます。

読み込むUTF-8のCSVファイル名の指定

読み込むUTF-8のCSVファイル名の指定を1行目で指定します。

[data.csv]

CSVファイル名の指定は、上記のコードのように角括弧で囲んで指定します。

「schema.ini」はどこに置くか

「schema.ini」はどこに置くかというと、読み込むUTF-8のCSVファイルと同じ場所に置きます。

「schema.ini」ってどうやって使うの?

「schema.ini」は、読み込むUTF-8のCSVファイルと同じ場所に置いておけば、勝手に「schema.ini」の中身をマクロが読みこんでくれます。

マクロを実行するだけで特に何も操作する必要はありません。

ここまでで説明した以下の2点を正しく行うことでマクロが「schema.ini」の中身を読み取って動作します。

  1. 「schema.ini」の設定(文字コードの設定、CSVファイル名の指定など)を正しく行い作成・保存する
  2. 読み込むUTF-8のCSVファイルと同じ場所に置くこと

「schema.ini」の設定・配置を正しく行いUTF-8形式のCSVファイルのデータをRecordsetで取得してExcelのシートに出力するマクロを実行すると、文字化けせずに正しくCSVファイルのデータがExcelのシートに出力されます。

作業の流れ

「schema.ini」を使ってUTF-8形式のCSVファイルのデータをRecordsetで取得してExcelのシートに出力するには次の流れで作業を行います。

STEP.1
【schema.ini】各設定を行った「schema.ini」を作成し、読み込むUTF-8のCSVファイルと同じ場所に置く
各設定を行った「schema.ini」を作成し、読み込むUTF-8のCSVファイルと同じ場所に置きます。
STEP.2
【Excelマクロ】Recordsetを使ってUTF-8のCSVファイルのデータをExcelのシートに出力する
Recordsetを使ってUTF-8のCSVファイルのデータをExcelのシートに出力します。

CSVファイルの例

今回使うテストデータのCSVファイルは次の通りです。

UTF-8形式のデータで月ごとの生徒の科目別点数データが存在しています。

「schema.ini」の例

サンプルで使う「schema.ini」は次の通りです。

UTF-8形式のCSVファイル「data.csv」を対象に、UTF-8形式のデータをExcelのシートに出力するよう設定されています。

ちなみに「schema.ini」はUTF-8形式のCSVファイルと同じ場所に置きます。

ただ同じ場所に置くだけで、置き場に関する設定は特に必要ありません。(Excelやマクロで何か設定する必要はありません。)

Excelファイルの例

今回は次のExcelファイルを用意しました。

マクロを実行すると、CSVファイルにある月(D列)や名前(E列)、各生徒の点数(F列からJ列)が出力されます。

マクロ実行後の結果を見てみると、文字化けせずに正しく月や名前の値がExcelのシートに出力されています。

セルの名前

配置されているセルの名前は次の通りです。

csvFilePath

CSVファイルが置いてある場所を入力します。

コードの例

Option Explicit

Private Sub btn_getDirFileList_Click()
    
    Dim adoCON      As New ADODB.Connection     'Connection用変数
    Dim rs          As ADODB.Recordset          'レコードセット用変数
    Dim sqlStr      As String                   'SQL文用変数
    Dim csvFilePath As String                   'CSVファイルの在り処
    
    Const csvFileNM     As String = "data.csv"  'CSVファイル
    
    'Recordsetオブジェクトのインスタンスを生成する
    Set rs = New ADODB.Recordset
    
    'CSVファイルの在り処を取得する
    csvFilePath = Worksheets("top").Range("csvFilePath").Value
    
    'CSVへのコネクション
    With adoCON

        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Extended Properties") = "Text;HDR=No;FMT=Delimited"
        
        'コネクションを開く
        .Open csvFilePath & "\"

    End With
  
    'データを取得するSQL文を作成する
    sqlStr = "select"
    sqlStr = sqlStr & " *"
    sqlStr = sqlStr & " from"
    sqlStr = sqlStr & " [" & csvFileNM & "]"
  
    'SQLを実行しレコードセットを取得する
    Set rs = adoCON.Execute(sqlStr)
        
    'データをシート「top」に貼り付ける
    Worksheets("top").Range("D2").CopyFromRecordset rs
    
    '後処理
    adoCON.Close
    Set rs = Nothing
    Set adoCON = Nothing
    
End Sub

コードの解説

注目すべきコード①

最初に見て頂きたいのは16行目から27行目です。

    'CSVファイルの在り処を取得する
    csvFilePath = Worksheets("top").Range("csvFilePath").Value
    
    'CSVへのコネクション
    With adoCON

        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Extended Properties") = "Text;HDR=No;FMT=Delimited"
        
        'コネクションを開く
        .Open csvFilePath & "\"

    End With

以上のコードは、CSVファイルの在り処を元に、マクロがデータソース接続情報を取得して接続するコードです。

以上のコードを実行することで、CSVファイルに接続する準備が整いました。

ポイントは、CSVファイル名を以上のコードで明記するのではなく、CSVファイルの置き場を指定(Openメソッドに指定)しているところです。

CSVファイルを参照するコードはSelect文のFROM句で説明しています。

注目すべきコード②

次に見て頂きたいのは30行目から33行目です。

    'データを取得するSQL文を作成する
    sqlStr = "select"
    sqlStr = sqlStr & " *"
    sqlStr = sqlStr & " from"
    sqlStr = sqlStr & " [" & csvFileNM & "]"

以上のコードは、Select文を生成するコードです。

フィールド名

31行目ではフィールド名を指定しています。

今回は列全てのデータが欲しいので「*」を指定しています。

FROM句

33行目ではCSVファイルの名前を指定しています。

データベースの場合、SQLのSelect文のFROM句にはテーブル名を指定しますが、CSVファイルの場合はCSVファイル名をFROM句に指定します。

参考

このCSVファイル名は、フルパスのファイル名ではなくファイル名自体を指定します。
例えばCSVファイル「data.csv」が「C:¥work¥test」の配下にある場合、「C:¥work¥test¥data.csv」と指定するのではなく、「data.csv」のようにファイル名を指定します。
CSVファイルのパスは16行目と25行目で指定しています。

    'CSVファイルの在り処を取得する
    csvFilePath = Worksheets("top").Range("csvFilePath").Value
        'コネクションを開く
        .Open csvFilePath & "\"

実際のSQL文(例)

コードだけではSelect文が分かりづらいと思うので、Select文の例を以下にお見せします。

select
    *
from
    [data.csv]

注目すべきコード③

次に見て頂きたいのは36行目から39行目です。

    'SQLを実行しレコードセットを取得する
    Set rs = adoCON.Execute(sqlStr)
        
    'データをシート「top」に貼り付ける
    Worksheets("top").Range("D2").CopyFromRecordset rs

以上はSelect文を実行し、CSVファイルから検索・抽出したデータをExcelのシートに貼り付けるコードです。

36行目では、Executeの引数にSelect文を指定してExecuteを呼び出すことでSelect文が実行されます。

抽出されたデータはRecordsetから取得することができます。

39行目で、Recordsetからデータを取り出してExcelのシートに貼り付けています。

動作確認

マクロを実行した実行結果は、本記事内のExcelファイルの例の内容をご覧ください。

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

  1. Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、Excelのマクロのコードの5行目の「ADODB.Connection」と6行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。

    Dim adoCON      As New ADODB.Connection     'Connection用変数
    Dim rs          As ADODB.Recordset          'レコードセット用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

最後に

本記事では、UTF-8形式のCSVファイルのデータをRecordsetで取得して文字化けせずにExcelのシートに貼り付ける方法についてご説明しました。

UTF-8形式のCSVファイルのデータをExcelのシートに貼り付けるのにRecordsetを使いたい場合は、「schema.ini」という外部のファイルを用意することで文字化けせずにExcelのシートに貼り付けることができます。

Recordsetを使いたい時は本記事を参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→ TechAcademyの「1週間 無料体験」はこちら