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


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

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

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

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

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

では、行ってみます!

スポンサーリンク

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

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

'CSVファイルの取り込み 一行ずつ取得しカンマでスプリットする最も定番なパターン
Sub getCSV()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "C:UsersNoriakiDropbox40_ブログvba-csvtestラーメン店アンケート_dq.csv"

Dim i, j As Long
Dim strLine As String
Dim arrLine As Variant 'カンマでsplitして格納

Open strPath For Input As #1 'csvファイルをオープン

i = 1
Do Until EOF(1)

    Line Input #1, strLine
    arrLine = Split(Replace(strLine, """", ""), ",") 'strLineをカンマで区切りarrLineに格納

    For j = 0 To UBound(arrLine)

        ws.Cells(i, j + 1).Value = arrLine(j)

    Next j
    i = i + 1
Loop

Close #1

End Sub

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

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

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

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

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

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

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

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

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

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

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

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

'CSVファイルの取り込み データ内にカンマが含まれているパターン
Sub getCSV_camma()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

Dim strPath As String
strPath = "C:UsersNoriakiDropbox40_ブログvba-csvtestラーメン店アンケート_dq & comma.csv"

Dim i, j As Long
Dim strLine As String
Dim arrLine As Variant 'カンマでsplitして格納

Open strPath For Input As #1 'csvファイルをオープン

i = 1
Do Until EOF(1)

    Line Input #1, strLine

    arrLine = Split(Replace(replaceColon(strLine), """", ""), ":") 'strLineをコロンで区切りarrLineに格納

    For j = 0 To UBound(arrLine)

        ws.Cells(i, j + 1).Value = arrLine(j)

    Next j
    i = i + 1
Loop

Close #1

End Sub

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

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

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

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

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

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

とある変更、というのは

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

という処理です。

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

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

'受け取った文字列のカンマをコロンに置き換える
'ダブルクォーテーションで囲まれているカンマは置き換えない
Function replaceColon(ByVal str As String) As String

Dim strTemp As String
Dim quotCount As Long

Dim l As Long
For l = 1 To Len(str) 'strの長さだけ繰り返す

    strTemp = Mid(str, l, 1) 'strから現在の1文字を切り出す

    If strTemp = """" Then  'strTempがダブルクォーテーションなら

        quotCount = quotCount + 1  'ダブルクォーテーションのカウントを1増やす

    ElseIf strTemp = "," Then  'strTempがカンマなら

        If quotCount Mod 2 = 0 Then  'quotCountが2の倍数なら

            str = Left(str, l - 1) & ":" & Right(str, Len(str) - l)  '現在の1文字をコロンに置き換える

        End If

    End If

Next l

replaceColon = str

End Function

関数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(文字列, 文字数)

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

str = Left(str, l - 1) & ":" & Right(str, Len(str) - l)  '現在の1文字をコロンに置き換える

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を読み込む方法

コメント

  1. すがもん より:

    はじめまして。

    最近調べもの中にこちらのサイトの存在を知り興味深く拝見させていただいております。

    不躾ながら、この記事についての質問させてください。

    21行目で arrLine = Split(Replace(replaceColon(strLine), “”””, “”), “:”)
    と一旦 “,” を “:” に変換させる処理を行っていらっしゃいますが、
    ここの処理は
         arrLine = Split(strLine,”,”)
    としておき、セルに値を入れる段階で
    ws.Cells(i, j + 1).Value = Replace(arrLine(j),””””,””)
    と処理するか、もしくは
    ws.Cells(i, j + 1).Value = Replace(ws.Cells(i, j + 1).Value
    と、セルの値に処理を行うと何か問題が発生するのでしょうか?

    こちらの方が処理が簡単ですが、記事になさる場合に面白みがないため
    わざと手の込んだ手法を取られたのかもしれませんが気になりましたので。

    本来なら自分でCSVファイル等を作成して試行すべきなのですが、
    こちらでの質問となりました事をご容赦ください。

    • すがもんさん

      コメントありがとうございます。
      いったんコロンにしている理由は記事内でもお伝えしていますが、「データ自体にカンマが含まれている場合(例えば”1,000円”など)は、そこで分割されてしまう(”1″と”000円”)という現象を防ぐためです。
      コロンへのリプレイスは独自関数で「ダブルクォーテーションで囲まれてないところだけ」という条件にしているため、データ内のカンマはそのまま、データ外のカンマはコロンに変更後にsplitされます。

      ご確認いただければと思います。

  2. すがもん より:

    あ、なるほど!
    私の認識不足で、前回の記事と今回の記事の内容がごっちゃになっておりました。
    ダブルクォーテーションで囲まれている範囲内でもカンマで改行されてしまうのですね。
    とても勉強になりました。
    ありがとうございました。

  3. ぴょん より:

    はじめまして
    このサイトで一から順に勉強させていただいている者です。

    細かいことですが
    21行目のコメントは

    誤 strLineをカンマで区切りarrLineに格納
    正 strLineをコロンで区切りarrLineに格納

    ではないでしょうか?

    • ぴょんさん

      コメントありがとうございます!
      おっしゃる通りですね…ご指摘ありがとうございます。

      修正をさせていただきました。

      今後とも、弊ブログをよろしくお願いいたします!

タイトルとURLをコピーしました