Excel VBAを使ったCSVファイル読み込み時の高速化、タブ区切り対応、ダブルクォーテーション削除のコツ：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、VBAを使ってCSVファイル読み込む際の基本的な考え方を解説します。

[薬師寺国安，PROJECT KySS]

印刷

通知 連載「VBA／マクロ便利Tips」の新着をメールで通知

見るPost

Shareシェア

はてなブックマーク

SharePocket Button

連載目次

※本Tipsの環境：Windows 10 64bit＋Anniversary Update、Excel 2016

　TIPS「FileSystemObjectを使うための準備――ファイル／フォルダをコピーするには」では、ファイルシステムへアクセスするメソッドなどを提供するFileSystemObjectやMicrosoft Scripting Runtimeの参照設定などを紹介したが、今回は、CSVデータを読み込む方法を解説する。

　CSVファイルで保存しておいたファイルは、そのファイルをダブルクリックするとExcelが起動してブックとして開くので、特にVBA読み込ませる必要もないように思えるかもしれない。しかしCSVファイルをブックとして開くと、自動変換機能によって、「003」という文字列が「3」という数値に変換されたり、「4-1」という文字列が「4月1日」のように日付として変換されたりしてしまうなどの問題が起こる。

　そこで本TIPSでは、VBAでCSVファイルをそのまま読み込むコードの一例を紹介するので、参考にしてほしい。

• CSVファイルを読み込む
• 区切り文字がタブ文字や半角スペースなどになっている場合
• カンマの数が決まっている場合
• 要素1つ1つが「"」（ダブルクォーテーション）で囲まれている場合

CSVファイルを読み込む

　今回は、リスト1のような「顧客データ.csv」ファイルを読み込む。

氏名,性別,年齢,住所,勤務先
遠藤蘭,男,20,大阪市中央区瓦町,グッズショップTORA
湯月団吾,男,64,松山市道後湯月町,夏目タルト店
打田筆人,男,30,大阪市北区中崎,Tigerバッティングセンター
城山最中,男,73,松山市勝山町,城山餅店
薬師寺国安,男,33,愛媛県宇和島市,PROJECT KySS
長崎蜜子,女,28,長崎市飽の浦町,カステラ一番
美味餅子,女,28,大分市生石,餅のカボス屋
投田秀人,男,40,東京都中央区日本橋,XMLスポーツ用品
牛尾闘魂,男,35,宇和島市新町,ステーキハウス闘牛
花田米,男,43,北宇和郡三間町大字宮野下,農業
鬼北伝承,男,53,北宇和郡広見町年則,鬼北神社宮司
清少納言,女,27,東京都千代田区,タレント
山田太郎,男,22,北海道札幌市,公務員
紫式部,女,20,東京都世田谷区,タレント

リスト1　顧客データ.csvファイル

　今回はSheet2にCSVファイルを読み込むのでSheet2を追加しておいてほしい。

　リスト2のコードを使う。

Sub CSVファイルの読み込み()
  Dim fso As New Scripting.FileSystemObject
  Dim csvFile As Object
  Dim csvData As String
  Dim splitcsvData As Variant
  Dim i As Integer
  Dim j As Integer
  
  Set csvFile = fso.OpenTextFile("K:\CSV_Data\顧客データ.csv", 1)
  i = 1
  Do While csvFile.AtEndOfStream = False 
    csvData = csvFile.ReadLine
    splitcsvData = Split(csvData, ",")
    j = UBound(splitcsvData) + 1
    Sheet2.Range(Sheet2.Cells(i, 1), Sheet2.Cells(i, j)).Value = splitcsvData
    i = i + 1
  Loop
 
  csvFile.Close
  Set csvFile = Nothing
  Set fso = Nothing
End Sub

リスト2　CSVファイルを読み込むコード

　新しいScripting.FileSyetemObjectのインスタンスを作成してfsoで参照する（2行目）。

　Object型のcsvFile変数、文字列型のcsvData変数、Variant型のsplitcsvData変数、Integer型の変数iとjを宣言する（3～7行目）。

　OpenTextFileメソッドで「K:\CSV_Data\顧客データ.csv」（リスト1）のデータを、読み込み専用で開く（9行目）。

　変数iを1で初期化しておき（10行目）、AtEndOfStreamプロパティ（11行目）を使うことで、ファイルの最後の行に達するまで、下記12～16行目の処理を繰り返すことができるようになる。

　ReadLineメソッドで読み込んだCSVファイルの1行を読み込む（12行目）。

　Split関数でカンマ（,）で区切られた各文字列を、（1次元）配列として返し、変数splitcsvDataに格納する（13行目）。

