Excel VBAでCSVファイルを開く方法

ExcelでCSVファイルを自動的に読み取るVBAマクロです。

先頭の0が消えるとか、数値の16桁以降が省略される、文字列の中にカンマが入っていて列がずれる、UTF-8の文字化けの対処もできます。

プログラムの条件

サンプルのCSVファイルです。ビートルズの『リボルバー』の曲リストです。

"トラック","タイトル","ボーカル"
"01","Taxman","ハリスン"
"02","Eleanor Rigby","マッカートニー"
"03","I'm Only Sleeping","レノン"
"04","Love You To","ハリスン"
"05","Here, There and Everywhere","マッカートニー"
"06","Yellow Submarine","スター"
"07","She Said She Said","レノン"
"08","Good Day Sunshine","マッカートニー"
"09","And Your Bird Can Sing","レノン"
"10","For No One","マッカートニー"
"11","Doctor Robert","レノン"
"12","I Want to Tell You","ハリスン"
"13","Got to Get You Into My Life","マッカートニー"
"14","Tomorrow Never Knows","レノン"

トラックNoの1桁台は頭に0が付き、5トラック目の「Here, There and Everywhere」は曲名の中にカンマが入っています。

いくつかパターンを紹介しますが、いずれも以下のような動作でCSVファイルを開きます。

マクロを実行するExcelブックと同じフォルダ内に「hoge.csv」があれば、そのCSVファイルに対して処理を実施
もし無ければ、ファイルを開くダイアログでユーザーがCSVファイルを選択する

Excelブックで開く

Excelブックで開きます。これはCSVを開くアプリケーションがExcelの場合に、普通にファイルをダブルクリックで開いた時と同じ動作です。

Sub Excelブックで開く()
    Dim openFileName As Variant  'ファイル名

    'ファイル名を指定
    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

    'Excelブックで開く
    Workbooks.Open openFileName
End Sub

Excelブックで開くの結果

マクロを実行したブックとは別に新規ブックとして開きます。以降の処理は何もブックを指定しなければ、アクティブになっているこのブックに対して行われます。

結果がわかりやすくていいのですが、トラックNoが「01」だったところが「1」になっています。数字だけのデータは文字列で入っていても数値として扱われてしまいます。

他のパターンですと、Excelは15桁までしか数値は入力できず、16桁以降は0に変換されてしまいます。クレジットカードナンバーは16桁ですので、数値変換されると不正確なデータになってしまいます。

LineInputで開く

そこでセルに数値として入力される前に、文字列として扱われるようにしてから入力します。

