VBAでCSVを開くと日付が勝手に変換される！「0落ち」や型崩れを防ぎ、文字列のまま読み込む手順

基幹システムからCSVをダウンロードしたとします。それをExcelで開いた瞬間、データが書き換わっていませんか。部署コードの「001」が「1」になってしまったり、日付ではないはずの「2/3」が勝手に「2月3日」に変わってしまったりする現象です。この自動変換機能は、普段の入力では親切なのかもしれませんが、データを扱う実務においては本当に困ったお節介になります。特に、マクロを使って大量のCSVファイルをまとめて処理しようとする時、この勝手な型変換のせいでデータが狂ってしまい、エラーで処理が止まる原因になります。

実は、普通に「Workbooks.Open」を使うだけでは防げません。この自動変換を止めることができないのです。マクロを実行するたびにデータが壊れてしまいます。それを手作業で修正するのは非常にもったいない時間と言えます。せっかく自動化を目指しているのに本末転倒となるでしょう。この記事では、私が過去に涙を飲んだデータ破壊の克服法を共有します。CSVを「そのままの文字列」として安全に読み込むテクニックです。

Excelの勝手な型推測とデータ消失

まず、Excelというソフトの基本的な性質を理解しましょう。Excelは入力された内容や読み込んだテキストを常に推測します。それが「何であるか」を自動で判定しようとする性質があるのです。数字だけで構成されていれば「数値」として扱ってしまいます。スラッシュが含まれていれば「日付」として認識しようとします。手動で数値を入力する際には便利な機能と言えるでしょう。しかし、外部データを取り込む際には大きな障害となります。CSVは単なる「カンマ区切りのテキスト」に過ぎません。本来はデータの型に関する情報を持っていないからです。

ExcelのCSV読み込み、自動変換の罠

ところが、ExcelはCSV読み込み時にも推測機能をフル稼働させます。たとえば、商品コードの「005」を読み込んだときを考えてみてください。Excelは「数値の5だな」と勝手に判断して先頭のゼロを消去します。特定の記号を含む文字列も日付や時刻と誤認するケースがあります。内部的にシリアル値という別のデータ形式に変換してしまう厄介な仕様です。この変換はファイルを読み込んだ瞬間に実行されます。そのため、一度変わってしまうと復元は非常に困難になるでしょう。元の「005」や「2/3」という文字列には戻せません。

さらに、一度日付型に変換されたセルは厄介な性質を持ちます。後から表示形式を「文字列」に変えても意味がありません。見た目が変わるだけで内部データは日付のままになります。

Excel自動変換の苦い経験

実は、私も以前この仕様のせいで苦い経験をしています。丸一日分の作業を無駄にしてしまったことがあるのです。当時、数千人分の名簿と勤怠データをCSVで受け取りました。それをVBAで照合するマクロを作っていたのです。ところが、何度実行しても「該当者なし」と大量にエラーが出ます。原因を調べると、社員番号の「0123」が「123」という数値に変わっていました。また、部署コードの「2/3」も、勝手に「2月3日」という日付に書き換わっていたのです。

部署コードの「2/3」が日付に、「001」が「1」になって照合エラーが出たのです。数千行のデータを前に途方に暮れてしまいました。Excelにキレそうになったのを今でも覚えています。結局、深夜までデータの復旧作業に追われる羽目になったのです。

このとき、「データが間違っている」と上司に報告しそうになるほどでした。

型指定マクロで手動作業を数秒に短縮

手動で修正していた頃は、毎回30分以上の時間を浪費していたのです。しかし、マクロで型指定をしてからは数秒で終わります。ちなみに、FieldInfoの引数で「文字列」として指定するための値は「2」です。

レガシーウィザードでCSVを文字列読み込み

しかし、いきなりVBAを組むのが難しい場合もあります。単発の作業でCSVを正しく読み込みたいときもあるでしょう。そのような時には、Excelの標準機能を手動で利用します。「テキストファイルウィザード」という機能が効果的です。最近のExcelでは「テキストまたはCSVから」が目立つかもしれません。ただ、これはPower Queryという別の機能へ誘導されてしまいます。もちろんPower Queryでも解決は可能です。しかし従来のシンプルな読み込みを行いたい場合もあるでしょう。その際は「レガシーウィザード」の設定を有効にしてください。

Excel CSV文字列インポート

具体的には、Excelの「オプション」から「データ」を選択します。「レガシーのデータインポートウィザードを表示する」にチェックを入れておきましょう。すると、インポート時に「テキストファイルから」が現れるはずです。これを選択してCSVを指定してください。画面上に「テキストファイルウィザード」が起動します。ここで重要なのは「3/3」のステップと言えるでしょう。画面に表示されるプレビュー上で、各列を選択していきましょう。そしてデータ形式を「文字列」に変更していくことができます。この操作をすべての列に対して行ってください。

Excelの自動変換を完全に無効化した状態で展開できます。

ただし、この手動操作は列数が多いCSVだと非常に手間です。

OpenTextでテキスト形式取り込み

このトラブルを防ぐため見つけたのが「Workbooks.OpenText」という命令です。普段使う「Workbooks.Open」の代わりに「OpenText」を使います。単に開くだけでなく、取り込みのルールを細かく指示できる機能があります。引数の「FieldInfo」がキーポイントです。ここで「何列目をどの型で読み込むか」を指定します。先ほどのウィザードでの操作を、そのままマクロで実行するイメージと言えるでしょう。

