改行を含んだCSVをExcelでインポートしたり開いたりすると途中から列がずれて崩れることがありますね。そんなお悩みを一発解決する方法です。操作中に見つけましたが、当初、解決法が検索して全然出てこなくて苦労したので、共有します。
秀丸エディタもマクロも不要です。取得と変換のPowerQueryエディターの中にちゃんと設定項目があるのですが、場所が分かりにくいのです。
エクスポートするシステム側で制御してもらえれば良いのですが、そうもいかないこともありますよね。複雑なコードを書くことなくメニューから操作できるのでノンプログラマーにもおすすめです。
エラーの原因はこれ
元データに改行コードが入っていると列のずれが起こってしまいます。取り込み時に起きるエラーはは列ずれによる型変換エラーであることが多いです。
ずれると型が変換できないことがあります。詳しくはこちらのページで紹介しています。
ファイル名を指定して取り込むのとフォルダーから取り込む場合では、設定箇所が異なります。
「テキストまたはCSVから」読み込む場合
メニューバーの「データ」→「テキストまたはCSVから」をクリックします。（無い場合は データ→新しいクエリ→ファイルから→csvから）
読み込むCSVを選択して「インポート」をクリックします。（CSVを選択すると開くがインポートに変わります）
データの変換をクリックします。
パワークエリエディタが起動したら「適用したステップ」の「ソース」をダブルクリックします。
【重要】これですよー！
「コンマ区切り値」のウインドウが開いたら「改行」の下のプルダウンから「引用符で囲まれた改行を無視」を選択して「ＯＫ」をクリックします。
PowerQueryエディターに戻ったら、左上の「閉じて読み込む」をクリックします。
ワークシートにCSVが読み込まれました。
テキストまたはCSVから読み込む場合の操作は以上です。
「フォルダーから」読み込む場合
メニューバーの「データ」→「ファイルから」→「フォルダから」をクリックします。
「参照」をクリックしてCSVを保存するフォルダーを選択し「ＯＫ」をクリックします。
「結合」→「結合および読み込み」をクリックします。
「Fileの結合」で「OK」をクリックします。
「サンプルファイルの変換」をクリックして、右側の「適用したステップ」から「ソース」をダブルクリックします。
「コンマ区切り値」のウインドウが開いたら「改行」の下のプルダウンから「引用符で囲まれた改行を無視」を選択して「ＯＫ」をクリックします。
PowerQueryエディターに戻ったら、左上の「閉じて読み込む」をクリックします。
ワークシートにCSVが読み込まれました。
フォルダーから読み込む場合の操作は以上です。
インポートした後でPowerQueryエディターを開くには
インポートした後で既にワークシートにデータが展開されている場合は、以下の手順でPowerQueryエディターを開きます。
メニューバーの「データ」→「データの取得」→「PowerQueryエディターの起動」をクリックします。
後は上で説明した手順でステップの項目をダブルクリックしましょう。
一度作っておけばワンボタンで一括更新できるのが強み
「取得と変換」の「フォルダーから」の機能は驚異的に便利です。ファイル名が変わろうが、ファイルの数が変わろうがお構いなしにファイルを結合（ＵＮＩＯＮ結合）してインポートしてくれます。
設定さえしておけば「すべて更新」のワンボタンでデータの取り込みから、集計まで実現できるのでとても便利です。私もフル活用しています。
取得と変換は金型のようなものです。手順は面倒なように見えますが、一度つくってしまえば、次からは元データを差し替えて「すべて更新」ボタンで一括更新。さらにこの表を基にピボットテーブルを作っておけば、集計表までできます。
例えばシステムの日々変わるデータを毎朝ダウンロードして、所定のフォルダに保存すれば、売り上げ金額や、件数、進捗具合などが、ワンボタンで更新できるのです。私は10ファイルぐらい連続で起動も更新もVBAで自動化していて、毎朝１時間ぐらい手ぶら状態です。爆速の上に正確。その間にメールチェックや業務連絡、ポットのお湯の補充までできています。
例えばこんな感じ
フォルダーからの場合
- 元データのCSＶを差し替える（古いファイルを削除して新しいファイルを保存する。ファイル名や名前が変わってもＯＫ。ただし列数、タイトルは同じ形式のものを）
- 取得と変換を設定したＥｘｃｅｌファイルを開く。
- データ→すべて更新 をクリックする。ワークシートの表が更新される。
※加えてダウンロードもアップロードも自動化しています。
おまけ
この方法を見つけるまではいったんExcelで開いて、マクロで改行コードを取り除いて保存したCSVを元データとして使っていました。
「取得と変換」のメリットは多数あります。例えばExcelの取り扱い可能行数を超えるCSVからでも、条件を指定してデータを抽出することができます。実際私が扱っているデータは104万件はゆうに超えているものです。
ちなみに取得と変換は凄くて、昨年200万行越え、430列のデータの分析やりましたが、できました。csvで4GB越えです。別に数自慢をしてるわけじゃないですが、気になる方の為に参考までに。
ただし、当然ながら抽出後のデータは取り扱い行数は超えることができません（Excelが読み込めない）のでご注意ください。
ちなみに改行コードなどの特殊文字はPowerQueryで置換することができます。
特殊文字の置換
メーニューバーの「ホーム」から「値の置換」をクリックします。
「詳細設定オプション」をクリックします。
開いたウインドウから「セルの内容全体の照合」にチェックを入れます(必須）。「特殊文字を使用した置換」にチェックを入れて「特殊文字を挿入」をクリックします。プルダウンメニューの中から「Tab」をクリックします。
「検索する値」のボックスに「#(tab)」が自動で入力されました。「OK]をクリックします。
※ここは全項目まとめてやりたいところですが、ひとつづつやりましょう。
「適用したステップ」に「置き換えられた値」が追加されました。数式バーに表示されているのが自動生成された式です。（※数式バーが表示されていない時は「表示」→「数式バー」にチェックを入れると表示されます。）
あ、それから置換する時にカラ文字じゃなく目印（★などの文字列）にすると、どのレコードに不要なコードが入っているか分かります。突き止めてどうする？ですけどねー。
では、また。
これすごく困ったし、英語でも探しまくっても無かったので英語のページも作っておきました。必要な方に伝わると嬉しいです。
English version
