【Excel VBA】CSVファイルを開く４パターン (Workbooks.Open、LineInput、ADODB.Stream、OpenText)

いろいろなアプリを使用しているとCSVファイルを使ってデータを書き出したり、そのデータを別のアプリにインポートして連携したりすることがあります。

CSVファイルはExcelを使用して開いて、データを分析したり加工することが多いです。CSVの既定アプリがExcelということもあってCSV=Excelと思っている人も少なくないでしょう。

ただ、CSVファイルをExcelで開く際には注意すべき点が多くあります。また、CSVファイルを開く作業をVBAマクロで自動化する場合、開き方によってはデータを正しく取り扱えない場合があります。

今回はVBAマクロでCSVファイルを開く際に注意すべき点とその対処法を４パターンに分けて紹介します。

CSVファイルとは
1. CSVファイルとExcelは相性が悪い
Excelブックとして開く
LineInputで開く
1. TAB記号区切りに変換してTAB記号で分割する
UTF-8のCSVファイルをADODB.Streamで開く
OpenTextで区切り文字と文字コードを指定してブックとして開く

CSVファイルとは

CSVファイルとExcelファイルを混同している人が多いですが、CSVファイルはあくまでテキストファイルです。

今回使うサンプルファイル「Revolver.csv」をテキストエディタで開くとこのようになっています。

ファイル名からビートルズのアルバム『リボルバー』であると察していただけるかと思います。左からトラックNo、曲名、ボーカル担当という順番にデータが並んでいるのですが、それぞれのデータは「, (カンマ)」で区切られています。

CSVは「Comma-Separated Values」の略で、このようにカンマで区切ってテーブルの構造を表す形式です。

CSVファイルとExcelは相性が悪い

CSVファイルはアプリケーション間のデータ連携などにもよく使われまして、Excelでも開いて編集することが可能です。アプリケーションから出力したデータをExcel上で管理したり、別のアプリケーションに渡すのにExcelで加工することができて便利です。

ただ、Excelで開けると一般的に周知されているCSVファイルですが、実はExcelの機能によってCSVファイルの値が意図しない形で取り扱われたり、場合によってはCSVファイルが破壊されたりします。

そうならないようにExcelでCSVファイルを取り扱う場合は開き方とデータの取得方法を工夫してやる必要があります。

今回はVBAを使ってCSVファイルからのデータ取得を自動化するという前提の話ではあるのですが、普通にExcelで開くとデータが壊れてしまう場合の一時的な対策としても使えるネタです。

Excelブックとして開く

まずはよく見かけるExcelブックとして開くパターンです。普通にCSVファイルをExcelで開くのと同じ結果です。

Sub Excelブックとして開く()
    Dim openFileName As String  '開くCSVファイル名
    
    '対象のファイル名を指定
    openFileName = ThisWorkbook.Path & "/hoge.csv"
    
    '指定した名前のファイルが見つからなければ選択して開く
    If Dir(openFileName) = "" Then
        openFileName = Application.GetOpenFilename("CSV（コンマ区切り）, *.csv, テキスト（タブ区切り）, *.txt, すべてのファイル, *.*")
        If openFileName = "False" Then
            Exit Sub
        End If
    End If

    Workbooks.Open openFileName
End Sub

ファイルの特定方法として２パターン用意しています。

まずはこのマクロが記録されているExcelブックと同じフォルダ内に「hoge.csv」があれば自動的にそのCSVファイルを開きます。

もし見つからなければ「ファイルを開く」ウィンドウを表示して、手動で選んで開きます。

今回使用するファイルは「Revolver.csv」ですので上図のウィンドウから選んで開きます。

ここから３パターン紹介しますが、いずれもこの共通方式です。

開きました。特に何も語ることのないExcelの画面です。

注意したいのが１列目のトラック。あえて文字列形式で「01, 02, 03…」と１桁の数字には頭に0を付けていました。

Excelブックで開くとこの0が取れてしまっています。厳密に言えば勝手に数値扱いされてしまっています。