1列目と2列目を文字列にしたいとします。FieldInfoには特定の形式でデータを渡します。具体的には「Array(Array(1, 2), Array(2, 2))」と記述してください。使われている「2」に注目してください。これはVBAの定数「xlTextFormat」に対応しています。Excelに対して「テキストのまま取り込みなさい」と命令できるのです。一切の変換をさせないように指示できます。これで先頭のゼロが消えることもありません。日付に変わる現象も防げます。そのまま読み込むための唯一の手段です。

コピペで簡単！FieldInfo自動判別配列

それでは、実務でそのまま使えるコピペ用のコードを紹介します。このコードの最大の特徴は列数の自動判別です。読み込むCSVの列数を自動的に判断してくれるのです。すべての列に「文字列（定数2）」の指定をループで作成します。これを使えば、列が100列あっても問題ありません。一切の修正なしで安全にファイルを開くことができます。まずは「GetOpenFilename」を使ってファイルを選択させましょう。そのファイルパスを「OpenText」に渡すという流れです。

具体的には、まず整数型の動的配列を用意します。次に、読み込みたい最大列数を上限としてループを回していくのです。たとえば255列などを上限に設定するとよいでしょう。配列の各要素に「列番号」と「2」のペアを格納していきます。最後に、この配列をFieldInfo引数に指定して実行するだけです。

OpenText FieldInfo配列の難関突破

Workbooks.OpenTextのFieldInfoの指定は本当に難解です。配列の作り方が分からずに何度もフリーズさせてしまいました。怒り狂った経験が私にもあるのです。当初は2次元配列の構造がまったく理解できませんでした。括弧の数が合わなかったり型不一致が出たりします。画面を閉じそうになったことは一度や二度ではありません。しかし、ループでの配列作成パターンを確立してからは変わりました。どんなCSVも怖くなくなったのです。

OpenTextのよくあるエラーと対策

このように強力なOpenTextですが、つまずきやすい点があります。初心者が最も悩みやすいポイントは「引数の書き方」と言えるでしょう。OpenTextは非常に多くのオプション引数を持っています。そのため、途中のカンマの数が一つズレただけで失敗するわけです。すぐに「構文エラー」が発生してしまうのです。また、FieldInfoに渡す配列の形式が特殊なのも難点と言えるかもしれません。実行時に「型が一致しません」というエラーもよく出ます。そこで、コードを書く際には「名前付き引数」を利用しましょう。この書き方を強くおすすめします。

名前付き引数とデバッグ、エラー回避の要点

名前付き引数とは、引数の名前を明示して値を渡す書き方です。「FieldInfo:=myArray」のように記述します。これを使うことで、引数の順番を気にする必要がなくなるでしょう。コードの可読性も劇的に向上するはずです。また、デバッグの際は「ローカルウィンドウ」を活用してください。作成した配列変数が正しい構造になっているかを確認します。「(1, 2), (2, 2)…」となっているかチェックしましょう。これによって目に見えないミスの原因を特定しやすくなります。多くのエラーは単純な指定ミスから発生しているからです。

さらに、CSVファイルが他のプロセスで開かれていないか確認します。基幹システムから書き出し中のファイルは開けません。

自動変換回避！Line InputでのCSV読込

ところが、極稀にOpenTextでも対応しきれないCSVが存在します。たとえば、一つのセルの中に改行が含まれている場合です。カンマ以外の特殊な区切り文字が混在していることも考えられます。そのような時には、Excelの機能に頼らない手法が有効です。「Line Input」という命令を使ってみてください。ファイルをテキストデータとして1行ずつ直接読み込む手法です。これは、Excelのシートとして開くわけではありません。マクロの中でテキストファイルをただの文字列として取り込みます。後から自分でセルに配置していくという確実な方法です。

Excel自動変換回避の鉄則

この手法の最大のメリットは、お節介な自動変換を防げる点です。Excelが介入する余地が1ミリもありません。ファイルを開くのではなく、テキストを「読み取る」だけです。そのため、数値の0落ちや日付変換が起こりようがありません。読み取った一行の文字列を「Split」関数でカンマ分割します。各要素を順番にセルへ書き込んでいく作業を行ってください。このとき「Value」ではなく「Text」プロパティを意識します。あらかじめセルの表示形式を「文字列」に設定しておきましょう。そうすれば完璧に元のデータを再現できます。

もちろん、この方法はOpenTextに比べると少し遅くなります。処理速度が低下する傾向があるのは事実です。

信頼性の高いVBA自動化術

このように、VBAでCSVを扱う際は自動変換との戦いと言えるでしょう。Excelは良かれと思ってデータを書き換えてしまうからです。それが実務では大きな障害となってしまいます。仕組みを理解し「Workbooks.OpenText」を覚えてからは、このトラブルに悩まされなくなりました。FieldInfoの設定も、一度コードを作ればコピペして使い回せます。列を文字列として安全に取り込めると、実務の安心感が変わるはずです。

上司に「このマクロ、数字が合わないんだけど」と言われました。胃が重くなる感覚は二度と味わいたくありません。原因がExcelの仕様にあると気づかず、自分の能力を責めていたのです。しかし型指定の技術を覚えてからは状況が好転しました。今では「データは壊れていない」と言い切れます。

正しいデータと賢い自動化

マクロがデータ正確性を損なっては最悪です。本末転倒な結果は避けなければなりません。作業時間を数時間から5分に短縮できたとします。しかしデータが間違っていれば意味がありません。今回のテクニックをぜひ駆使してください。信頼性の高い自動化ツールを作成してみましょう。データと正しく向き合うことがスキルアップの近道です。Excelのお節介機能に負けないでください。スマートな業務効率化を目指して頑張りましょう。

関連リンクとチェックリスト

学習サービスとアンケート