Excelであれば通常そのままCSVファイルを直接開くことができますが、VBAのLine Inputステートメントを使用してCSVファイルのデータを一行ずつ読み込んでワークシートの各セルに読み込んだデータを書き込む方法についての覚書です。
CSVファイルを読み込む際に個々のデータに何らかの加工をした上でExcelに展開したかったり、同じ構造を持つ複数のCSVファイルを一つの表としてExcelに書き込ませたいときなどに使いまわしているプログラム紹介します。
サンプルプログラムには、[ファイルを開く]ダイアログボックスを表示して任意のファイルを指定した上で読み込むサンプル、[フォルダ選択]ダイアログボックスを表示して指定した任意のフォルダ内にある全てのCSVファイルを連続して読み込むサンプルプログラムも紹介します。
- CSVファイルをVBAを使用して読み込む方法について
- Line Inputステートメントを使用したCSVファイルの読み込み基本サンプルプログラム
- CSVファイルから読み込んだデータをワークシートのセルに書き出すサンプルプログラム
- [ファイルを開く]ダイアログボックスを表示して任意のCSVファイルを指定してセルに書き出すサンプルプログラム
- 指定した任意のフォルダ内にある全てのCSVファイルを読み込んでセルに書き出すサンプルプログラム
- 最初に読み込んだファイルの項目名のみ残して他のファイルの項目行(1行目)はスキップするサンプルプログラム
- CSVファイルの中身を一行ずつワークシートのセルに書き出すVBAのサンプルプログラムまとめ
CSVファイルをVBAを使用して読み込む方法について
CSVファイルに限らずテキスト形式のファイルを読み込む際には主に2つの方法があります。
Line Inputステートメントを使用する
VBAの標準ファイルI/O(Input、Output)として用意されているLine Inputステートメントを使用してファイルを開き、一行ずつ読み込みます。
FileSystemObjectのReadLineを使用する
ExcelからWindows Script Hostオブジェクトを参照して、FileSystemObjectオブジェクトの機能を借りてテキストファイルに対する処理を実行します。
Line Inputと比べるとファイルの内容をいっぺんに読み込めたり、行のスキップができたりなど使い勝手が良いです。
今回のサンプルプログラムは、Line Inputステートメントを使用しています。
FileSystemObjectを使用したサンプルは別の記事で紹介したいと思います。
Line Inputステートメントを使用したCSVファイルの読み込み基本サンプルプログラム
以下にLine Inputステートメントを使用した基本的なサンプルプログラムを紹介します。
イミディエイトウィンドウにデータを出力するので、[表示]メニューの[イミディエイト ウィンドウ]をクリックしてイミディエイト ウィンドウを表示しておいてください。
Sub Sample01()
Dim intFileNo As Integer, strFileName As String
Dim strTextLine
'ファイル番号を取得
intFileNo = FreeFile
'開きたいファイルのフルパスを変数に代入する
strFileName = "C:¥Temp¥社員.csv"
'ファイルを開く
Open strFileName For Input As #intFileNo
'ファイルの終端(End Of File)まで処理を繰り返す
Do Until EOF(intFileNo)
'Line Inputステートメントを使用して1行分データを読み込む
Line Input #intFileNo, strTextLine
'イミディエイトウィンドウに読み込んだ1行分のデータを出力
Debug.Print strTextLine
Loop
Close #intFileNo
End Sub
サンプルプログラムの説明
Line Inputステートメントを使用してファイルを開いてデータを読み込む際には、必ず以下の情報が必要となります。
- ファイル名(フルパス) 開きたいファイルのフルパス名です。
- ファイル番号
ファイル番号について
プログラムから開くファイルに対して一意の識別番号を割り当てる必要があります。
特定の数値を直接割り当てることもできますが、サンプルプログラムでは開いている番号を自動で割り当ててくれるFreeFile関数を使用しています。
ファイルに割り当てた識別番号は、そのファイルが閉じるまで有効となります。
サンプルプログラムでは、上記の情報を全て変数に割り当てています。
サンプルプログラムの流れとしては以下のとおりです。
- OpenステートメントでFreeFile関数によって割り当てられたファイル番号を使用してファイルを開く
- Line Inputステートメントにより開いたCSVファイルの1行目がstrTextLine変数に代入される Line Inputが実行されるたびに1行ずつ読み込まれます。
- 変数strTextLineに読み込まれた1行分のデータをイミディエイト ウィンドウに出力する
- 2～3の処理をファイルの終端まで繰り返す
- ファイルの終端まで来たら繰り返し処理(Do Until Loop)を抜けてファイルを閉じる
サンプルプログラムのポイント
Line Inputステートメントを使用する上でのポイントです。
- Openステートメントを使用してファイルを開く際にファイルを識別するための番号を付与する必要がある。
- Line Inputステートメントではファイルに付与された識別番号を指定する。
- Line Inputステートメントが実行されたタイミングで指定した変数に1行分のデータ(先頭から改行コードまで)が代入される。
- Line Inputステートメントを実行するごとに1行ずつ行が進んでいくので、明示的に次の行に進む命令を実行する必要はないし、そもそもそのような命令もない。
- Line Inputステートメントでは行をスキップしたり、指定した行番号から読み込むというような指定はできない。 ただし、別の方法で行をスキップや指定した行数から読み込みを開始したり、条件に合致した時だけ読み込ませることは可能。
- 開いたファイルに付与した識別番号は、そのファイルが閉じるまで保持されるため、必要なくなったら明示的に必ずファイルを閉じるのがエチケット。
CSVファイルから読み込んだデータをワークシートのセルに書き出すサンプルプログラム
以下にSample01プログラムを利用して読み込んだデータをワークシートのセルに書き出すサンプルプログラムを紹介します。
Sample02
Sub Sample02()
Dim intFileNo As Integer, strFileName As String
Dim strTextLine, arrData, i As Integer
intFileNo = FreeFile
strFileName = "C:¥Temp¥社員.csv"
'書き出しを開始するセルの選択
ThisWorkbook.Worksheets("Sheet1").Range("A1").Select
Open strFileName For Input As #intFileNo
Do Until EOF(intFileNo)
Line Input #intFileNo, strTextLine
'","を区切り記号として読み込んだ1行分のデータを
'分解して配列として取り込む
arrData = Split(strTextLine, ",")
'配列の1つ1つの要素値をセルに横方向に書き出す
For i = 0 To UBound(arrData)
ActiveCell.Offset(0, i).Value = arrData(i)
Next i
'1行分のデータをセルに書き出したら次の行を
'書き出すためにExcel側も行を下に移動させておく
ActiveCell.Offset(1, 0).Select
Loop
Close #intFileNo
End Sub
Sample02のポイント
読み込んだ1行分のデータをセルに書き出す際のポイントは、1行分のデータを","を区切り記号として個々のデータとして分解し、配列として変数に代入する点と配列をForループを使ってセルに書き出している点です。
Forループの部分をもう少し詳しく説明します。
変数strTextLineには、Line Inputステートメントによって1行分のデータが入っています。
例えばこんな感じです。
strTextLine = A,B,C
それを","(カンマ)区切りで分解して配列として変数arrDataに代入しているのでarrDataの中身を表すとこんな感じです。
arrData(0) = A arrData(1) = B arrData(2) = C
※配列の要素の開始番号は、明示的内定がなければ0からとなります。
Forループで使用されているUbound関数は、配列の要素番号の最大値を返す関数です。 上の例で言えば"2"を返します。
Forループの"i = 0 To Ubound(arrData)"は、arrData配列の0番から2番まで、つまり配列の要素数分繰り返すことを意味しています。
ActiveCell.Offset(行,列) は、現在アクティブなセルから指定された数値分、行方向、列方向分ずらしたセルを示します。
つまり、Forループで i の値は0、1、2と変化していくので以下のようになります。
i = 0 の時
ActiveCell.Offset(0, 0).Value = arrData(0) となり、現在アクティブなセルにarrData(0)の値が書き込まれる。
i = 1 の時
ActiveCell.Offset(0, 1).Value = arrData(1) となり、現在アクティブなセルの1列右のセルにarrData(1)の値が書き込まれる。
i = 2 の時
ActiveCell.Offset(0, 2).Value = arrData(2) となり、現在アクティブなセルの2列右のセルにarrData(2)の値が書き込まれる。
","を区切りとして1行分のデータが分解され、各値がセルの横方向に1つ1つ書き出されていきます。
1行分のデータのセルへの書き出しが終わったら(Forループを抜けたら)次の行を書き出す開始位置として、今アクティブなセルを1行下にずらすためにActiveCell.Offset(1, 0).Select を実行しています。
少しわかりづらいかもしれませんが、ウォッチ式で各変数の値を見ながらプログラムをステップ実行すると理解しやすくなると思います。
[ファイルを開く]ダイアログボックスを表示して任意のCSVファイルを指定してセルに書き出すサンプルプログラム
Sample02を更に改良して、プログラム実行時に[ファイルを開く]ダイアログボックスを表示して任意のCSVファイルを選択できるようにしたサンプルプログラムをいかに紹介します。
Sample03
Sub Sample03()
Dim intFileNo As Integer, strFileName As String
Dim strTextLine, arrData, i As Integer
'CSVファイルを選択する[ファイルを開く]ダイアログボックスを表示し、
'選択されたファイルを変数に代入
strFileName = Application.GetOpenFilename("CSVファイル,*.csv")
'キャンセル時はプログラムを終了する
If strFileName = "False" Then
MsgBox "キャンセルがクリックされました。", vbInformation
Exit Sub
End If
intFileNo = FreeFile
ThisWorkbook.Worksheets("Sheet1").Range("A1").Select
Open strFileName For Input As #intFileNo
Do Until EOF(intFileNo)
Line Input #intFileNo, strTextLine
arrData = Split(strTextLine, ",")
For i = 0 To UBound(arrData)
ActiveCell.Offset(0, i).Value = arrData(i)
Next i
ActiveCell.Offset(1, 0).Select
Loop
Close #intFileNo
End Sub
Sample03のポイント
Sample02では開きたいCSVファイルをプログラム中で直接変数に代入していましたが、Sample03ではGetOpenFilenameメソッドを使用して[ファイルを開く]ダイアログボックスを表示し、[ファイルを開く]ダイアログボックスで選択されたファイルを変数に代入するように変更しています。
また、[ファイルを開く]ダイアログボックスで[キャンセル]ボタンが押された時は処理を終了するようにIf文が追加されています。
GetOpenFilenameメソッドを使用して[ファイルを開く]ダイアログボックスを表示する方法についての詳細は以下の記事を参照してください。
指定した任意のフォルダ内にある全てのCSVファイルを読み込んでセルに書き出すサンプルプログラム
指定した任意のフォルダに保存されている複数のCSVファイルを全て読み込んでセルに書き出すサンプルプログラムを紹介します。
サンプルプログラムではFileDialogオブジェクトを使用しますが、このオブジェクトはExcelに標準で存在するオブジェクトではないため、以下の設定を事前に行ってください。
オブジェクトライブラリの参照設定
サンプルプログラムで使用するFileDialogを使用する場合、以下のオブジェクトライブラリを参照設定しておく必要があります。
Microsoft Office 15.0 Object Library
※15.0というバージョンは、Excelのバージョンによって異なります。
オブジェクトライブラリの参照手順
以下に「Microsoft Office 15.0 Object Library」の参照手順を記載します。
- Excelのリボンツールバーの[開発]タブをクリックする。
- [開発]タブの右端にある[Visual Basic]をクリックする。
- [Microsoft Visual Basic for Applications]画面(VBAエディタ)が開くので[ツール]メニューの[参照設定]をクリックする。
- [参照可能なライブラリ ファイル]の一覧(五十音順に並んでいます)から「Microsoft Office 15.0 Object Library」のチェックをオンにして[OK]をクリックする。
Sample04
Sub Sample04()
Dim intFileNo As Integer, strFileName As String
Dim strTextLine, arrData, i As Integer
Dim dlg As Object, dlgAns As Boolean, strFolderPath As String
'フォルダを指定する[参照]ダイアログボックスの表示
Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
dlgAns = dlg.Show
'[キャンセル]時は処理を終了
If dlgAns = False Then
Exit Sub
End If
'選択されたフォルダのパスを取得し"¥"を付加
strFolderPath = dlg.SelectedItems(1) & "¥"
ThisWorkbook.Worksheets("Sheet1").Range("A1").Select
'Dir関数を使用して指定されたフォルダ内のCSVファイル名を
'順番に取得
strFileName = Dir(strFolderPath & "*.csv")
'Dir関数の結果が""(CSVファイルがもうない)まで処理を繰り返す
Do Until strFileName = ""
intFileNo = FreeFile
'フォルダのパスとDir関数で取得したファイル名を結合して
'開くファイルをフルパスで指定する
Open strFolderPath & strFileName For Input As #intFileNo
Do Until EOF(intFileNo)
Line Input #intFileNo, strTextLine
arrData = Split(strTextLine, ",")
For i = 0 To UBound(arrData)
ActiveCell.Offset(0, i).Value = arrData(i)
Next i
ActiveCell.Offset(1, 0).Select
Loop
Close #intFileNo
'次に見つかったCSVファイルのファイル名を変数に代入
strFileName = Dir()
Loop
End Sub
Sample04のポイント
ポイントとしては、フォルダを任意に指定できる[参照]ダイアログボックスの表示とDir関数により指定されたフォルダに存在する全てのCSVファイルのファイル名を取得している点です。
Sample04の欠点
サンプルプログラムを実行するとすぐにわかると思いますが、当然ながら全てのCSVファイルの先頭行が読み込まれますので、先頭行が項目名になっている場合、読み込んだファイル分項目名もセルに書き込まれてしまいます。
なので、もう少し改造してみます。
最初に読み込んだファイルの項目名のみ残して他のファイルの項目行(1行目)はスキップするサンプルプログラム
Sample04を改造して最初のファイルの項目名のみ残して以降のファイルの項目名はスキップさせる処理を追加します。
Sample05
Sub Sample05()
Dim intFileNo As Integer, strFileName As String
Dim strTextLine, arrData, i As Integer
Dim dlg As Object, dlgAns As Boolean, strFolderPath As String
Dim fileCounter As Integer, lineCounter As Integer
Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
dlgAns = dlg.Show
If dlgAns = False Then
Exit Sub
End If
strFolderPath = dlg.SelectedItems(1) & "¥"
ThisWorkbook.Worksheets("Sheet1").Range("A1").Select
strFileName = Dir(strFolderPath & "*.csv")
'開いたファイル数をカウントする変数をリセット
fileCounter = 0
Do Until strFileName = ""
intFileNo = FreeFile
'読み込んだ行数をカウントする変数をリセット
lineCounter = 0
Open strFolderPath & strFileName For Input As #intFileNo
'ファイルが開かれるたびにファイル数をカウントする変数に1加算
fileCounter = fileCounter + 1
Do Until EOF(intFileNo)
Line Input #intFileNo, strTextLine
'行が読み込まれるたびに行数をカウントする変数に1加算
lineCounter = lineCounter + 1
'開いたファイルが2ファイル目以降でかつ、先頭行だった場合
'処理を行わず次の行を読み込む
If fileCounter > 1 And lineCounter = 1 Then
Line Input #intFileNo, strTextLine
End If
arrData = Split(strTextLine, ",")
For i = 0 To UBound(arrData)
ActiveCell.Offset(0, i).Value = arrData(i)
Next i
ActiveCell.Offset(1, 0).Select
Loop
Close #intFileNo
strFileName = Dir()
Loop
End Sub
Sample05のポイント
一番最初に開いたファイルの項目名だけはセルに書き出し、以降に開いたファイルの1行目にある項目名部分は読み込まないという条件を判断できるようにすることがポイントです。
一番最初に読み込んだファイルのなのか、2ファイル目以降なのかは、ファイルを開くたびに開いたファイル数をカウントする変数を用意してカウントさせます。
読み込む行も同じように今読み込んだ行が開いているファイル何行目なのかをカウントする変数を用意してカウントさせます。
後は、「開いているファイルが2ファイル目以降でかつ、先頭行の場合」という条件を2つのカウント用変数を使って判断させれば良いだけです。
開いているファイルが2ファイル目以降 :
fileCounter > 1
先頭行の場合 :
lineCounter = 1
つまり、
「If fileCounter > 1 And lineCounter = 1 Then」
となります。
1つ大事な点は、2つのカウント用の変数をリセット(初期化)するコードの挿入場所を間違えるとうまく動作しないので注意してください。
行のスキップについては、Line Inputを実行した時点で行を読み込むので、何も処理せずもう一度Line Inputを実行すれば次の行が読み込まれるのでスキップすることになります。
CSVファイルの中身を一行ずつワークシートのセルに書き出すVBAのサンプルプログラムまとめ
今回のサンプルプログラムは、ExcelのVBAの標準I/OのLine Inputステートメントについて、基本から徐々に改造して複数のファイルを1つのシートにまとめるまで作ってみました。
サンプルプログラムは、ExcelのVBAとして作っていますが、Line Input部分についてはAccessのVBAでも利用できますので参考にしてください。
コメント