CSVデータの扱い(Excelでの取り込み方法他)
CSVデータの扱いについてご案内します。
データベースを扱う際にはCSVファイルを触ることが良くあります。
以下知っている方には言わずもがなのことですが、誰かに引き継いだり、教えたりということになると、
色々と説明する必要が出てきます。
そういった時に以下を見てね!といった風に役立てていただければと思います。
2020/10/31加筆しました。接続の削除(すみません。リンクの解除っていってました)などについても書いてます。
CSVデータの扱い
1.知っておきたいこと
(1)CSVとはどんなもの
CSVは「Comma Separated Value(=カンマ(,)で区切った値)」の頭文字。
ファイルの拡張子は「.csv」です。
互換性が高いので、システム間のデータのやり取り(システムからのダウンロードやアップロード)での使用頻度が高いです。
(2)実例
ID,氏名,所属
111,日本太郎,総務部
222,世界花子,経理部
他に各フィールドを”(ダブルクォーテーション)で囲む以下のようなパターンもあります。
“111”,”日本太郎”,”総務部”
(3)Excelの特性とCSVとの相性
Excelは、元々表計算ソフトです。
ですから、「数字らしきもの」を見ると、とにかく数字として解釈したがります。
(これを仮に「Excelの数値の呪い」とします。)
CSVファイルを開くときも、このExcelの数字の呪いが発動します。
本当は文字型でも、強制的に数字として読み込んでしまい、これをそのまま保存すると(ファイル形式をExcel(.xlsx)にせず、CSVのままでも)
例えば、頭の0を落としたりします。(頭の0が落ちなくても、本来文字型の数字も 数値型にしてしまいます。)
例をあげると、こういうことがおきます。
CSVファイル
社員番号,姓名,申請NO
01234,日本太郎,2019122012170510123456
Excelでの読み込み
2.やってはダメ。CSVの取扱い
以下のいずれも、「やってはダメ」な開き方です。
×(1)エクスプローラからそのまま開く
→開くだけならいいんです、保存さえしなければ。
私も、どんなデータか見たい時に「開く」のだけは良くやります。
(その時は「ファイルは上書き保存しないぞ」「保存しないぞ」と自分に言い聞かせ続けます。)
何度もいいますが、「CSV形式で保存」しても、数値の呪いはかかってしまい、「0」は落ちてしまいますので。
×(2)ダウンロードするときに「開く」をクリックする
CSVファイルは、Windowsの初期設定ではExcelと関連付けられているため、
エクスプローラやWeb上でCSVを開くと、Excelが起動した上でファイルを開きます。
そこで「Excelの数値の呪い」が発動です。
これを防ぐには、4の方法で開く必要があります。
ちなみに数値の呪いによる「頭の「0」落ち」は、桁数が短ければ、TEXT関数で文字型にすることはできますが、
「桁数が長い場合」は修復不可能です。
3.事前にやっておいた方がよいこと
今後CSVを扱うなら、以下をやっておきましょう。
(1)エクスプローラ上で拡張子を表示する設定に変えておく
ファイルがCSVなのかExcelなのかは、わかりにくいので、CSVファイルを扱う人は、「登録されている拡張子は表示しない」をOffにしておきましょう。
4.CSVファイルを開くには
(1)Excelを起動してからウィザードを使う
やっと本題です。
CSVファイルはこんな風に開きます。
①Excelの新規ファイルを開く
②データ/データの取得と変更
③ファイルから/テキストまたはCSVから
Excelのバージョンによっては、「ファイルから」が無いケースもあります。
④CSVファイルを選択してインポートをクリック
⑤ファイルウィザードに従って、文字列指定する。
・カンマやタブで区切られたデータ/次へ
⑥Shiftを押しながら、全ての列を選択して、文字列に指定する(一気に文字列を選択・指定するのがポイント)
⑦CSV取り込み開始位置を指定してOK
⑧完了!
CSVデータが壊れることなく、取り込まれた
(この場合、A列もD列も文字列となっている。)
⑨拡張子をExcelにして保存
5.(ちなみに)Office365での対応
ちなみにOffice365では、上記取り込み方法では、ウィザードが起動しません。
PowerQueryが起動するので、型を設定したりすれば以下のように取り込めますが、
従来通りの方法で取り込むなら、Excelのオプション設定で、従来のウィザードの呼び出しを可能にする必要があります。
■設定
Excelのオプション/データ/レガシ データインポートウィザードの表示/テキストから(レガシ)に「v」をつける
■読み込み
データ/データの取得/従来のウィザード/テキストから(レガシ)/テキストを読み込むとウィザードが起動する
6.留意点や他の開き方
(1)接続について
CSVファイルに接続した状態になっているので、ファイルを開くとメッセージが出ます。
再接続が不要なら、接続を削除します。
(2)「4.CSVファイルを開くには」の方法以外にも
CSVファイルの拡張子を変更して(例:csv→dat にする例が多いです)
Excelを起動して、ファイルを開く方法もあります。
(ウィザードが立ち上がるので 4.の手順と同様に文字型に変換します)
7.CSVデータの修正方法
(1)CSVデータの一部を修正するときは「メモ帳」等のテキストエディタを使う
ほんの一部を修正するときは「メモ帳」を使います。
ファイルを右クリックして、「プログラムから開く」で「メモ帳」を選択して開く。
,カンマや”ダブルクォーテーションや改行マークを消さないように気をつける
※もちろんファイルの中身を確かめるときも使えますが、中身を確かめる時は「ダブルクリックしてExcelで開いた」方がわかりやすいです。
ただし、「上書き保存しないように、肝に銘じながら」というのを「習慣づけて」いく必要があります。
(2)大幅に修正するなら
ウィザードでCSVファイルを変換してから
「4.CSVファイルを開くには/(1)Excelを起動してからウィザードを使う」
Excel上で修正した上で、CSVに保存します。
保存の際に、拡張子を「CSV」にすれば大丈夫です。
8.ExcelからのCSVファイルの作り方
Excelファイルを保存するときに、拡張子をCSVに選択すれば、CSVファイルが出来ます。
ただし、Excelと異なり、複数のシート・様々な書式設定は保存できないので、
いったんExcelを上書き保存してから、CSV保存する必要があります。
9.その他
①CSVエディタについて
10年以上前に「CSVエディタ」を使っていましたが、シェアウェアという以外、もう覚えていません。
複数の関係業者から受領したCSVデータを統合して、システムにアップロードしていました。
当然のことながら、メモ帳で編集するより、ずっと「便利!」です。
フリーウェアがインストールできる環境なら、インストールをオススメします。
(どれがいいかは、ググってください。Cassava Editorがおすすめらしい気配です。)
②Accessでの読み込みについて
初回の取り込みはウィザードを使いますが、
自動化するときは、インポート定義」をします。
やり方は Accessのノウハウ(フォーム上でのインポート設定) をご参照ください。
10.その他のExcelのノウハウ
Excelに取り込んだ後の使えるノウハウもいくつか掲載しました。
興味のある方はどうぞ。
【Excel】重複がある際に適切なデータを選ぶ(COUNTIF関数を使う)
【Excel】複数の列要素(数値と文字両方あり)があるクロス集計表を作る(行と列の組み合わせでVLOOKUPのKEYを作る)
【Excel】「同じ部の副部長2名を1行で表示」するようなクロス集計表を作る(COUNTIFで連番列を作り、VLOOKUPのKEYに使う)
PowerQuery(Excel2016以降)でのCSVの開き方も記載しました。