【Excel VBA】データの中にカンマが入っているCSVファイルを綺麗に分割する方法

以下のようなデータがあった場合に

“○○○○○”,”○○○,○○”,”○○○○○”

区切り文字をカンマで分割すると

配列(0) → “○○○○○”

配列(1) → “○○○

配列(2) → ○○”

配列(3) → “○○○○○”

というデータが出来上がってしまいます。

どうすれば綺麗に分割できるのでしょうか？

この記事では以下の方法を知ることができます。

この記事で学べること

データの中にカンマが入っているCSVファイルを綺麗に分割する方法。

サンプルコード
ポイントとなる技術
実行結果
まとめ

サンプルコード

以下はデータの中にカンマが入っている場合にも綺麗に分割するコードです。

Sub sample()

     ' 読み込むテキストファイルのパスを指定します
     Open "C:\Sample\Sample.csv" For Input As #1

     ' 読み込み先Excelの行番号
     Dim row_num As Long: row_num = 1

     ' ファイルの終端まで繰り返し処理
     Do While Not EOF(1)

         ' ファイルを1行読み込みbufに入れます
         Line Input #1, buf

         ' 「","」の文字列を「"|"」に置換する
         buf_replace = Replace(buf, """,""", """|""")

         ' CSVファイルを「|」で分割し配列に入れます
         dataArr = Split(buf_replace, "|")

         ' カンマで分割された数の分だけ列方向繰り返す
         For i = 0 To UBound(dataArr)

             ' 読み込んだ値をExcelに書込みます
             Cells(row_num, i + 1).Value = dataArr(i)

         Next

         ' 行番号を更新します
         row_num = row_num + 1

     Loop

     ' 読み込んだファイルを閉じます
     Close #1

End Sub

Sub sample()

' 読み込むテキストファイルのパスを指定します

Open "C:\Sample\Sample.csv" For Input As #1

' 読み込み先Excelの行番号

Dim row_num As Long: row_num = 1

' ファイルの終端まで繰り返し処理

Do While Not EOF(1)

' ファイルを1行読み込みbufに入れます

Line Input #1, buf

' 「","」の文字列を「"|"」に置換する

buf_replace = Replace(buf, """,""", """|""")

' CSVファイルを「|」で分割し配列に入れます

dataArr = Split(buf_replace, "|")

' カンマで分割された数の分だけ列方向繰り返す

For i = 0 To UBound(dataArr)

' 読み込んだ値をExcelに書込みます

Cells(row_num, i + 1).Value = dataArr(i)

' 行番号を更新します

row_num = row_num + 1

Loop

' 読み込んだファイルを閉じます

Close #1

End Sub

ポイントとなる技術

区切り文字を別の文字に置換する

Replace関数を使用し、区切り文字であるカンマを別の文字列に置換します。

Replace関数

buf_replace = Replace(buf, “検索する文字列”, “置換後の文字列”)

公式の説明：開始位置から始まる文字列式の部分文字列である文字列 (既定値は 1) を返します。この文字列では、指定した部分文字列が指定した回数別の部分文字列に置き換えられます。

この関数を使用して分割文字列を置換します。

,（カンマ）を |（パイプ）に置換する

Replace関数により ,（カンマ）を |（パイプ）に置換し、分割文字列を |（パイプ）にします。

分割文字列の置換

buf_replace = Replace(buf, ““”,””“, ““”|””“)

※ ,（カンマ）のみを検索するのではなく、両サイドの “（ダブルクォーテーション）を合わせて指定するのがポイントです。

なぜなら ,（カンマ）のみを指定すると、データの中のカンマまで |（パイプ）に置換されてしまうからです。今回はあくまでも、分割文字列を ,（カンマ）から |（パイプ）に置換していので “,” を検索文字列に指定します。

“○○○○○”,”○○○,○○”,”○○○○○”

　　　　　　　　　↓

“○○○○○”|”○○○,○○”|”○○○○○”

このように置換します。

” を文字列として指定する時は “ の前に ” をつけます。

|（パイプ）を分割文字列に指定する。

Split関数を使用し、|（パイプ）で分割します。

Split関数

Split(“分割したい文字列”, “|”)

公式の説明：指定された数のサブ文字列が含まれる 0 ベースの 1 次元配列を返します。

Split関数の第１引数に、分割したい文字列を指定します。

Split関数の第２引数に、どの文字列で分割するかを指定します。今回は |（パイプ）を指定します。

実行結果

では以下のCSVファイルを読み込んで実行してみましょう。

普通にカンマで分割した結果と、今回のように置換して分割した結果を比較してみましょう。

カンマで分割した結果

カンマの位置で分割されているため、ずれてしまいました。

では、今回のプログラムで再度実行してみましょう。

置換してパイプで分割した結果

きちんと区切り位置で分割されています。

まとめ

今回は、CSVファイルのデータの中に ,（カンマ）が入っている場合の対処ほうをご紹介しました。

この記事で学んだこと

・ CSVファイルのデータにカンマが入っている場合は “,” を “|” に置換し | で分割する。

もしよろしかったら、ご参考にしてください。

ではまた。