CSV

HOME
CSV
CSVの読込は Power Query が万能

2024年11月26日 / 最終更新日時 : 2024年11月26日 alljapan CSV

CSVの読込は Power Query が万能

CSVの注意点

先頭の「0」が消える

これは「0落ち」と呼ばれていて、例えば元データが「09012345678」のような電話番号をExcelで開くと「9012345678」になってしまう現象です。

長い数字が指数表記になる

例えば「123456789012」と入力されていたとします。これをExcelで開くと「4.91235E+12」と表示されます。これは指数表記というもので、Excelは12桁以上の数字はこのように扱います。

住所欄が日付になる

宛先住所の「番地」欄に「2-3-4」と書かれていたとします。これをExcelで開くと「2002/3/4」と日付と解釈されてしまいます。

実例

これから説明するテスト用のCSVは、以下のデータを使用します。

テキスト表示すると、以下です。

このCSVファイルを、Excelで開くことを利用した以下のマクロを使用して、シートに入れます。

<CSV to Array> 「CSV」を「配列」へ。Excelで開く。

すると結果は、以下の通りとなります。

問題点

A2セルは、12桁の「999999999999」でしたが、指数表示なりました。これを、配列に入れて処理するのは、問題ないのですが、配列をExcelに戻して、CSVで保存すると、指数表示のままCSVに保存される問題があります。
A5セルは、「2-3-4」が年月日に変換されてました。
A6セルは、「0001234567」の頭の「0」がなくなりました。
A7セルは。「(3)」が、マイナス表示に変換されました。

これでは、不具合があるので、以下のマクロを使用して、シートに入れます。

<CSV to Sheet> 「CSV」を「シート」へ。複数の文字コート・区切り文字に対応。QueryTablesを使用。

すると結果は、以下の通りとなります。

A列の勝手な変換はなくなりまいたが、改行ありデータにて不具合があります。

これらの不具合に対応するため、Power Query を使用した結果が以下です。見出しの列名が重複した場合の対応として、2行目を見出しにしています。1行目を無視して、処理することにより、見出しの列名の重複にも対応しています。

ちなみに、見出しの列名が重複した場合、勝手に末尾に_1付くように列名が変わってしまいます。以下のように、C列とE列の列名が同じ「価格」の場合、E列の見出しは、「価格_1」に変換されます。

さまざまなCSV読込方法は、以下も参照にしています。ありがとうございます。

Power Query の使い方

まずは、手動でCSVファイルを読み込みます。

「テキストまたはCSVから」を選択

CSVファイルを選択して「インポート」

右下の「データ変換」を選択

左上の「詳細エディター」を選択

これを、以下に書き換えます。

let
    fileFullPath=Excel.CurrentWorkbook(){[Name="fileName"]}[Content]{0}[ファイルフルパス],
    ソース = Csv.Document(File.Contents(fileFullPath),[Delimiter=",", Encoding=932]) 
in     
    ソース

この中の {[Name=”fileName”]}[Content]{0}[ファイルフルパス] の「fileName」がテーブル名で、「ファイルフルパス」が見出しです。

右下の「完了」を選択

左上の「閉じて読み込む」を選択

「csvList.csv」ファイルを、Power Query で読み込むことができました。
これで、Power Query での作業は完了です。

テーブル作成

次は、テーブルを作成します。

以下の通り、テーブル名「fileName」、見出し「ファイルフルパス」、1列１行のテーブルを作成します。

以下のように、B3セルには、「fileName」という名前を付けておきます。これは、マクロで使用します。Power Query を更新することで、このファイル名「C:\TEMP\sample2.csv」に、CSVデータが更新されます。

マクロ

次に、以下のマクロを紹介します。このコードはPower Queryを使用して指定されたCSVファイルをExcelシートに読み込むためのマクロです。

'******
'* CSVファイルをシートに読み込むマクロ
'* Power Queryを使用して外部データをリフレッシュする
'* 引数:
'* fileName: フルパスのCSVファイル名
Private Sub csv_to_sheet_PowerQuery(ByVal fileName As String)
    On Error GoTo ErrorHandler  ' エラーが発生した場合、ErrorHandlerにジャンプ
    
    ' 【Step 1】指定されたCSVファイル名をExcelシートのセルに設定
    ' "fileName"という名前のセル範囲があらかじめ定義されている必要がある
    ' このセルはPower Queryでデータソースとして参照される設定になっている
    Range("fileName") = fileName  ' 引数で渡されたファイルパスをセルに代入

    ' 【Step 2】Power Queryのデータソースに関連するListObjectを取得
    ' "csvList"という名前のテーブルオブジェクトが対象シート（"csvList"シート）に存在している必要がある
    ' このテーブルオブジェクトはPower Queryの出力結果がリンクしているExcelテーブル
    Dim lo As ListObject
    Set lo = ThisWorkbook.Worksheets("csvList").ListObjects("csvList")  ' テーブルオブジェクトを取得

    ' 【Step 3】QueryTableをリフレッシュしてデータを更新
    ' ListObjectに関連付けられているQueryTableをリフレッシュすることで、
    ' Power Queryがデータを再取得して指定されたCSVファイルの内容を更新
    ' BackgroundQuery:=False でリフレッシュが完了するまで待機する設定
    lo.QueryTable.Refresh BackgroundQuery:=False  ' Power Queryのデータ更新を実行

    ' 【注意事項】
    ' 1. Power Queryを事前に設定し、データソースに「fileName」セルを参照するよう構成しておく必要がある。
    ' 2. テーブル名"csvList"およびワークシート名"csvList"は変更可能だが、コード内で一致させる必要がある。
    ' 3. "fileName"の定義が欠けている場合、このコードはエラーを発生させる。
    
    Exit Sub  ' 正常終了

ErrorHandler:
    MsgBox "エラーが発生しました: " & Err.Description, vbExclamation
End Sub

ブロック

コードのポイント解説

Range("fileName")の役割
- fileNameは名前付きセルで、CSVファイルのパスをPower Queryが認識できるようにするための変数的な役割を果たします。
- このセルはPower Queryのデータソース設定内で使用され、参照ファイルを動的に変更可能にしています。
ListObjectとQueryTable
- ListObjectはExcelのテーブルオブジェクトを表し、Power Queryの出力がリンクされているものです。
- このテーブルにはPower Queryで読み込んだデータが格納されます。
- QueryTable.RefreshでPower Queryに接続されたデータを再取得します。
BackgroundQuery:=Falseの意味
- 非同期（バックグラウンド）ではなく、リフレッシュ操作が完了するまで次の処理を待機します。
- 同期的に処理を行うことでデータの完全な更新が保証されます。
事前の設定が必要
- fileNameという名前のセルと、Power Queryがこのセルを参照するように構成されたクエリが必要です。
- csvListという名前のテーブルがワークシートに存在し、Power Queryの出力先として設定されている必要があります。