MacでExcelに読み込みたいCSVファイルのデータに改行が含まれているときの対処法

もしかしたらWindows環境では気にしなくてよいことかもしれません。CSVファイルをExcelで読み込むときに、データの区切り文字と文字列の引用符を指定することで、改行コードが含まれていても１つのデータコンテンツとしての括りを明示できるはずで、CSVファイルを単純にExcelで開くと正しく読み込んでくれた記憶があります。

今回直面したのは、Mac環境でExcelであるCSVファイルを読み込んだときに、18桁の数字データ（私のデータの場合、TwitterのTweet IDでした）が指数表示になるという現象でした。Excelのデフォルト機能のため、回避するのにテキストファイルのインポートを使ったのですが、データコンテンツに改行が含まれているレコードで、引用符で括られているにも関わらず、途中で別レコードとして処理されてしまいます。

CSVを直接開くと数値データが指数表示に変換されてしまう

データ途中で改行コードが効いてしまう

また、インポートの前提として、テキストデータの日本語文字コードをExcel for Macで読み込めるものに変換しておかないと文字化けを起こしてしまいます。

そこで、この２つの課題をクリアするのにテキストエディタを使って加工し、意図する形でのインポートができるようになりました。以下、行なった加工手順をご紹介します。

（本来なら、Excelに読み込んだときに元データと同様にセル内改行が入っているのがよいと思いますが、今回は半角スペースに置き換えました。Excel内で復元するには、識別テキストを残したままインポートし、VBAを使って戻すとよいと思います。）

この加工作業を行うためのテキストエディタに必要な機能は、

1. 日本語文字コードを変換できる
2. 正規表現による文字列置換ができる

の２点です。私が使ったテキストエディタは「CotEditor」でした。

１．文字コードをUTF-16にする

Excelに読み込みたいCSVファイル（またはタブ区切りなどのテキストファイル）をテキストエディタで開き、エンコーディングを「Unicode  (UTF-16)」に変換する

２．データ途中にある改行コードを削除する

この加工は、１回の処理ではできず、下記のように３つのステップで処理しました。

１）レコードの区切りになっている改行コードを別の識別テキストに置き換える
２）その後、残った改行コードを半角スペースに置き換える
３）１で置き換えた識別テキストを改行コードに置き換える

順番に説明します。

２−１．レコード区切り改行コードを識別テキストに置換する。

データコンテンツ中にある改行コードだけを半角スペースに置換するために、あらかじめ「置換したくない改行コード（つまりレコード区切りの改行コード）を別のものに変更」しておきます。私の場合は改行コードとその前後にあるはずの引用符を検索し、そのうち改行コードだけを「crlf」というテキストに置換しました。

改行コードを検索したり置換するには、通常モードの検索ではなく正規表現モードで実行する必要があります。

２−２．（残っている）改行コードを半角スペースに置換する。

２−１の処理を行なったことで、残っている改行コードはデータコンテンツ途中のものだけのはずになっています。そこで、今度は改行コードだけを検索対象にして、それを半角スペースに置換します。

２−３．識別テキストを改行コードに置換する。

最後に、もともとはレコード区切りとして入っていた改行コードを、識別テキストからに戻します。

以上の手順でCSVファイルを加工、保存し、それをExcelファイルのインポート機能にてテキストファイルとして読み込ことで、２つの課題をクリアしました。同じような状況の方の参考になりましたら。