エクセルVBAでデータにカンマが含まれてしまっているCSVを取り込む

★気に入ったらシェアをお願いします!

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

引き続きエクセルVBAで様々なタイプのCSVを取り込んでいきます。

前回はCSVのデータがダブルクォーテーションで囲まれているパターンのCSVを取り扱いました。

エクセルVBAでダブルクォーテーションで囲まれているCSVファイルを取り込む
エクセルVBAでCSVファイルを取り込む方法シリーズです。今回はデータがダブルクォーテーションで囲まれているパターンのCSVをエクセルVBAで取り込む方法についてお伝えしたいと思います。

今回は、データの中にカンマが含まれている場合のCSVをエクセルVBAで取り込む方法についてお伝えしていきます。

では、行ってみます!

前回のおさらいと今回の課題

まず、前回のおさらいをしていきます。

Line InputでCSVのレコードを一行取り込むと、ダブルクォーテーションをReplaceで削除した上で、Splitでカンマ区切りをするという処理ですね。

データにカンマが含まれるとそこで分割されてしまう

今回は、取り扱うCSVデータを少し改良しまして、このようなファイルを作成しました。

データ内にカンマが含まれているCSV
チャーシュー麺の定価が1,000円ですので、CSVデータ内には「1,000円」とカンマが含まれたデータが含まれています。

カンマ…このままでちゃんと処理できるのでしょうか?

では、これを上記プログラムで取り込んでみます。

CSVを取り込んだらズレてしまった
やっぱり駄目でした…

チャーシューの行のセルがズレてしまっています。

本当は分割してほしくないのですが「1,000」のカンマでSplitされてしまっているんです。

カンマがデータに含まれているCSVデータを取り込むプログラム

では、データの中にカンマが含まれている場合のCSV取込み方を紹介していきます。

プログラムはこちらです!

前回のプログラムから変わったのは21行目だけです。

Splitがカンマではなくて「:」つまりコロンになっていますね。

またstrLineを引数としたreplaceColon…謎の関数があります。

これは自作の関数です。以降で解説をしていきます。

ダブルクォーテーションの外のカンマをコロンに置き換える関数

replaceColonは文字列を引数strとして受け取り、とある変更を加えて戻す関数です。

とある変更、というのは

  • テータに含まれないカンマはコロンに置き換える
  • データに含まれるカンマはそのまま

という処理です。

こうすることで、返された文字列はコロンでSplitをすればよい、ということになります。

プログラムはこちらです。

関数replaceColon(ByVal str As String)の流れを整理してみますね。

For文とMidで文字列の最初から1文字切り出す

まずFor文で受け取った文字列のstrの文字を最初からなめていきます。

11行目のMidでstrから現在の文字を1文字取り出してstrTempに格納しています。

Mid

Mid(対象文字列,開始文字数,切り出す文字数)

と記述して、対象文字列を最初からの文字数から切り出す文字数分を切り抜いて返します。

ダブルクォーテーションの数をカウントして区切るべきカンマを見定める

次に、strTempがダブルクォーテーションならquotCountが1プラスされます。quotCountは今まで出会ったダブルクォーテーションの数をカウントしていますので

  • quotCountが偶数:strTempはデータの外
  • quotCountが奇数:strTempはデータの中

ということが言えます。データは全て2つのダブルクォーテーションで囲われているわけですから、奇数の間はデータの中にいるわけです。

ですから、strTempがカンマだったときに

  • quotCountが偶数:strTempをコロンに置き換える
  • quotCountが奇数:strTempはそのままカンマ

としておきます。

Modですが

数値A Mod 数値B

は数値Aを数値Bで割った場合の余りを計算します。

LeftとRightを使って現在の文字列をコロンに置き換える

LeftRightですが

Left(文字列, 文字数)
Rigth(文字列, 文字数)

文字列の最初から文字数分を切り出した、または文字列の最後から文字数分を切り出した文字列を返します。

lは現在の位置ですから、Left(str, l – 1)は現在の位置より前の文字列です。

Len(str)はstrの長さ、lは現在の位置ですから、Right(str,Len(str) – l)は現在の位置より後の文字列です。

つまり、strの現在いる位置の文字をコロンに置き換わるだけです。

これで、データを分割すべきカンマはすべてコロンに置き換わり、データ内のカンマはそのまま維持されます。

実行結果

以上のプログラムを実行してみますと

CSVをエクセルVBAで取り込んだ

ちゃんとズレずにCSVデータを取り込むことができました。

まとめ

これで、データの中にカンマが含まれている場合のCSVをエクセルVBAで取り込むことができました。

Mid,Left,Rightなどを使って文字列を切り出す処理や、ダブルクォーテーションが登場した数をカウントしてModで偶数個目か奇数個目かを判定する処理など、なかなか組みごたえのある内容だったかもしれません。

少しややこしい感じはありますが、数値にカンマが含まれているケースは結構出くわします。

ぜひマスターしちゃいましょう!

次回、CSVファイルの文字コードがUTF-8の場合の取り込み方を紹介できればと思います。

文字化けよさようなら!エクセルVBAでUTF-8のCSVを読み込む方法
エクセルVBAのLine Input命令でUTF-8のCSVファイルを取り込むと文字化けを起こします。今回は、ADODB.Streamを使ってUTF-8のCSVファイルを取り込む方法についてお伝えします。

どうぞお楽しみに!

連載目次:エクセルVBAで色々なCSVファイルを取り扱う

様々なソフトウェア間のデータ交換の際に一般的に多く活用されているCSVファイル。エクセルで取り込んで集計や加工することもたくさんあることと思います。このシリーズでは、CSVファイルをエクセルVBAで取り込むためのテクニックについてお伝えしています。
  1. CSVとは何か?そしてエクセルでCSVファイルを扱うときの注意点
  2. エクセルVBAでCSVを読み取る初心者向けの最も簡単なプログラム
  3. エクセルVBAでCSVデータをカンマで区切ってワークシートに取り込む方法
  4. エクセルVBAでCSV取込み~改行がされないパターンの対処法
  5. エクセルVBAでダブルクォーテーションで囲まれているCSVファイルを取り込む
  6. エクセルVBAでデータにカンマが含まれてしまっているCSVを取り込む
  7. 文字化けよさようなら!エクセルVBAでUTF-8のCSVを読み込む方法