　UBound関数を使い、配列の要素の数を調べ、変数jに格納する。+1しているのは、配列の添え字は0から開始されるためだ（14行目）。

　Sheet2のi行目の1列目から、i行目のj列目までの範囲に、読み込んだCSVデータを表示する（15行目）。

　12行目のReadLineメソッドでは1行しか読み込まないので、変数iを加算して、行の末尾まで読み込ませる（16行目）。

　最後に、開いていたファイルをCloseメソッドで閉じ、オブジェクト変数をNothingでクリアする（19～21行目）。

　実行すると図1のようにExcelのSheet2にCSVファイルが読み込まれる。

図1　CSVファイルが読み込まれた

区切り文字がタブ文字や半角スペースなどになっている場合

　なおCSVとは、本来「Comma-Separated Values」の略称で、カンマ（,）で区切られた文字列が並んだテキストファイルのことだが、環境によっては、区切り文字がタブ文字や半角スペースなどになっていることもある。そのため、「Character-Separated Values」の略とすることもある。

氏名	性別	年齢	住所	勤務先
遠藤蘭	男	20	大阪市中央区瓦町	グッズショップTORA
湯月団吾	男	64	松山市道後湯月町	夏目タルト店
：
：

リスト3　顧客データ.csvファイル（タブ区切り）

　タブ文字や半角スペースになっている場合は、リスト2の13行目のコードを参考に、カンマ（,）をタブ文列や半角スペースに変えるなどで対応してほしい。

カンマの数が決まっている場合

　14行目のように、UBound関数を使うことで、カンマ（,）の数が行ごとに異なるファイルだった場合も対応できるが、カンマの数が決まっている場合は、変数jを宣言せず、jの代わりにカンマの数（リスト1の場合は、4）に+1にした数を使った方が、UBound関数を呼び出す分の処理時間を短縮できる。

　読み込むCSVファイルの行数によっては、処理時間が大きく変わってくるので、汎用性とのバランスを取る必要があるが、状況によって使い分けてほしい。

要素1つ1つが「"」（ダブルクォーテーション）で囲まれている場合

　下記のように要素1つ1つが「"」（ダブルクォーテーション）で囲まれている場合もある。「"330,440円"」のようにカンマが入った文字列が要素の1つになるデータもあるからだ。このような場合は、Replace関数で「"」を空白に置換して削除するなどで対応してほしい。

"氏名","性別","年齢","住所","勤務先"
"遠藤蘭","男","20","大阪市中央区瓦町","グッズショップTORA"
"湯月団吾","男","64","松山市道後湯月町","夏目タルト店"
：
：

リスト4　顧客データ.csvファイル（「"」～「"」あり）

　Replace関数を使うタイミングは、Split関数を使う前後に分かれる。

　「"330,440円"」のようにカンマが入った文字列が要素の1つになるデータがある場合は、Split関数を使った後にReplace関数を使わなければならない。汎用性を考えたら、Split関数を使った後のコードにしておいた方がいいだろう。しかし、15行目のように配列変数splitcsvDataを一気にRangeオブジェクトにしていた高速化テクニックが使えなくなる。

　そうではない場合は、Split関数を使う前（12行目）のcsvDataにReplace関数を使えるので、処理が高速になる。これも、UBound関数の解説のときに言った通り、汎用性と処理速度のバランスを考えて適宜使い分けてほしい。

次回は、指定したドライブの空き容量やファイルのタイムスタンプや属性の取得

　今回は、これで終わりだ。CSVファイルの読み込みは業務で使う場面が多いが、読者の環境によっては、今回紹介したように、区切り文字がタブ文字や半角スペースなどになっている場合、カンマの数が決まっている場合、要素1つ1つが「"」（ダブルクオーテーション）で囲まれている場合などの違いがあるだろう。さまざまな形式のCSVファイルを一気に読み込まなければならない場合もあるかもしれない。

　今回紹介したコードを状況によってカスタマイスして使いこなすことで対応してほしい。

　次回は、「指定したドライブの空き容量やファイルのタイムスタンプや属性の取得」について解説する。お楽しみに。

この連載を「連載記事アラート」に登録する New

著者紹介

薬師寺 国安（やくしじ くにやす）　／　薬師寺国安事務所

薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。

1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。

1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。

2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。

Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。

Microsoft MVP for Development Platforms - Client App Dev （Oct 2003-Sep 2012）。

Microsoft MVP for Development Platforms - Windows Phone Development（Oct 2012-Sep 2013）。

Microsoft MVP for Development Platforms - Client Development（Oct 2013-Sep 2014）。

Microsoft MVP for Development Platforms - Windows Platform Development (Oct 2014-Sep 2015）。