VBAでダブルクオテーションの無い、かつデータ内にカンマが含まれているCSVファイルを取り込みたい

Question

やりたいこと

以下のようなCSVファイルをエクセルにインポートしたい。

• データはダブルクォーテーションで囲われている列と囲われていない列がある
• 文字コードShift_JIS、改行コードLF
• ダブルクォーテーションで囲われていない列のデータ内にカンマが存在する

実際のサンプルデータ

顧客ID,"申込日",都道府県,市区町村,番地等,マンション名,日付
1000,"2021年06月28日",愛知県,名古屋市西区,X丁目XX-X,〇〇ハイツ,201,2021年7月3日

試してみたコード

Sub Import()

    '########## インポート ##########
    Dim i As Long, j As Long
    Dim buf As String, tmp As Variant, tmp2 As Variant, n As Long
    Dim strLine As Variant
    Dim arrLine As Variant
    Dim myFile As Variant
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Open ActiveWorkbook.Path & "\sample.csv" For Input As #1
        Line Input #1, buf
    Close #1
    
    'bufをLFコードで区切りtmpに格納
    strLine = Split(buf, vbLf)
        
    For i = LBound(strLine) To UBound(strLine)
        '項目行
        If i = 0 Then
            arrLine = Split(Replace(strLine(i), """", ""), ",")
        Else
            'strLineをコロンで区切りarrLineに格納
            arrLine = Split(Replace(replaceColon(strLine(i)), """", ""), ":")
        End If
    
        For j = 0 To UBound(arrLine)
            If i = 0 Then
                ws.Cells(i + 1, j + 1).Value = arrLine(j)
            Else
                ws.Cells(i + 1, j + 1).Value = Replace(arrLine(j), """", "")
            End If
        Next j
    Next i
    
End Sub

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

    Dim strTemp As String
    Dim quotCount As Long
    
    Dim l As Long
    'strの長さだけ繰り返す
    For l = 1 To Len(str)
        'strから現在の1文字を切りだす
        strTemp = Mid(str, l, 1)
        'strTempがダブルクオーテーションなら
        If strTemp = """" Then
            'ダブルクオーテーションのカウントを1増やす
            quotCount = quotCount + 1
        'strTempがカンマなら
        ElseIf strTemp = "," Then
            'quotCountが2の倍数なら
            If quotCount Mod 2 = 0 Then
                '現在の1文字をコロンに置き換える
                str = Left(str, l - 1) & ":" & Right(str, Len(str) - l)
            End If
        End If
    Next l
    
    replaceColon = str

End Function

結果

「マンション名」にカンマが入ってしまっているため以下のようにずれてセルに格納されてしまう。
|顧客ID|申込日|都道府県|市区町村|番地等|マンション名|日付||
|:--|:--:|--:|
|1000|2021年06月28日|愛知県|名古屋市西区|X丁目XX-X|〇〇ハイツ|201|2021年7月3日|

元データをすべてのデータをダブルクォーテーションで囲んでもらわない限り無理な気はするのですが、対処方法がありましたら教えていただけますと幸いです。

Answer 1

こんな感じでお茶を濁すのはどうでしょうか。

If UBound(arrLine) > 6 Then
    arrLine(5) = arrLine(5) & arrLine(6)
    arrLine(6) = arrLine(7)
    ReDim Preserve arrLine(6)
End If

Comments

[singlestroke]

カンマが「マンション名」でかつ「カンマが1つ」ということであればいけそうですね。
ありがとうございます。

[jinoji]

カンマが入りそうなのはマンション名だけ、という割り切りで、
カンマ複数を想定するなら、たとえば以下のように変えればいいかと。
u = UBound(arrLine)
If u > 6 Then
Debug.Print i, u, strLine(i)
For k = 6 To u - 1
arrLine(5) = arrLine(5) & arrLine(k)
Next
arrLine(6) = arrLine(u)
ReDim Preserve arrLine(6)
End If

[singlestroke]

カンマの数は1つ、入る可能性があるのは「番地等」と「マンション名」で同時に入ることはない、という条件になりそうです。

Answer 2

| ダブルコロンで囲われていない列のデータ内にカンマが存在する

この「ダブルコロン」はダブルクォーテーションのことですか？

カンマはデータの区切りなので、データにそういう誤りがあるとしたら、どうしようもないと思います。

Comments

[singlestroke]

> この「ダブルコロン」はダブルクォーテーションのことですか？
そうです。修正しました。

やはり無理ですかね。。

[itagagaki]

カンマが想定よりも多いことから、カンマを含むカラムが存在することは推測できても、ではそれがどのカラムなのかを推定することは不可能ですよね。
あ、でも、カンマを含む可能性があるのはマンション名以外には無いとすれば、カンマが多い場合つまりカラム数が多い場合に既定のカラム数になるまでマンション名のカラムに次のカラムのデータを結合していくという処理をすれば、まあ可能ではあると思いますが。