'データ内にカンマが含まれているCSVファイルの取り込み
Sub GetCSVCamma()
Dim filePath As String
Dim F As Integer
Dim strLine As String
Dim strField As Variant
filePath = "C:\Users\XXX\基準2.csv"
F = FreeFile()
Open filePath For Input As #F
Line Input #F, strLine
Do Until EOF(F)
'データを1行読み込む
' Input #F, strField(0), strField(1), strField(2), strField(3)
Line Input #F, strLine
If CsvChackComma(strLine) = False Then
strLine = RreplaceSymbol(strLine)
strField = Split(Replace(strLine, """", ""), "$;$")
Else
strField = Split(Replace(strLine, """", ""), ",")
End If
Loop
Close #F
End Sub
'CSVファイルの取り込み データ内にカンマが含まれているかチェック
Function CsvChackComma(argStrLine As String) As Boolean
Dim buf As String
Dim strChar As String
Dim quotCnt As Long
Dim charCnt As Long
buf = argStrLine
strChar = ","
charCnt = Len(buf) - Len(Replace(buf, strChar, ""))
' MsgBox charCnt & "は" & charCnt & "個あります"
strChar = """"
quotCnt = Len(buf) - Len(Replace(buf, strChar, ""))
' MsgBox quotCnt & "は" & quotCnt & "個あります"
If ((charCnt * 2) + 2) Mod quotCnt = 0 Then
CsvChackComma = True
Else
CsvChackComma = False
End If
End Function
'受け取った文字列のカンマを$;$に置き換える
'ダブルクォーテーションで囲まれているカンマは置き換えない
Function RreplaceSymbol(argLine As String) As String
Dim buf As String
Dim strLine As String
Dim i As Long
Dim quotCnt As Long
strLine = argLine
For i = 1 To Len(strLine)
buf = Mid(strLine, i, 1)
If buf = """" Then
quotCnt = quotCnt + 1
ElseIf buf = "," Then
If quotCnt Mod 2 = 0 Then
strLine = Left(strLine, i - 1) & "$;$" & Right(strLine, Len(strLine) - i)
End If
End If
Next i
RreplaceSymbol= strLine
End Function
