MS Excel のCSV読み込み不具合対策

勝手な解釈をするExcel

MS Excel でCSVファイルを読み込むと、データの内容が勝手に変更されてしまう場合があります。例えば、「007」という数字3文字の文字列が「7」に変わってしまったりします。つまり、それらしいデータを勝手に数字や日付として認識し、標準の書式で表示してしまうようです。一度誤認識されて読み込まれたデータは、もうExcel上では元のデータに修正できません。

この不具合に対する対策として、メニューの「データ」→「外部データの取り込み」で、各項目を文字列に設定して読み込むという方法があります。しかし、頻繁にCSVファイルを利用している人にとっては、毎回こんなことをするのは面倒です。そこで、VBAによるアドインで何とかならないものかと検討してみました。

CSVの全項目を文字列として読み込む

明示的にExcelに各項目を文字列として読み込ませることができれば、「007」が「7」に変換されることなく、そのまま「007」と読み込まれるはずです。CSVファイルを開いたとき、全項目を文字列として読み直すプログラムをVBAを使って作ってみました。次のプログラムを「Cl1」という名前でクラスモジュールとして作成しました。

Option Explicit

Public WithEvents App As Application

'===== CSVファイルを開いた時に実行確認 =====
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    If UCase(Right(Wb.Name, 4)) = ".CSV" Then
        If MsgBox("全項目を文字列として読み込みますか？", vbYesNo, "CSV読み込み") = vbYes Then
            LoadCsvS Wb.Name
        End If
    End If
End Sub

'===== CSVファイル読込み(全項目を文字列として) =====
Private Sub LoadCsvS(fname As String)
    Dim fpname As String
    Dim i As Long, j As Long, k As Long
    Dim hln As String
    Dim hi As Variant, hi1 As Variant
    Dim cc As String
    Dim qflg As Boolean
    Dim dat(256) As String  ' 列数の最大値は256(Office XP)

    fpname = Application.ActiveWorkbook.Path & "\" & fname
    Open fpname For Input As #1    

    i = 0
    Do Until EOF(1)
        hln = ""
        qflg = False
        Do Until EOF(1)
            cc = Input(1, #1)

            If qflg = True Then
                ' "..."内の文字列の処理
                If cc = """" Then
                    qflg = False
                ElseIf cc = vbCr Then
                    cc = ""
                End If
                hln = hln & cc
            Else
                ' "..."内以外の文字列の処理
                If cc = vbCr Or cc = vbLf Or cc = vbCrLf Then
                    If hln <> "" Then Exit Do  ' 1行読込み終了
                ElseIf cc = "," Then
                    hln = hln & vbBack
                ElseIf cc = """" Then
                    ' "..."内の""かどうか判別
                    k = Len(hln)
                    If k < 1 Then
                        qflg = True
                        hln = hln & cc
                    ElseIf Right(hln, 1) <> """" Then
                        qflg = True
                        hln = hln & cc
                    End If
                Else
                    hln = hln & cc
                End If
            End If
        Loop            

        ' vbBackを区切りとして分解

        hi = Split(hln, vbBack, -1, vbTextCompare)    

        j = 0
        For Each hi1 In hi    

            ' 文字列両端のダブルクォーテーションを消す
            If Left(hi1, 1) = """" And Right(hi1, 1) = """" Then
                hi1 = Mid(hi1, 2, Len(hi1) - 2)
            End If    

            dat(j) = hi1
            j = j + 1
        Next        

        ' シートに1行表示
        i = i + 1
        Rows(i).NumberFormatLocal = "@"
        Rows(i).Value = dat
        Erase dat
    Loop

    Close #1
End Sub

このプログラムが、CSVファイルを開いたときに動作するように、標準モジュールに次のように書いておきました。

Option Explicit

Dim acl1 As New Cl1

'===== アドイン読込時に自動実行 =====
Sub Auto_Open()
    Set acl1.App = Application
End Sub

上記クラスモジュールと標準モジュールを含むアドインを作成し、Excelで利用できるように設定すれば準備完了です。アドインの作成方法はExcelのヘルプ等で調べられます。

アドインの設定をすると、ExcelでCSVファイルを開いた時に「全項目を文字列として読み込みますか？」と尋ねてきますので、「はい」を選ぶと全項目のデータを文字列として読み直します。これで不要な変換を防ぐことができます。

注意！

上記のプログラムを流用するのは自由ですが、無保証でありサポート対象外ですので、ご注意ください。