この記事はAdvent Calendar 2017 Excel VBA 14日目の投稿です。
前半は、ほぼ駄文なので、ソースコードだけ見たいひとはこちら
経緯
先日、仕事でutf-8のcsvファイルを扱うことがあったのですが、色々と問題があったため、csvファイルをExcelで読み込めるまでの流れをまとめてみました。
csvファイルとは
csvファイルとはカンマ区切りのファイルのことです。
よくツールやＤＢのデータのインポート、エクスポートに使用されます。
便利なファイル形式ではありますが、人間がそのまま読むには縦列がずれてしまいとても読みづらいです。
そんなとき、便利なのがExcelです。カンマごとにセルを分けてくれるため縦列がそろい、大変見やすくなります。
csvファイルをExcelで見たいとき皆さんはどうやって見ていますか？
①.csvをExcelに紐づけて直接開く
②すでに開いているExcelにドラッグアンドドロップする
③データのインポートから取り込んでいる
④テキストで開いてカンマをタブに一括置換してExcelに張り付けている
etc
個人的にはデータ内にカンマがない限り④でやっています。
なぜなら、①、②は手軽ですがcsvファイルの文字コードがutf-8だと文字化けすることがあるからです。
③は面倒ですがきちんと文字コードを指定してあげればちゃんと読込んでくれますが余計なことされていそうで嫌い、あとLF改行とかCR改行？が混じっていたりとか原因不明でよく失敗します。
うまく読み込めないとき
上記方法が全滅した場合、私はしばらく途方に暮れます。。。（あぁ、面倒くさい
途方に暮れるのに飽きたら、ExcelにはまだExcelマクロという手段があるじゃないか！と気をとり直してALT + F11を押します。
自分が欲しい条件のcsvリーダのサンプルソースをぐーぐる先生に聞いてみましたが、見つからない・・・
欲しいサンプルソースの条件
以下の二つの条件を満たすサンプルソースが欲しかった。
- 文字コードがUTF-8で読めること
- ダブルクォーテーション内にカンマ、改行があっても処理できること
utf-8のcsvファイルを読む方法、ダブルクオーテーション内のカンマとか改行コードはそのままにして読む方法など個別にはありますが、どっちもできるものがなかった。
**ないなら作ればいいじゃない！**のエンジニア精神で作ってみました。
Excelマクロのいいところはぐぐればサンプルコードが沢山あり単体で使えなくても繋ぎ合わせれば大体のことができることです。
参照設定
ADODB.Streamを使用するためMicrosoft ActiveX Data Objects 6.1 Libraryの参照を追加します。
実際のソースコード
仕様：入力の文字コードはutf-8、改行コードCRLF or LF、ダブルクオーテーション内は改行コード、カンマがあっても良い
Const delimiter = "~" 'ダブルクォーテーション内のカンマを一時的に変える文字指定
Const lineFeedCode = vbCrLf '読み込むファイルの改行コード指定 CRLF or LF
Sub getCSV_utf8()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
Dim strPath As String
strPath = "C:\wk\test.csv" '入力ファイル指定
Dim i As Long, j As Long
Dim strLines, arrLine, strLine As Variant
Dim strAll As String
Dim strBuf As String
'ADODB.Streamオブジェクトを生成
Dim adoSt As Object
Set adoSt = CreateObject("ADODB.Stream")
i = 1
With adoSt
.Charset = "UTF-8" 'Streamで扱う文字コートをutf-8に設定
.Open 'Streamをオープン
.LoadFromFile (strPath) 'ファイルからStreamにデータを読み込む
strAll = .ReadText(adReadAll)
strLines = Split(strAll, lineFeedCode)
For Each strLine In strLines 'Streamの末尾まで繰り返す
If strBuf <> "" Then
strBuf = strBuf & lineFeedCode & strLine
Else
strBuf = strLine
End If
If double_quotation_count(strBuf) Mod 2 = 0 Then
arrLine = Split(Replace(replaceDelimiter(strBuf), """", ""), delimiter) 'strLineをカンマで区切りarrLineに格納
For j = 0 To UBound(arrLine)
ws.Cells(i, j + 1).Value = arrLine(j)
Next j
i = i + 1
strBuf = ""
End If
Next strLine
.Close
End With
End Sub
'受け取った文字列のカンマをdelimiterに置き換える
'ダブルクォーテーションで囲まれているカンマは置き換えない
Function replaceDelimiter(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) & delimiter & Right(str, Len(str) - l) '現在の1文字をdelimiterに置き換える
End If
End If
Next l
replaceDelimiter = str
End Function
Function double_quotation_count(target)
Dim buf As String, i As Long, cnt As Long
For i = 1 To Len(target)
If Mid(target, i, 1) = """" Then cnt = cnt + 1
Next i
double_quotation_count = cnt
End Function