今日を乗り切るExcel研究所

Excel に働かされていませんか

Excel で CSV ファイルを「0落ち」や「文字化け」なしで開きたい

今回は、Excel で CSV ファイルを開くと起きる「0落ち」や「文字化け」に対処するため、「クエリ エディター」を使う方法を紹介します。 また関連して、CSV ファイルを再読み込みするマクロを作成します。

f:id:shego:20180211154053p:plain

Excel の CSV 問題とは

Excel の CSV ファイルを読み込む時の昔からあるやっかいな問題として、一番よく遭遇するのはいわゆる「0落ち(ゼロ落ち)」と「文字化け」でしょう。

「0落ち」は、「00123」のような0詰め番号が数値の「123」になってしまう現象で、Excel がデータを数字列を数値に変換するために起こります。 同様に勝手に自動変換されるデータに「カッコ付き数字」、「住所の番地」、「全角数字」などがあります。 もっと言えば「セルの書式設定」の「表示形式」にあるパターンにマッチするデータ表現はどれも自動変換で壊される可能性があります。

「文字化け」はテキストエンコーディングが標準の「UTF-8」であるテキストファイルの文字を Excel が読み違えるために起こる現象です。 「UTF-8」というのは現在主流の国際標準規格ですが、Excel は(というより Windows は)、同じ UTF-8 でも「BOM 付き UTF-8」と言う特殊な方式を採用していて、逆に標準の UTF-8 ファイルを開くとわざわざ文字化けさせるのです。

UTF-8 の変換については別記事にもしました。

www.shegolab.jp

これらの問題は本当に余計で迷惑なことで、全国 Excel ユーザイライラ度ランキンクトップ3以内には入っていると思います。

Excel で CSV を読み込む方法

ネットで検索すると、すでに CSV 問題への対処方法を紹介した記事がたくさんあり、次の3つに整理できるようです。

  • 「テキスト ファイル ウィザード」を使って、エンコーディングや列のデータ形式を指定して取り込む方法
  • エンコーディング変換、各数字データの先頭にクオートを挿入するなど、CSV ファイルの内容を加工する方法
  • テキストエディタで CSV をいったん丸ごとコピーして、「文字列」に書式設定済みの Excel シートに貼り付けする方法

その具体的な操作手順は、ネット上に丁寧な説明が既にたくさんありますので、本記事では割愛させていただきます。

問題は、CSV ファイルを開いた「後」に、これらの不具合を修復する手段がない事です。 上記の方法はどれも CSV を開く前の対処法です。 CSV ファイルを開いて不具合に気付いたらそれはいったん閉じて、拡張子の変更・テキストの変換・データ形式の設定など、事前の「仕込み」の作業をしてから開き直す必要があります。

この手戻り感と、またしてもユーザを働かせようという魂胆にイライラが募ります。

クエリ エディターを使って CSV ファイルを取り込む

Excel 2016 以降では「クエリ エディター」という新しいツールが加わり、それを使って CSV ファイルを読み込むこともできます。しかも読み込んだ「後」からも対処の変更が可能です。

クエリ エディターとは

「クエリ エディター」というのは、Power Query という新しいデータ分析機能のデータ処理手続き(クエリ)を編集するためのツールです。Excel の別館を新築したような外観で、ボタンも多くパッと見難しそうですが、慣れるとウィザードより画面が広くて快適です。

Office 365 をサブスクリプション契約している方の Excel では、「データ」タブの「外部データの取り込み」が無くなっていて、代わりに居座っている「外部データの取得と変換」というのが実は Power Query のことです。 買い切り版 Office の Excel 2016 の場合「データ」タブの「取得と変換」というのがそれです。 Excel 2010 と 2013 には標準で Power Query はありませんがアドインとしてインストール可能です。

クエリエディターを使った CSV の取り込み機能は優秀です。

  • 先頭行の列見出しを自動判別します
  • データ型は列の複数行の内容をチェックして列単位で判定されます
  • テキストデータに折り返しの改行があっても壊れずに取り込まれます
  • 先頭が "ID" のファイルでも問題なし

しかし、そのクエリエディターをしても、相変わらず文字化けや意図しない0落ち・日付化等への対処は必要です。