Sub LineInputで開く()
    Dim openFileName As String  'ファイル名
    Dim strLine As String       'CSVファイルの１行
    Dim strArray() As String    'カンマで分割した値を格納する配列
    Dim inputStr As String      'セルに入力する文字列
    Dim targetRow As Long       '入力する行番号
    Dim i As Integer            'カウンタ

    '対象のファイル名を指定
    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 = Replace(strArray(i), """", "")    'inputStrに値を格納し、"(ダブルクォート)を取り除く

                '0列目のTrackは頭にシングルクォートを付けて文字列として扱う
                If i = 0 Then
                    inputStr = "'" & inputStr
                End If

                'セルに入力する
                ActiveWorkbook.ActiveSheet.Cells(targetRow, i + 1).Value = inputStr
            Next i
        End If

        '入力行の１行進める
        targetRow = targetRow + 1
    Loop

    '「1」を閉じる
    Close #1
End Sub

LineInputで開いた場合、CSVファイルを1行ずつ処理することができ、Split関数を使ってカンマで分割すれば列ごとに処理を分けることができます。データを1件ずつ取り出して、処理を行った後にセルに入力していきます。

Trackの列については強制的に文字列として入力するために頭にシングルクォートを付ける処理をしています。

このように列ごとに処理を分けられますが、配列のインデックスは0から始まるので注意です。

LineInputで開くの結果

ただ、Split関数を使ってカンマで区切ったところ、「Here, There and Everywhere」の文字列の中のカンマで区切ってしまって分割されてしまいました。

CSVをTSVに変換

ダブルクォートで囲まれた中にあるカンマは値として、ダブルクォートの外にあるカンマはCSVの区切り記号として分割させたいところ。

そこでダブルクォートの外にあるカンマをタブ記号に変換し、Split関数ではタブ記号で分割するようにします。

カンマで分割する CSV (Comma-Separated Values) からタブ記号で分割する TSV (Tab-Separated Values) に変換してやります。

Sub TSVをLineInputで開く()
    Dim openFileName As String  'ファイル名
    Dim strLine As String       'CSVファイルの１行
    Dim strArray() As String    'カンマで分割した値を格納する配列
    Dim inputStr As String      'セルに入力する文字列
    Dim targetRow As Long       '入力する行番号
    Dim i As Integer            'カウンタ

    '対象のファイル名を指定
    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(CSVをTSVに変換(strLine), vbTab)
            For i = 0 To UBound(strArray)
                'inputStrに値を格納し、"(ダブルクォート)を取り除く
                inputStr = Replace(strArray(i), """", "")

                '0列目のTrackは頭にシングルクォートを付けて文字列として扱う
                If i = 0 Then
                    inputStr = "'" & inputStr
                End If

                'セルに入力する
                ActiveWorkbook.ActiveSheet.Cells(targetRow, i + 1).Value = inputStr
            Next i
        End If

        '入力行の１行進める
        targetRow = targetRow + 1
    Loop

    '「1」を閉じる
    Close #1
End Sub

Function CSVをTSVに変換(ByVal str As String) As String
    Dim strTemp As String
    Dim quotCount As Long
    Dim l As Long

    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
    CSVをTSVに変換 = str
End Function

「CSVをTSVに変換」という変換用の関数を作りCSVをTSVに変換、Split関数にはTSVに変換した値をvbTAB（タブ記号）で分割しています。

CSVをTSVに変換の結果

正常に表示することができました。

ADODB.StreamでUTF-8対応にする

UTF-8で文字化け

Excelで扱える文字コードはShift_JISで、ポピュラーなUTF-8のCSVファイルは日本語が文字化けします。

CSVファイルをShift_JISかUTF-8(BOM付き)に保存し直せば文字化けを避けられますが、いちいちそんなことしてられないのでVBA側で対処します。

Sub ADODB_Streamで開く()
    Dim openFileName As String  'ファイル名
    Dim adoSt As Object         'オブジェクト
    Dim strBuf As String        'オブジェクトから取得したテキストを格納
    Dim strLine() As String     'CSVファイルの１行
    Dim strArray() As String    'カンマで分割した値を格納する配列
    Dim inputStr As String      'セルに入力する文字列
    Dim i As Integer            'カウンタ
    Dim j As Integer            'カウンタ

    '対象のファイル名を指定
    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

    'ADODB.Streamに格納
    Set adoSt = CreateObject("ADODB.Stream")
    With adoSt
        .Charset = "UTF-8"
        .Open
        .LoadFromFile openFileName
        strBuf = .ReadText
        .Close
    End With

    strLine = Split(strBuf, vbCrLf)
    For i = 0 To UBound(strLine)
        If strLine(i) <> "" Then
            strArray = Split(CSVをTSVに変換(strLine(i)), vbTab)
            For j = 0 To UBound(strArray)
                'inputStrに値を格納し、"(ダブルクォート)を取り除く
                inputStr = Replace(strArray(j), """", "")

                '0列目のTrackは頭にシングルクォートを付けて文字列として扱う
                If j = 0 Then
                    inputStr = "'" & inputStr
                End If

                'セルに入力する
                ActiveWorkbook.ActiveSheet.Cells(i + 1, j + 1).Value = inputStr
            Next j
        End If
    Next i
End Sub

ADODB.Streamというオブジェクトを使用して、文字コードを変えて入出力することができます。

ただ、このオブジェクトはWindowsでしか使えず、Macでは非対応です。

OpenTextを使用する

ADODB.Streamが使えないMacでUTF-8のCSVファイルを開けない場合はOpenTextを使えば、Excelブックで開く時と同じように扱えます。

ただし、UTF-8のCSVファイルをそのままでは開けず、拡張子をtxtにするかUTF-8(BOM付き)にしておく必要があります。ここでは拡張子をtxtにしたパターンで行います。

Sub OpenTextで開く()
    Dim openFileName As String  '開くCSVファイル名

    '対象のファイル名を指定
    openFileName = ThisWorkbook.Path & "\hoge.txt"

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

    '引数で文字コードと区切り文字、文字列で表示する列を指定して開く
    Workbooks.OpenText Filename:=openFileName, Origin:=65001, Comma:=True, FieldInfo:=Array(Array(1, xlTextFormat))

End Sub

文字コードはOriginで指定します。

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

区切り文字もカンマ以外を指定することができますので、いろいろなテキストファイルで使えます。

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

FieldInfoでは列ごとにデータ型を指定することができます。配列Arrayに列番号とデータ型ごとの値を指定します。指定しない場合はxlGeneralFormatになります。

データ型	値
自動判定	xlGeneralFormat
文字列	xlTextFormat
YMD 日付形式	xlYMDFormat
読み込まない	xlSkipColumn