CSVファイルでExcelで開いたら表示がおかしい出る場合、文字列で開く!

 

CSVファイルををExcelでそのまま開くことによる問題

エクセル

楽天市場の商品情報CSVなどCSVファイルをExcelで開いた時

・セルの中身が#NAMEになる

・電話番号などの数列の一番最初の0が消える

などといった症状に遭われたことがあるかと思います。

今回はなぜそのようなことが起こるのか、そして表示やデータの化けを避ける方法について解説します。

Excelが読み込み時に余計な処理をしている

CSVファイルは内容によってはExcelで読み込むと中身が書き変わってしまう。

ExcelでCSVファイルを開いた時の例

このように、実際の中身とは別に#NAMEと表示されてしまったり、数列の先頭のゼロが消えてしまったりします。

メモ帳で開くとわかるのですが、Excelで開いた際に実際の状態からかなり変更されてしまっています。

ExcelでCSVを表示崩れ・変化させずに読み込む_実際のCSVファイルの中身

冒頭のCSVファイルのもともとの状態

こうしてみると、CSV列の『 -csv 』の頭には勝手にイコールがついて『 =-csv 』にされていたり、電話番号列の09012345678の最初の0が消されているのがわかります。

(ちなみに2列目の数列はセルの横幅が狭いために表示ができていないだけで、セル横幅を調整してやるときちんと表示されるので、実は問題ありません。)

実はこれ全部Excelによる悪さ。

試しにCSVファイルをメモ帳などで開いてみるとわかるのですが、実際にはデータ上ではそのようなことにはなっていません。

Excelはデータを読み込んで表示する際、全てにExcel特有の処理をかけています。

例えば数列の頭の0が消える現象は、「 ゼロから始まる数値はありえない 」というExcelの信念(仕様)のもと最初のゼロを消してしまうのです。

CSVをいじる際にはなんとも迷惑な話ですが、Excelとしては職務(仕様)に忠実に働いているだけのことです。

解決方法 CSVファイルをデータ読み込みより読み込む

Excel上でセルの書式設定が『 文字列 』になっているセルであれば、セル内に先頭が -(半角ハイフン / マイナス)だろうが0になっている数列を打ち込もうが問題なく表示されます。

しかし、普通にExcelでCSVファイルを開くと、読み込んだ時点ですでに余計な処理をした後。

データとしては既に改ざんされたあとなので、あとから書式設定を文字列に設定しても意味がありません。

冒頭の例でいえば、既に中身が書き替えられているため文字列にしたところで『 =-csv 』『 9012345678 』の文字列になるだけで、『 =-csv 』や『 09012345678 』に戻るわけではないのです。

なので、余計な処理をかけないようCSVファイルを開くと同時に書式設定を変更できるデータ読み込み機能を使います。

Excelのデータ読み込みによるCSVファイルの文字列変換

ExcelでCSVを表示崩れ・変化させずに読み込む方法_データタブより外部データを読み込み_テキストからデータを読み込む

画像はExcel2013バージョンでの場合

まず、Excelで何もファイルを開いていないまっさらな空白のブック&シートを用意します。

そこから

データタブ → 外部データの読み込み → テキストファイル

の順でクリック。

スクリーンショット 2017-06-01 20.17.11

これでテキストファイルウィザード画面が開きます。

楽天やYahoo!ショッピング、AMAZONといったなどの商品用CSVや顧客情報CSVなどの場合、最初から選択されている『 カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ 』のまま、次へをクリックします。

ExcelでCSVを表示崩れ・変化させずに読み込む方法_区切り文字をカンマにチェック

するとテキストファイルウィザードの2/3へ移動します。

これはCSVファイルのセル列の区切り文字を設定する画面。

楽天RMSからダウンロードしたCSVファイルの場合はカンマで区切られているので、カンマにチェックをつけます。

下のプレビュー画面で各セルの間に壁ができ、区切り文字が設定されたのがおわかりになるかと思います。

このようにカンマなど特定の文字を区切りとして設定することで、セルを分けることができます。

ちなみにAMAZONの場合はカンマ区切りのCSV(カンマセパレートバリュース)ではなくタブ区切りのテキストファイルであるTSV(タブセパレートバリュース)。

なので、ここではカンマではなくタブにチェックを入れると区切りがうまく設定できます。

区切りがうまく設定できたら、次へをクリック。

ExcelでCSVを表示崩れ・変化させずに読み込む方法_セルの書式、データ形式を選ぶ画面

いよいよCSVを崩さずセルの中身を書き替えられずに開く方法最後の手順です。

ここではCSVファイルを開く際に各列の書式設定をどうするかを設定することができます。

そう、ここが一番大事なポイントで、全列を文字列に設定して開くことでExcelにデータを書き替えられずに済むのです。

