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

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

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

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

301 Moved Permanently

tonari-it.com

今回は、データの中にカンマが含まれている場合の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データを少し改良しまして、このようなファイルを作成しました。

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

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

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

やっぱり駄目でした…

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

本当は分割してほしくないのですが「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を使って現在の文字列をコロンに置き換える

LeftとRightですが

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データを取り込むことができました。

まとめ

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

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

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

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

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

文字化けよさようなら！エクセルVBAでUTF-8のCSVを読み込む方法

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

tonari-it.com

どうぞお楽しみに！

連載目次：エクセル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を読み込む方法