ちなみに、Excelで開いて上書き保存すると、元のデータも0が消えてしまって元通りにできなくなります。こういうことが起こるためCSVファイルの既定アプリが標準でExcelに設定されていることを嫌う人が多いです。

今回のサンプルではトラック番号なので別に数値でも問題ありませんが、他にも文字列として扱ってほしいのに数値や日付などの別の書式に変えられて元データを破壊する問題は枚挙に暇がありません。

LineInputで開く

Excelブックとして直接CSVファイルを開いてしまうから指定する間もなくデータを破壊されてしまうわけで、まずは一旦テキストファイルとしてデータを読み取り、こちらの意図する形式でセルに入力するというやり方で対処ができます。

Sub LineInputで開く()
    Dim openFileName As String  '開くCSVファイル名
    Dim strLine As String       'CSVファイルの１行
    Dim strArray() As String    'カンマで分割した値を格納する配列
    Dim inputStr As String      'セルに入力する文字列
    Dim targetRow As Long       '入力する行
    
    '対象のファイル名を指定
    openFileName = ThisWorkbook.Path & "/hoge.csv"
    
    '指定した名前のファイルが見つからなければ選択して開く
    If Dir(openFileName) = "" Then
        openFileName = Application.GetOpenFilename("CSV（コンマ区切り）, *.csv, テキスト（タブ区切り）, *.txt, すべてのファイル, *.*")
        If openFileName = "False" Then
            Exit Sub
        End If
    End If
    
    'ファイルを「1」として開く
    Open openFileName For Input As #1
    
    '各行を「,」で分割しデータを取得
    targetRow = 1
    Do Until EOF(1)
        Line Input #1, strLine
        If strLine <> "" Then
            strArray = Split(strLine, ",")
            For i = 0 To UBound(strArray)
                inputStr = ""
                inputStr = Replace(strArray(i), """", "")    'inputStrに値を格納し、"(ダブルクォート)を取り除く
                
                'トラックNoであれば文字列で扱うため頭に'(シングルクォート)を付ける
                If i = 0 Then
                    inputStr = "'" & inputStr
                End If
                
                'セルに入力する
                Cells(targetRow, i + 1).Value = inputStr
            Next i
        End If
        
        '入力行の１行進める
        targetRow = targetRow + 1
    Loop
    
    '「1」を閉じる
    Close #1
End Sub

CSVファイルのデータを開いているアクティブなシートに転記するマクロです。

トラックの文字列はそのまま入力すると数値になってしまいますが、Excelは頭に「’ (シングルクォート)」を付けると強制的に文字列として扱えるので、VBA上で「’」を付けてから入力するようにしました。

しかし別の問題が発生してしまいました。05の「Here, There and Everywhere」が曲名の途中で列が分かれてしまっています。

この方法は「,」で区切るので、文字列の中に「,」が含まれていると意図しないところで区切ってしまいます。つまり「Here, There and Everywhere」の中に含まれる「,」を区切りと誤認識してしまっているというわけです。

TAB記号区切りに変換してTAB記号で分割する

文字列に「,」が含まれるデータを取り扱う場合は、「”」で囲まれた中の「,」はそのままで、「”」の外の「,」は別の文字に置換し、置換した文字でSplit関数を行うという方法で対処ができます。

Sub LineInputで開く()
    Dim openFileName As String  '開くCSVファイル名
    Dim strLine As String       'CSVファイルの１行
    Dim strArray() As String    'カンマで分割した値を格納する配列
    Dim inputStr As String           'セルに入力する文字列
    Dim targetRow As Long       '入力する行
    
    '対象のファイル名を指定
    openFileName = ThisWorkbook.Path & "/hoge.csv"
    
    '指定した名前のファイルが見つからなければ選択して開く
    If Dir(openFileName) = "" Then
        openFileName = Application.GetOpenFilename("CSV（コンマ区切り）, *.csv, テキスト（タブ区切り）, *.txt, すべてのファイル, *.*")
        If openFileName = "False" Then
            Exit Sub
        End If
    End If
    
    'ファイルを「1」として開く
    Open openFileName For Input As #1
    
    '各行を「,」で分割しデータを取得
    targetRow = 1
    Do Until EOF(1)
        Line Input #1, strLine
        If strLine <> "" Then
            strArray = Split(CSVtoTSV(strLine), vbTab)
            For i = 0 To UBound(strArray)
                inputStr = ""
                inputStr = Replace(strArray(i), """", "")    'inputStrに値を格納し、"(ダブルクォート)を取り除く
                
                'トラックNoであれば文字列で扱うため頭に'(シングルクォート)を付ける
                If i = 0 Then
                    inputStr = "'" & inputStr
                End If
                
                'セルに入力する
                Cells(targetRow, i + 1).Value = inputStr
            Next i
        End If
        
        '入力行の１行進める
        targetRow = targetRow + 1
    Loop
    
    '「1」を閉じる
    Close #1
End Sub

Function CSVtoTSV(ByVal str As String) As String
    Dim strTemp As String
    Dim quotCount As Long
    Dim l As Long
    Dim str As String
 
    For l = 1 To Len(str)
        strTemp = Mid(str, l, 1)
        If strTemp = """" Then
            quotCount = quotCount + 1
        ElseIf strTemp = "," Then
            If quotCount Mod 2 = 0 Then
                str = Left(str, l - 1) & vbTab & Right(str, Len(str) - l)
            End If
        End If
    Next l
    CSVtoTSV = str
End Function

CSVtoTSVという関数を作成し、「”」に囲まれていない「,」をタブ記号(vbTab) に置換します。つまりCSVからTSVに変換するというわけです。

エクセルVBAでデータにカンマが含まれてしまっているCSVを取り込む

エクセルVBAで様々なタイプのCSVを取り込んでいきます。今回は、意外と多いパターンであるデータの中にカンマが含まれている場合のCSVをエクセルVBAで取り込む方法についてお伝えしていきます。

この方法はこちらの記事を参考にしたのですが、参考元では「: (コロン)」に置換する方法でした。これだと「:」を含む文字列があると同じことが起きてしまいますので、滅多に使われないであろうタブ記号にしたというわけです。

これで問題ありませんね。

UTF-8のCSVファイルをADODB.Streamで開く

Excelで扱える文字コードはShift_JISでして、最近よく使われるUTF-8のテキストファイルをExcelで開くと文字化けします。

ここまでのサンプルファイルはShift_JISでしたが、これをUTF-8に変換して上記のマクロで開くとこんな感じになります。

日本語で記述している１行目とボーカルが文字化けしてしまいました。

対策としてはCSVファイルをShift_JIS、もしくはUTF-8(BOM付き)で保存し直すというやり方ですが、これを毎回するのは手間です。

UTF-8のテキストファイルをExcelで使うために、ADODB.Streamを使ってCSVファイルを開きます。

Sub ADODBStreamで開く()
    Dim openFileName As String  '開くCSVファイル名
    Dim adoSt As Object         'オブジェクト
    Dim strBuf As String        'オブジェクトから取得したテキストを格納
    Dim strLine() As String     'CSVファイルの１行
    Dim strArray() As String    'カンマで分割した値を格納する配列
    Dim inputStr As String      'セルに入力する文字列
    
    '対象のファイル名を指定
    openFileName = ThisWorkbook.Path & "/hoge.csv"
    
    '指定した名前のファイルが見つからなければ選択して開く
    If Dir(openFileName) = "" Then
        openFileName = Application.GetOpenFilename("CSV（コンマ区切り）, *.csv, テキスト（タブ区切り）, *.txt, すべてのファイル, *.*")
        If openFileName = "False" Then
            Exit Sub
        End If
    End If
    
    'ADODV.Streamに格納
    Set adoSt = CreateObject("ADODB.Stream")
    With adoSt
        .Charset = "UTF-8"
        .Open
        .LoadFromFile openFileName
        strBuf = .ReadText
        .Close
    End With
     
    '1行ずつ処理を行う
    strLine = Split(strBuf, vbCrLf)
    For i = 0 To UBound(strLine)
        If strLine(i) <> "" Then
            strArray = Split(CSVtoTSV(strLine(i)), vbTab)
            For j = 0 To UBound(strArray)
                inputStr = ""
                inputStr = Replace(strArray(j), """", "")    'inputStrに値を格納し、"(ダブルクォート)を取り除く
                
                'トラックNoであれば文字列で扱うため頭に'(シングルクォート)を付ける
                If j = 0 Then
                    inputStr = "'" & inputStr
                End If
                
                'セルに入力する
                Cells(i + 1, j + 1).Value = inputStr
                
                targetRow = targetRow + 1
            Next j
        End If
    Next i
End Sub

Function CSVtoTSV(ByVal str As String) As String
    Dim strTemp As String
    Dim quotCount As Long
    Dim l As Long
    Dim str As String
 
    For l = 1 To Len(str)
        strTemp = Mid(str, l, 1)
        If strTemp = """" Then
            quotCount = quotCount + 1
        ElseIf strTemp = "," Then
            If quotCount Mod 2 = 0 Then
                str = Left(str, l - 1) & vbTab & Right(str, Len(str) - l)
            End If
        End If
    Next l
    CSVtoTSV = str
End Function

ADODB.Streamでも数字だけの文字列を数値に変換しちゃったり、文字列中の「,」で区切ってしまうのでその対策をしています。

あとADODB.Streamを使用する際に「Microsoft ActiveX Data Objects 2.8 Library」を有効にする必要と認識していたのですが、どうやら記述方法によって異なるようです。

'Microsoft ActiveX Data Objects 2.8 Library 設定不要
Dim adoSt As Object
Set adoSt = CreateObject("ADODB.Stream")

'Microsoft ActiveX Data Objects 2.8 Library 設定必要
Dim adoSt As New ADODB.Stream

一応Microsoft ActiveX Data Objects 2.8 Libraryの設定方法も載せておきます。

VBEを開いてメニューのツールから参照設定を開きます。

参照可能なライブラリファイルの中から「Microsoft ActiveX Data Objects 2.8 Library」を探してチェックを入れておきます。

ただし、この方法はMacではMicrosoft ActiveX Data Objects 2.8 Libraryがないので使用できません。やはりOfficeを使った事務作業はMacではなくWindowsを使った方がいいですね。

OpenTextで区切り文字と文字コードを指定してブックとして開く

ADODB.Streamが使えないMacでは、OpenTextを使用する方法があります。

Sub OpenTextを使用して開く()
    Dim openFileName As String  '開くCSVファイル名
    
    '対象のファイル名を指定
    openFileName = ThisWorkbook.Path & "/hoge.csv"
    
    '指定した名前のファイルが見つからなければ選択して開く
    If Dir(openFileName) = "" Then
        openFileName = Application.GetOpenFilename("テキスト（タブ区切り）,*.txt, CSV（コンマ区切り）, *.csv, すべてのファイル, *.*")
        If openFileName = "False" Then
            Exit Sub
        End If
    End If
    
    '引数で文字コードと区切り文字を指定して開く
    Call Workbooks.OpenText(openFileName, Origin:=65001, Comma:=True)
End Sub

OpenTextの第２引数で文字コード、第３引数で区切り文字を設定します。

文字コード	値
Shift_JIS	932
UTF-8	65001
UTF-16	1200

区切り文字	値
カンマ	Comma:=True
タブ	Tab:=True
セミコロン	Semicolon:=True
スペース	Space:=True
その他	Other:=True, OtherChar:=”hoge”

ただし、この方法はUTF-8のCSVファイルをTXTファイルかUTF-8(BOM付き)に変換しないと文字化けしてしまいます。さらにブックで開くので数字のみの文字列を数値と認識してしまう不具合がつきまといます。

変換する手間をかけてまでやることではないので、他に手段がない場合の妥協案ぐらいに考えた方がいいかもしれません。