【作業手順】

  1. クエリエディターで CSV ファイルを開きます
    • Office 365 Excel:「データ」タブ>「データの取得と変換」>「テキストまたは CSV から」
    • Office 2016 Excel:「データ」タブ>「取得と変換」>「新しいクエリ」>「ファイルから」>「CSV から」
    • CSV ファイルを選択して「インポート」ボタンを押します
    • クエリエディターが開き、CSV 内容が表示されます
  2. もし、日本語が文字化けしていたら、テキストエンコーディングを変更します
    • 右の「クエリの設定」パネルの「適用したステップ」リストの一番上にある「ソース」をダブルクリックします
    • 表示される「コンマ区切り値」ダイアログの「元のファイル」プルダウンで 「--なし--」 を選択し「OK」ボタンを押します
    • 文字化けが解消しています f:id:shego:20180204153442p:plain

      【追記】 Office 365 では上記と異なり、CSVファイルを開いた直後の確認画面で文字コードを変更できるようになっているようです。 もし確認画面でテキストが文字化けしていたら、「65001:Unicode (UTF-8)」というのをプルダウンから探して選択するか、あるいは「--なし--」を選択します。

  3. もし、0落ちや日付化されたデータがあったら、その列の型を「テキスト」に変更します
    • 右の「クエリの設定」パネルの「適用したステップ」リストの「変更された型」を選択しておきます
    • 自動変換をやめたい列(カラム)のグレーの列見出しを右クリックします
    • 表示されるコンテキストメニューから「型の変更」>「テキスト」を選択します
      • 上部ボタンメニューの「型の変更」からでも変更できます
    • 0落ちや日付け変換されていたデータが元のテキストに戻ります f:id:shego:20180211014437p:plain
  4. 「閉じて読み込む」ボタンを押して CSV データをワークシートに取り込みます
    • CSV のデータが色つきの「テーブル」として展開されます

こうして得られたシートは、単に CSV ファイルを「開いて」いるというのではなく、『「クエリ」を通して CSV ファイルに「接続」されている「テーブル」』 という状態になっていて、処理内容を「後から」でも変更できます。

注意点がいくつかあります。

  • クエリエディターでデータ形式を「テキスト」に変更した列でも、ワークシートでセル書式を見てみると「G/標準」のままという不思議な状態になっています。数値に戻らないよう、表示形式を「文字列」に変更しておくと安心です。

  • UTF-8 の CSV で「1行目(見出し行)に日本語が含まれていた場合」は上記操作でもエラーになります。 その場合、列見出し行とデータ型の自動判別を解除しておく必要があります。

    1. クエリ エディターで「クエリの設定」パネル>「適用したステップ」リストにある「昇格されたヘッダー数」と「変更された型」の項目を削除します
      • 項目名にマウスカーソルをあてると左に現れる×(バツ)マークをクリックすると削除されます

クエリの解除

しがらみのない素の CSV データに戻したい場合、下記の操作でクエリを解除できます。

  1. テーブルを選択
  2. スタイルをクリアします
    • 「テーブルツール/デザイン」タブ>「テーブルスタイル」>スクロールバー右下クリック>「クリア」
  3. テーブルを解除します
    • テーブルを右クリック>「テーブル」>「範囲に変換」
  4. クエリを削除します
    • 「ブック クエリ」>クエリを右クリック>削除

古い Excel は使えない?

筆者が使っている Excel 2013 でも、アドインで Power Query が使えるはずなのですが、なぜかインストールできませんでした。 Excel に見捨てられたらマクロで何とかするしかありません。



開いている CSV ファイルを読み直するマクロ

CSV ファイルを開いた「後」 に 読み直すマクロを作成しました。

普通に CSV ファイルを開いたシートで本マクロを実行すると、その CSV を読み直してデータを上書きします。

その際、日本語の文字化けは自動的に解消します(標準 UTF-8 のみ)。

0落ちや日付化などがあったら、マクロを実行する前にその列の表示形式を「文字列」などにあらかじめ変更しておくことで、不都合なデータ変換を回避できます。

ただ、読み込みがだいぶ遅いので、大きなファイルでの使用は避けてください。

Option Explicit

Sub CSVファイル読み直し()
    Dim csvFileName As String
    csvFileName = ActiveWorkbook.FullName
    
    If Not LCase(csvFileName) Like "*.csv" Then Beep: Exit Sub
    If LCase(csvFileName) Like "https*" Then
        MsgBox "クラウド環境には対応していません" & vbCrLf & csvFileName
        Exit Sub
    End If
    
    Dim saveSelection As Range
    Set saveSelection = Selection
    
