CSVファイルの読み込み（VBA研究会議事録_22）

VBA研究会で論じられた内容を記録する議事録の22本目（2023/07/15開催分）です。前回の議事録はコチラになります。

hirocom777.hatenadiary.org

前回はセルの名前定義に取り組みました。セルの名前定義は便利な機能なのですが、いろいろなトラブルの原因になったりします。使用する際の注意点などについてご紹介しました。

今回はCSVファイルの読み込みです。

CSVファイルの読み込み

それでは今回のお題です。以下の様な内容のCSVファイルがあります。

"店舗ID","店舗名","出店形態","販売員No","販売員","売上高"
"0001","札幌A ","路面","0001-01","青山　裕也","16,311"
"0001","札幌Ａ","路面　","0001-03","田口　美紀","99,606"
"0002","札幌 B ","ｼｮｯﾋﾟﾝｸﾞ ｾﾝﾀｰ","0002-01","金本　沙月","15,722"
"0002","札幌B","ショッピング センター","0002-02","棚橋　薫","93,996"
"1001","仙台A","路 面","1001-01","斉藤  隆","90,099"
"1001","仙台A","路面","1001-02","向坂  朱美","59,256"
"1002","仙台B","ショッピングセンター","1002-02","浅沼　竜弥","36,739"
"2001","宇都宮","路面","2001-01","加藤　美智","42,238"
"2001","宇都宮","路面","2001-02","上原　由利","26,635"

このファイルをExcelで読み込むと以下のようになります。

いろいろ変換された形で読み込まれてしまいました。これを以下のように修正して読み込みたいです。

店舗ID

IDは0を含めた4桁の文字列を維持する
店舗名

地域名（全角）＋半角英字（大文字）とし余分な空白は削除する
出店形態

出店形態は全角文字で統一し、空白はすべて削除
販売員No

店舗ID + 半角ハイフン「-」+ 2桁数字
販売員

姓と名の間は全角1文字の空白で統一し、前後の空白は削除
売上高

通貨形式に修正

QueryTableによる読み込み

テキストデータであるCSVファイルをVBAで読み込む方法はいろいろあるのですが、今回はQueryTableによる読み込みをご紹介します。

msdn.microsoft.com

QueryTableとは、Excelに外部データベースを読み込むために用意されているオブジェクトです。以前のExcelでは、「データタブ」→「外部データの取り込み」でデータを取り込む機能がありました。この機能を実現するのがQueryTableオブジェクトです。現在ではこの機能はパワークエリにとって代わられたようですが、QueryTableオブジェクト自体は使用可能です。

以下はQueryTableオブジェクトを使用した、CSVファイルを読み込むコードです。標準モジュールに記述します。

Private Sub ReadCsvByQueryTable()
    Dim readData As QueryTable
    
    '読込CSVファイル名の指定
    Dim fileName As String
    fileName = ThisWorkbook.Path & "\22_CSVファイルの読み込み.csv"
    
    '表示先指定
    With Sheets("Sheet1")
        .Cells.Clear
        Set readData = .QueryTables.Add(Connection:="TEXT;" & fileName, _
        Destination:=.Range("A1"))
    End With
    
    '読込形式の指定
    With readData
        .TextFileCommaDelimiter = True 'カンマ区切りの指定
        .TextFileParseType = xlDelimited  '区切り文字の形式
        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2) '文字列に指定
        .TextFileStartRow = 1 '開始行の指定
        .TextFilePlatform = 932 '文字コード指定（Shift_JIS）
        .RefreshStyle = xlOverwriteCells '上書き
        .Refresh 'シートに表示
        .Delete '接続を削除
    End With
End Sub

Excelファイルと同じフォルダーにあるCSVファイルを、思惟したシートに読み込んでいます。ポイントとしては、すべての列を文字列形式で読み込んでいるところです。CSVファイルはテキストデータですので、これで読み込み時の不要な変換を防ぐことができます。

このコードを実行した結果は以下のようになります。店舗ID、販売員Noの不要な変換はなくなりました。

表記ゆれ、表示形式の修正

表記ゆれ、表示形式の修正ですが、読み込み後に以下のコードを実行します。

    '表記ゆれ修正
    Dim i As Long
    For i = 2 To Sheets("Sheet1").UsedRange.Rows.Count
      With Sheets("Sheet1").UsedRange.Rows(i)
        '店舗名　全角+半角英字大文字
        .Cells(2).Value = Replace(StrConv(.Cells(2).Value, vbNarrow), " ", "")
        '出店形態 全角文字で統一し、空白はすべて削除
        .Cells(3).Value = Replace(StrConv(.Cells(3).Value, vbWide), "　", "")
        '販売員 姓と名の間は全角１文字の空白で統一
        .Cells(5).Value = Trim(Replace(StrConv(.Cells(5).Value, vbWide), "　　", "　"))
        '売上高は通貨形式
        .Cells(6).NumberFormatLocal = "\#,##0;\-#,##0"
        .Cells(6).Value = .Cells(6).Value
      End With
    Next

Replace、StrConv、Trimを使用して、表記ゆれを修正しています。売上高の表示形式変更ですが、セルの書式設定を実行し後に値を再入力すれば実現可能です。実行結果は以下のようになりました。