黒くなっている部分が現在選択している列。

この状態で文字列や日付などに変更が可能です。

もちろん今回は文字列に変更するのですが、一列ずつやるのは面倒。

ExcelでCSVを表示崩れ・変化させずに読み込む_全体を選択し、データ形式(セル書式)文字列指定にする

Shiftキーを押しながらクリックで範囲指定ができる

Shiftキーを押しながらクリックをすると範囲選択ができます。

Shiftキーによる範囲選択は 始点をクリック → 終点をShiftを押しながらクリック で間も含めて全部選択してくれる便利機能。

最初に一番左の列が指定されていますので、一番右端の列まで一気に範囲指定し、全列(全セル)の書式設定を文字列に指定しましょう。

全て文字列にし終わったら完了ボタンをクリックです。

ちなみにテキストファイルウィザードの3つの画面は戻ると次へボタンで自由に行き来し、設定をやり直すことが可能です。

ExcelでCSVを表示崩れ・変化させずに読み込む_新しくファイルを出している場合は既存の~でOk

最後に現在のExcelブックに読み込んだデータを指し込みか、完全に新しいブックに読み込んで表示するかを選択できます。

今回の手順では新しいブックからの紹介していますので、既存のワークシートのままでOKをクリックすれば大丈夫です。

ExcelでCSVを表示崩れ・変化させずに読み込む方法_手順完了

CSVのデータ内容、表示が崩れないよう文字列での読み込みができた!

これで読み込みは完了。

ご覧の通り、-から始まるセルも0から始まる数列も全て文字列として扱われ、表示崩れや中身の書き換えがなされていない状態でExcelで読み込むことができました。

注意点1 CSVではなくExcelブックとして扱われている

データ読み込みの方法で開いた場合、CSVファイルそのものを開いたのではなくCSVファイルよりデータを読みとってきた全く新しいファイルとなっています。

ファイルを保存しようとすると名前を付けて保存状態になるので、CSVでアップロードしなければならない場合は保存時にファイルの種類をCSV(カンマ区切り)に指定しましょう。

ExcelでCSVを表示崩れ・変化させずに読み込む方法_ファイルとしてはExcelファイルなので、CSVで保存

CSVの場合はファイルの種類をCSV(カンマ区切り)で保存する

なお、楽天市場やYahoo!ショッピングではカンマ区切りCSVですが、AMAZONセラーセントラルにアップロードするファイルの場合はテキスト(タブ区切り)を選択します。

AMAZONはCSVではなくTSV(タブセパレートバリュース)を使う仕様になっているためです。

ちなみに今回のやり方では文字列として読み込んでいますが、その後CSVやTSVで保存したファイルをExcelで再び開くと、セル書式は全て標準に戻ってしまいます。

これは、CSVやTSV規格はセル書式を保存しないため。

その場合、再度表示崩れやデータの書き換えなく開くためにはもう一度データ読み込みより開く必要があります。

もし今後もそのCSVファイルを使う可能性があるのであれば、一旦xlsやxlsxなどのExcelブックとして保存しておくことをオススメします。

そして編集後にアップロード用ファイルとしてCSVで吐き出す、という形をとれば全列全セルが文字列のままで保存できるので、そのままExcelで開いても崩れなどがない状態にすることができます。

CSVファイルの全セル書式設定を文字列で開く方法まとめ

・CSVファイルをExcelでそのまま開くと先頭の0が消える、セルの中身が書き換えれるなどの現象が起きてしまい、エラーの原因になる可能性がある

・Excelのデータ読み込み機能を使うことで全セルを文字列とした状態で開き書き換えを回避することができる

ただし、この方法では時折CSVの列や行が大きく崩れてしまう場合があります。

(なぜかそのまま開くと列や行は崩れないのに)

そうなると考えられる可能性、要因がいくつもあるため回避は容易ではありません。

その場合はExcelを使うことを諦め、他のソフトを使うと良いでしょう。

もともとCSVといったデータベースに近いファイルはマイクロソフトのofficeソフトの中ではExcelではなくAccessの方が向いています。

ただ、サーバー屋さんやシステム屋さんといった専門的な職種出もない限りAccessまで導入している方は少ないことでしょう。

そこで、管理人としてはCSV専用のフリーソフトとしてcassavaをオススメしています。

その辺りは他の記事でご紹介していますので、下記関連記事よりご覧ください。

  • このエントリーをはてなブックマークに追加
  • Pocket

この記事へのコメントはこちら

メールアドレスは公開されませんのでご安心ください。
また、* が付いている欄は必須項目となりますので、必ずご記入をお願いします。

内容に問題なければ、下記の「コメント送信」ボタンを押してください。