Help us understand the problem. What is going on with this article?

utf-8のcsvファイルをExcelで読み込むまで

More than 1 year has passed since last update.

この記事はAdvent Calendar 2017 Excel VBA 14日目の投稿です。

前半は、ほぼ駄文なので、ソースコードだけ見たいひとはこちら

経緯

先日、仕事でutf-8のcsvファイルを扱うことがあったのですが、色々と問題があったため、csvファイルをExcelで読み込めるまでの流れをまとめてみました。

csvファイルとは

csvファイルとはカンマ区切りのファイルのことです。
よくツールやDBのデータのインポート、エクスポートに使用されます。
便利なファイル形式ではありますが、人間がそのまま読むには縦列がずれてしまいとても読みづらいです。

そんなとき、便利なのが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の参照を追加します。
参照設定1.png

参照設定2.png

実際のソースコード

仕様:入力の文字コードはutf-8、改行コードCRLF or LF、ダブルクオーテーション内は改行コード、カンマがあっても良い

csv_reader
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

参考

t114
基本的に自分用の備忘録メモとして使っているため、細かいところはご容赦ください。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした