シーゴの Excel 研究室

タイトル変更しました （旧称：今日を乗り切るExcel研究所）

トップ > データ-CSV > Excel で CSV ファイルを「０落ち」や「文字化け」なしで開きたい

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

データ-CSV

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

f:id:shego:20180211154053p:plain

Excel の CSV 問題とは

Excel で CSV ファイルを開くと、いろいろと問題が起きがちなのですが、一番よく遭遇するのはいわゆる「０落ち（ゼロ落ち）」と「文字化け」でしょう。

「０落ち」は、「00123」のような０詰め番号の 0 が無くなり、ただの「123」になってしまう現象で、Excel がデータを数字列を数値に変換するために起こります。 これは０詰だけの問題ではなく、ほかにも勝手に数値に変換されるデータに、カッコ付き数字（(123)）、金額（\123）、全角数字（１２３）、日付などがあります。 もっと言えば、「セルの書式設定」の「表示形式」にあるパターンに当てはまるデータはどれも数値変換で壊される可能性があります。

もう一つの「文字化け」は、日本語の文字がめちゃくちゃな読めない文字となって表示されることです。これは、CSVのテキストエンコーディングが、標準的な「UTF-8」となっているのに、 Excel が「シフトJIS」と読み違えるために起こる現象です。 UTF-8 の問題については別記事に取り上げました。

www.shegolab.jp

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

Excel で CSV を読み込む方法

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

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

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

これらの方法で CSV の大抵の問題は解決できるはずです。

ただ筆者が不満に思うのは、CSV ファイルを開いた「後」に、不具合を修復する手段がないことです。

上記の方法はどれも CSV を開く「前」の対処法です。

Excel で CSV ファイルを開いて不具合に気付いたら、開いたのをいったん閉じて、拡張子の変更・テキストの変換・データ形式の設定など、事前の修正作業をしてから、改めてCSV を開き直す必要があります。

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

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

いろいろ調べてみたところ、「クエリ エディター」というツールが使えそうです。

Excel 2016 以降で追加された「クエリ エディター」は、CSV ファイルも読み込むことができます。 しかも読み込んだ「後」からも問題への対処の変更が可能です。

そこでクエリ エディターを使って CSV ファイルを読み込んでみたいと思います。

クエリ エディターとは

「クエリ エディター」というのは、Excel に追加された 「Power Query（パワークエリ）」 というデータ分析機能のための、処理手続き（クエリ）を編集するためのツールです。

Power Query はリボンの「外部データの取得と変換」として使われる機能で、これまでの「外部データの取り込み」を置き換えるものです。 様々な情報元から外部データ取り込めるだけではなく、データ分析や加工も自在に行える強力な機能です。

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

Office 365 でExcel を利用している方は、 Excel 「データ」タブの「外部データの取り込み」がなく、代わりに「外部データの取得と変換」になったのに気が付かれたかもしれません。 実は名前だけでなく、中身の機能もほとんど Power Query に置き換わっています。 買い切り版 Office の Excel 2016 をご使用の場合、「データ」タブに「取得と変換」というのがいつの間にか追加されていると思います。それが Power Query です。 Excel 2010 と 2013 には標準で Power Query はありませんが、アドインとして追加することが可能です。

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

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

そのクエリエディターをしても、相変わらず文字化けや意図しない０落ち・日付化等への対処は必要ですが、それは開いた「後」に変更できます。

【作業手順】

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

こうして得られたシートは、単に CSV ファイルを「開いて」いるというのではなく、『「クエリ」を通して CSV ファイルに「接続」されている「テーブル」』 という状態になっています。 そのためデータ形式を変更後「更新」することでいつでも変更可能です。

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

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

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

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

クエリの解除

シート上の CSV データは、Power Query の「クエリ」に紐づいています。

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

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

古い Excel は使えない？

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

　

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

開いた CSV ファイルを、再読み込みするマクロを作成しました。

これで、CSVを開いて不具合が分かった「後」のタイミングで対処することが可能になります。

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

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

０落ちや日付化けなどがあったら、あらかじめその列の表示形式を「文字列」などに変更してからマクロを実行することで、正しく表示されるようになります。

本マクロは、再読み込みにだいぶ時間がかかるので、大きなファイルでの使用は避けてください。

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

【制限事項】

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

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

まとめ

「０落ち」や「日付化」ような Excel と CSV の問題はどうも Excel の仕様ということで、改善される見込みはなさそうです。

「文字化け」については、Windows レベルで UTF-8 への対応が進められているので、いずれ解消されるかもしれません。

従来の「テキスト ファイル ウィザード」は Power Query に取って代わられて将来的になくなるかもしれません。今回はあえて新しい Power Query の「クエリ エディター」を使った取り込みを詳しく紹介しました。

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

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

【追記】

Excel 2019 では画面周りの仕様がまた変わってしまっているようで、この記事の内容では古くて分かりにくいかもしれません。また別の話題の記事で Power Query について取り上げる予定ですので、そのときに再度解説したいと思います。

関連記事

www.shegolab.jp

www.shegolab.jp

www.shegolab.jp