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

CSVをExcelで開いたら。社員番号の「001」が「1」になっていたり。商品コードが日付に変わっていたりすることがあります。先頭のゼロが消えると、照合や請求処理で別の値として扱われます。

ExcelはCSVの内容を数値や日付として自動判定します。通常の操作では便利ですが、業務データをそのまま取り込みたい場合には困ります。VBAでWorkbooks.Openを使っても。自動変換を止められないケースがあります。

CSVを開いたときに値が変わる

この記事では。Workbooks.OpenTextのFieldInfoで列を文字列として指定する方法と。うまく動かないときの確認点を説明します。

Workbooks.Openでは防げなかった

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

元の「005」や「2/3」という文字列には戻せません。

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

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

列指定を確認する

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

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

Workbooks.OpenTextで列を指定する

Workbooks.OpenText Filename:=csvPath, _
    DataType:=xlDelimited, Comma:=True, _
    FieldInfo:=Array(1, 2), Local:=True

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

FieldInfoの指定内容を確認する

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

その際は「レガシーウィザード」の設定を有効にしてください。

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

この操作をすべての列に対して行ってください。

読み込み方法を選ぶ

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

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

VBAで実行するときの注意点

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

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

そのまま読み込むための唯一の手段です。

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

取り込み後に検証する

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

配列の組み立てでつまずいた点

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

よくあるエラーを切り分ける

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

この書き方を強くおすすめします。

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

多くのエラーは単純な指定ミスから発生しているからです。

列指定を確認する

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

Line Inputを使う場合

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

後から自分でセルに配置していくという確実な方法です。

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

そうすれば完璧に元のデータを再現できます。

読み込み方法を選ぶ

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

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

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

取り込み後の確認を自動化する

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

実際に使う前の確認項目

取り込み後は、先頭ゼロ、日付らしい文字列、空欄、引用符を含む値を数件確認します。コードがエラーなく終わっても、値が変わっていれば成功とは言えません。

列順が変わるCSVでは、ヘッダーを読んで対象列を探す処理が必要です。実際のファイルを複数パターンで試してから、自動化の範囲を広げてください。

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

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