Sub CsvImport001Management02()
'インポート CSV(UTF-8)
'項目にカンマ・改行が含まれているデータ
Dim i As Integer
Dim ado_strem As New ADODB.Stream
Dim strLine As String, strLine2 As String
Dim tmp As Variant, tmp2 As Variant
Dim canma As String
Dim colName(20) As String
'File変数
Dim filePath As String
filePath = "C:\Users\shiba\My\プログラム言語\ACCESS\基準.csv"
canma = ","
With ado_strem
.Charset = "UTF-8"
.Mode = 3 '1:読み取り専用 3:読み取り/書き込み両方
.Type = adTypeText '1:バイナリデータ 2:テキストデータ 0:不明(規定値)
.LineSeparator = 10 '改行コード 10:LF 13:CR -1:CRLF(規定値)
.Open
.LoadFromFile (filePath)
'列取得 すべてのデータを読み込むには「-1」を指定し、1行ずつ読み込むときは「-2」を指定
strLine = .ReadText(-2)
strLine = Replace(strLine, """", "")
tmp = Split(strLine, ",")
For i = LBound(tmp) To UBound(tmp)
colName(i) = tmp(i)
Next i
'データ読込
Do While Not (.EOS)
strLine = .ReadText(-2)
'セルの改行対策
Do
If Right(strLine, 1) <> """" Then
strLine2 = .ReadText(-2)
strLine = strLine & strLine2
Else
Exit Do
End If
Loop
'最初のデータの「"」以降を取り除く
strLine = Right(strLine, Len(strLine) - 1)
'最後のデータの「"」以降を取り除く
strLine = Left(strLine, Len(strLine) - 1)
'","で分割
tmp = Split(strLine, """" & canma & """")
For i = LBound(tmp) To UBound(tmp)
' colName(i) = tmp(i)
Debug.Print tmp(0) & ":" & tmp(1) & ":" & tmp(2)
Next i
i = i + 1
Loop
.Close
End With
End Sub