On Error GoTo finish
    Application.ScreenUpdating = False
    
    setCsvMode
    pasteTextFile csvFileName
    Range("a1").Value = trimBom(Range("a1").Text)
    setReadOnly ActiveWorkbook
    
finish:
    saveSelection.Select
    Application.ScreenUpdating = True
    If ERR.Number <> 0 Then MsgBox ERR.Description & vbCrLf & csvFileName
End Sub

Private Sub pasteTextFile(filename As String)
    Dim cmd As String
    If isUtf8(filename) Then
        cmd = "cmd.exe /c chcp 65001 & clip < """ & filename & """"
    Else
        cmd = "cmd.exe /c clip < """ & filename & """"
    End If
    
    With CreateObject("Wscript.Shell")
        If .Run(cmd, 0, True) <> 0 Then Beep: Exit Sub
    End With
                
    Range("A1").Select
    ActiveSheet.Paste
    ActiveSheet.UsedRange.Rows.AutoFit
    
    clearClipboard
End Sub

Private Function isUtf8(txtFile As String) As Boolean
    Const PROBE_LEN = 1000
    
    Dim buff(PROBE_LEN - 1) As Byte
    Open txtFile For Binary Access Read As #1
        Get #1, , buff
    Close #1
    
    Dim wchars(PROBE_LEN * 2 - 1) As Byte
    Dim i As Integer
    For i = 0 To PROBE_LEN - 1
        wchars(i * 2) = buff(i)
    Next
        
    With CreateObject("VBScript.RegExp")
        .Pattern = "[\xE0-\xEF][\x80-\xBF][\x80-\xBF]"
        isUtf8 = .Test(wchars)
    End With
End Function

Private Sub setCsvMode()
    With ActiveSheet.UsedRange.Columns(1)
        .TextToColumns Comma:=True, _
            ConsecutiveDelimiter:=False, _
            TextQualifier:=xlTextQualifierDoubleQuote
    End With
End Sub

Private Function trimBom(txt As String)
    trimBom = Replace(txt, ChrW(&HFEFF), "", 1, 1, vbBinaryCompare)
End Function

Private Sub clearClipboard()
    Range("A1").Copy
    Application.CutCopyMode = False
End Sub

Private Sub setReadOnly(wbk As Workbook)
    If wbk.ReadOnly Then Exit Sub
    wbk.Saved = True
    wbk.ChangeFileAccess xlReadOnly
End Sub

【使い方】

  1. 上記の VBA プログラムをExcelマクロの標準モジュールにコピー&ペーストします
    • CSV ファイルとは別のワークブックにしたほうがいいでしょう。
  2. Excel で普通に CSV ファイルを開きます
    • 「ファイルを開く」から開くか、CSV ファイルをダブルクリックします
  3. Excel シートに CSV データが読み込まれます
  4. もし、テキストが文字化けしていても、そのままにして構いません
  5. もし、0落ちなど値の壊れているデータがあったらその列の表示形式を「文字列」に変更しておきます
    • 列を選択 >「セルの書式設定」>「表示形式」>「文字列」を選択
    • 数値を使わないなら、全セル範囲を選択して「文字列」に設定しても構いません
  6. 本マクロを実行します
  7. CSV データが読み直されます
    • 書式を変更したセルの値は元のままの文字列データになっています
    • 文字化けは解消しているはずです
    • 不用意な保存で元の CSV ファイルを上書きしないよう、「読み取り専用」になっています。

注意として、十分な検証ができなかったため、以下の CSV ファイルには対応していません。

  • OneDrive や SharePoint といったクラウド上にある CSV ファイル
  • 「外部データの取り込み」から取り込んだ CSV ファイル

まとめ

0落ちや文字化けといった Excel と CSV の問題はどうも仕様であり、今後も対応作業が必要になりそうです。

「テキスト ファイル ウィザード」はなくなりそうなので、今回、新しい「クエリ エディター」を使った取り込みを紹介しました。

まだ Power Query は MOS にも出ないしア〇バでも教えてくれませんが、今後 VLOOKUP やピボットテーブルよりも評価されるビジネススキルになりそうなので、どんなものかぐらいはチェックしておきたいところです。

クエリエディターについては Windows 7 上のパッケージ版 Excel 2016 で検証しました。 マクロは Window 10 の Excel 2013 環境で検証しました。

関連記事

www.shegolab.jp

www.shegolab.jp

www.shegolab.jp