Excel for MacでBOM無しUTF-8なCSVファイルをインポートする

Excel の外部データ取り込み機能でテキストファイルをインポートする際、文字コードが UTF-8 で BOM(Byte Order Mark, Unicodeの符号化情報を示すファイル先頭データ)が付いてない所謂 UTF-8N 形式のCSVファイルが文字化けしてしまい、うまくインポート出来ない問題にぶち当たる事がある。

一旦テキストエディタ等で UTF-8N のCSVファイルを開き、BOM付UTF-8やSJIS形式で保存し直した後に Excel でインポートすれば解決するのだが、金融取引の履歴ファイルなど定常的にインポートする作業ではこの手間を何とか省きたいものだ。

Excel for Mac の VBAマクロでこれを自動化しようと試みたが、Windows版であれば ADO(ActiveX Data Objects) を参照する事でBOM追加やSJIS変換が出来るらしいものの、Mac版ではこれが使えず、調べた範囲ではこれと言った代替策も見当たらない。

そこで UTF-8N 決め打ちで BOM を追加し、改行文字を LF から CRLF に変換してからインポートするマクロを頑張って作ってみた。

・MacOS 12.4 Monterey
・Microsoft Excel for Mac 2019

Sub ImportUtf8nCSV() OriginalCsvFilePath = "[変換元ファイルパス(UTF-8N)]" '日本語入るとNG ConvertedCsvFilePath = "[変換先ファイルパス(UTF-8)]" '日本語入るとNG '変換元ファイルをバイナリで読み込み Dim byteArray() As Byte Open OriginalCsvFilePath For Binary Access Read As #1 ReDim byteArray(LOF(1)) byteArray = InputB(LOF(1), 1) Close #1 'UTF-8用のBOM Dim BomArray(0 To 2) As Byte BomArray(0) = &HEF: BomArray(1) = &HBB: BomArray(2) = &HBF '変換先書込み用変数 Dim bw As Byte, cr As Byte cr = &HD '変換先ファイルに書き出し Open ConvertedCsvFilePath For Binary Access Write As #2 Put #2, , BomArray 'BOMの書き込み For Each b In byteArray 'CSV内容の書き込み bw = b If (bw = &HA) Then Put #2, , cr 'LFの前にCRを挿入 Put #2, , bw Next b Close #2 '表示中のワークシートに変換先ファイルを読み込み QueryTablesTarget = "TEXT;" + ConvertedCsvFilePath With ActiveSheet.QueryTables.Add(Connection:=QueryTablesTarget, _ Destination:=Range("$A$1")) .AdjustColumnWidth = False '列幅を自動調整しない .PreserveFormatting = True 'シートの書式を維持する .RefreshStyle = xlOverwriteCells 'シートに上書きして読み込む .SaveData = False .TextFilePlatform = 65001 '文字コードを UTF-8 に設定 .TextFileStartRow = 1 '1行目から読み込む .TextFileParseType = xlDelimited '区切り記号付きで読み込み .TextFileCommaDelimiter = True '区切り文字がコンマ .Refresh BackgroundQuery:=False '読み込み実行 .Delete 'データ接続を削除 End With End Sub

マクロから操作対象ファイル/ディレクトリを最初に開く際には MacOS のセキュリティ機能でアクセスの許可が求められるので、ファイル/フォルダを選択してアクセス権を付与しておく必要がある。

Put文でByte値を直接指定すると何故か0x00(&H0)が追加された2バイトで書かれてしまうので、Byte型で宣言した変数 bw や cr を使っている。またデータ接続先がワークシートに残らないよう、読込実行後には接続に使った QueryTables を削除している。

UTF-8NのCSVファイルにBOMが付けば Excel から文字化け無く開く事が出来るが、QueryTables を使って表示中のワークシートに直接読み込むには、改行変換(LF→CRLF)も行う必要があった。
（LF改行の場合「このテキストファイルのデータは、1 つのワークシートに入りきりません。」と警告される）