CSVを開くマクロの作り方
Excel VBAでCSVファイルを扱いたい場面はよくあります。売上データの取込、システムから出力された一覧の読込、ほかのツールとのデータ連携など、CSVはとても身近なファイル形式です。ただ、実際にVBAで扱おうとすると、思ったより簡単ではありません。単純にカンマで区切ればよさそうに見えるのですが、CSVの項目の中にカンマが入っていたり、ダブルクォーテーションで囲まれていたり、さらには改行が入っていたりすることがあるからです。
たとえば住所や備考欄のような項目では、値の中にカンマや改行が含まれることがあります。こうしたCSVを自前で1行ずつ読んで、Splitでカンマ区切りにするような作りにしてしまうと、列ずれやデータ崩れの原因になります。そこで今回は、Excelがもともと持っているCSVの読込機能をVBAから使って、できるだけ安全にCSVファイルを開くマクロを作る方法を紹介します。
ポイントは、ダブルクォーテーションで囲まれた項目や、項目内にカンマ・改行が含まれているCSVでも扱いやすいようにしているところです。まずは完成版のコードを載せて、そのあとで各部分を細かく解説していきます。
まずは完成版のコード
Sub CSVファイルを開いて表示する()
Dim filePath As Variant
Dim ws As Worksheet
Dim startCell As Range
Dim rng As Range
filePath = Application.GetOpenFilename("CSVファイル (*.csv), *.csv", , "CSVファイルを選択してください")
If filePath = False Then
MsgBox "キャンセルされました。"
Exit Sub
End If
Set ws = ThisWorkbook.Sheets(1)
Set startCell = ws.Range("A1")
With ws.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=startCell)
.TextFilePlatform = 65001
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileConsecutiveDelimiter = False
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.Refresh BackgroundQuery:=False
.Delete
End With
Set rng = ws.UsedRange
rng.Replace What:=vbCrLf, Replacement:="", LookAt:=xlPart
rng.Replace What:=vbLf, Replacement:="", LookAt:=xlPart
rng.Replace What:=vbCr, Replacement:="", LookAt:=xlPart
MsgBox "CSVファイルを読み込みました。"
End Sub
このマクロでできること
このマクロでは、CSVファイルを選択して、指定したシートの指定した位置から内容を読み込めるようにしています。しかも、単純なカンマ区切りだけではなく、ダブルクォーテーションで囲まれた項目にも対応できるようにしています。そのため、値の中にカンマが入っているデータでも、1つの項目として読み込みやすくなります。
さらに、項目の中に改行が含まれているCSVについても、まずはExcelに正しく解釈させ、そのあとでセル内改行だけを削除しています。CSVの途中で雑に改行を消すと、1レコードの途中で行が切れてしまうことがあるのですが、この方法ならそのリスクを減らせます。
また、読み込み開始位置をA1以外に変えることもできます。CSVの貼り付け位置を表の途中にしたいときや、既存の見出しの下に読み込みたいときにも応用しやすい作りです。
CSVファイルを選ぶ処理
最初に行っているのは、読み込むCSVファイルを選択する処理です。
filePath = Application.GetOpenFilename("CSVファイル (*.csv), *.csv", , "CSVファイルを選択してください")
Application.GetOpenFilenameを使うと、Excel上でファイル選択ダイアログを表示できます。今回はCSVファイルだけを選べるように、拡張子を*.csvに限定しています。これなら、ユーザーが毎回パスを手入力しなくても、対象のCSVを選んで読み込めます。
続いて、キャンセルされた場合の処理も入れています。
If filePath = False Then
MsgBox "キャンセルされました。"
Exit Sub
End If
ファイル選択でキャンセルされると、filePathにはFalseが入ります。そのまま後続処理へ進むとエラーの原因になるので、ここで終了させています。こういう小さな分岐を入れておくと、実際に使うマクロとして安定しやすくなります。
貼り付け先のシートと開始位置を指定する
次に、CSVを読み込む先のシートと開始位置を決めています。
Set ws = ThisWorkbook.Sheets(1)
Set startCell = ws.Range("A1")
wsは読み込み先のワークシートです。今回はこのブックの1枚目のシートを指定していますが、シート名で指定してもかまいません。たとえばSet ws = ThisWorkbook.Sheets("取込用")のようにすれば、特定のシートに固定して読み込めます。
startCellはCSVの貼り付け開始位置です。今回はA1を指定していますが、B3やD5に変えることもできます。たとえば次のようにすれば、B3から読込が始まります。
Set startCell = ws.Range("B3")このように変数で開始位置を持っておくと、あとでコードを読み返したときにも意図がわかりやすくなります。CSVの取込位置を変えたいだけなら、基本的にはこの部分を修正すれば対応できます。
CSVを正しく読み込む処理
この記事の中心になるのが、CSVを読み込むこの部分です。
With ws.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=startCell)
.TextFilePlatform = 65001
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileConsecutiveDelimiter = False
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.Refresh BackgroundQuery:=False
.Delete
End WithここではQueryTables.Addを使っています。これはExcelが持っているテキストファイル読込機能をVBAから利用するための書き方です。自分で1行ずつ読んで解析するのではなく、ExcelにCSVとして解釈させているのが大きなポイントです。
Connection:="TEXT;" & filePathの部分で、読み込む対象がテキストファイルであることを指定しています。そしてDestination:=startCellで、読み込んだ内容をどのセルから配置するかを指定しています。
最後の.Deleteは、CSV読込に使ったQueryTableを処理後に削除するためのものです。これをしておかないと、シート上に外部データ接続の情報が残ることがあります。単発の読込マクロとして使うなら、読み込み後に削除しておくほうがすっきりします。
ダブルクォーテーション付きの項目にも対応できる理由
CSVを扱うときに特に大事なのが、項目内にカンマが含まれているケースです。たとえば、次のようなCSVがあったとします。
社員番号,氏名,住所
1001,山田太郎,"東京都,新宿区"住所の値にはカンマが入っていますが、このカンマは区切りではなく値そのものです。そのため、項目全体がダブルクォーテーションで囲まれています。CSVとしてはよくある正しい書き方です。
もしこれを単純にSplit(line, ",")で処理すると、東京都と新宿区で別の列に分かれてしまいます。つまり、見た目は3項目でも、機械的には4つに割れてしまうのです。
今回のマクロでは、次の設定によってこの問題に対応しています。
.TextFileCommaDelimiter = True
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileCommaDelimiter = Trueで区切り文字がカンマであることを指定し、.TextFileTextQualifier = xlTextQualifierDoubleQuoteでダブルクォーテーションに囲まれた部分を特別扱いするようにしています。これによって、ダブルクォーテーションの内側のカンマは、列の区切りではなく1つの値の一部として扱われます。
つまり、CSVはただのカンマ区切りテキストではなく、ダブルクォーテーションも含めて解釈しないと正しく扱えないということです。この部分をExcel側に任せているのが、今回のやり方の強みです。
項目内に改行があっても扱える理由
もう1つの難所が、項目の中に改行が入っているCSVです。たとえば備考欄や説明文のような項目では、次のようなデータになることがあります。
商品コード,備考
1001,"1行目
2行目"見た目には2行ありますが、CSVのルールとしてはこの全体が1レコードの1項目です。ダブルクォーテーションの内側にある改行は、レコード区切りではなく、値の一部として扱われます。
ここでやってはいけないのが、ファイルを1行ずつ読んで処理することです。たとえばLine Inputで読んだり、改行で分割したりすると、本来1つの項目であるデータが途中で切れてしまいます。
今回のマクロでは、CSVの解析そのものをExcelに任せています。そのため、ダブルクォーテーションの中にある改行も1つのセルの内容として読み込みやすくなります。つまり、まずは正しく取り込むことを優先して、そのあとで見た目を整えるという順番にしています。
読み込んだあとの改行を削除する処理
CSVを正しく読み込んだあとで、セル内の改行だけを削除しているのが次の部分です。
Set rng = ws.UsedRange
rng.Replace What:=vbCrLf, Replacement:="", LookAt:=xlPart
rng.Replace What:=vbLf, Replacement:="", LookAt:=xlPart
rng.Replace What:=vbCr, Replacement:="", LookAt:=xlPart
ws.UsedRangeで、シート上で使われている範囲を対象にしています。そしてReplaceで改行コードを空文字に置き換えています。改行コードには環境やデータの作られ方によって複数の種類があるため、vbCrLf、vbLf、vbCrをそれぞれ置換しています。
ここで大事なのは、CSVファイルの途中で改行を消しているのではなく、読み込まれたセルの中の改行だけを消している点です。CSVの構造を壊さずに、最終的な表示だけを整える流れになっています。
一覧表として見やすくしたいだけなら、このやり方のほうが安全です。CSVを読み込む段階で改行を無視しようとすると、レコードの切れ目と項目内改行の区別ができず、かえって危険です。
改行を削除ではなくスペースにする方法
改行を完全に削除すると、もともと2行に分かれていた文字がそのままくっついてしまうことがあります。たとえば「株式会社ABC」と「営業部」という2行だったデータが、「株式会社ABC営業部」になってしまうと、少し読みにくい場合があります。
そんなときは、改行を削除するのではなく、半角スペースに置き換える方法もあります。
rng.Replace What:=vbCrLf, Replacement:=" ", LookAt:=xlPart
rng.Replace What:=vbLf, Replacement:=" ", LookAt:=xlPart
rng.Replace What:=vbCr, Replacement:=" ", LookAt:=xlPart人が目で確認する一覧表として使うなら、こちらのほうが自然なケースも多いです。逆に、後続処理で空白が邪魔になるなら空文字のままでもかまいません。CSVを何のために読み込むかによって、ここは調整してよい部分です。
文字化けするときの確認ポイント
CSV読込では、区切りや改行だけでなく文字コードにも注意が必要です。今回のコードでは次の設定を入れています。
.TextFilePlatform = 65001
これはUTF-8のCSVを想定した設定です。最近はUTF-8で出力されるCSVも増えているため、まずはこの指定で問題ないことが多いと思います。ただし、古いWindows系のシステムや既存業務システムから出力されたCSVでは、Shift_JISで保存されていることもあります。
その場合は、次のように変更します。
.TextFilePlatform = 932
CSVを読み込んだときに日本語が文字化けしているなら、まずはここを疑うとよいでしょう。区切りの設定が正しくても、文字コードが違っていれば内容は正しく読めません。
CSVを扱うときに気をつけたいこと
今回のマクロでは、ダブルクォーテーション付きの項目や、項目内のカンマ・改行が入ったCSVでも読み込めるようにしています。ただ、実務でCSVを扱うときは、読み込めることだけで安心してはいけません。大事なのは、そのあと別のシステムへ渡すときにも、同じ値を正しく1項目として出力できるかどうかです。
たとえば「東京都,新宿区」という値を1項目として取り込めたとしても、次にCSVへ書き出すときにダブルクォーテーションを付けずにそのまま出してしまえば、受け取る側では2項目と解釈される可能性があります。つまり、入口だけでなく出口でもCSVのルールを守らないといけません。
仕様上は、CSVにカンマや改行を値として含めることは可能です。しかし、連携先のシステムがそこまで厳密にCSVを扱っていないことも多く、特に項目内改行はトラブルの元になりやすいです。そのため、実務ではそもそも値の中に改行を入れない、カンマを別記号に置き換える、列を分けるといった設計を選ぶことも少なくありません。
CSVは見た目が単純なので軽く扱われがちですが、実際には「どこまで厳密にCSVとして解釈するか」で結果が変わってきます。だからこそ、自前で雑に処理するよりも、まずはExcelの機能を活用して正しく取り込む考え方が大切だと思います。
まとめ
VBAでCSVファイルを開くマクロを作るときは、単純にカンマで分割するだけでは足りません。CSVには、ダブルクォーテーションで囲まれた項目や、項目内にカンマや改行が含まれるケースがあるからです。こうしたデータを安全に扱うには、CSVをただの文字列として見るのではなく、CSVのルールに従って解釈する必要があります。
今回紹介したマクロでは、QueryTables.Addを使ってExcelにCSVとして読み込ませることで、ダブルクォーテーション付きの項目や改行入りの項目にも対応しやすくしています。そして、読込後にセル内改行だけを削除することで、一覧表として使いやすい形にも整えています。
CSVは単純そうで意外と奥が深い形式です。だからこそ、読み込み処理を自前で雑に作るのではなく、Excelが持っている機能を活かして、なるべく壊れにくい方法で扱うのが実務では大切だと感じます。CSVファイルを扱うマクロを書くときは、ぜひ今回の考え方をベースにしてみてください。
