マクロのある暮らし(9回目) - CSVを扱う
こんにちは！なるーらぼです！
4月に入りましたが、雨がよく降りますね。
お花見は行きましたか？なるーらぼは行きましたよ！8分咲きくらいでしたが、良いものですね。
CSV
新年度も始まりますと、データを操作するようなことが増えますよね（増えないと思いますけど）。そんなとき頻繁に遭遇するのがCSVです。
CSVってカンマで区切られた「なるーらぼ,2016/4/4,9,マクロ」こんなものです。ExcelでCSV形式に保存すると「"なるーらぼ","2016/4/4","9","マクロ"」という感じで保存されます。
とても便利な形式で、ExcelがインストールされているとダブルクリックするだけでExcelの表として開いてくれますし、メモ帳なんかで開くと上記のような形式のテキストファイルでもあります。ということで、Excelとは切っても切り離すことのできないものです。
そしてあまりに頻繁に利用されるため、マクロを利用する理由のほとんどがこれなんじゃないだろうかさえ思ってしまいます。
CSVを使ってみる
一番簡単なのはさきほども言ったようにダブルクリックして開く、ですよね。
例えば以下のようなCSVを用意してみました。
これをExcelで開くとこうなります。
ではマクロから扱ってみましょう。
Excelマクロから扱う場合は上記のようにブックとして開くことができますので「Workbooks.Open」を利用すれば難なく開くことができます。
Sub note_mu()
Const csvname As String = "nalulabo.csv"
Dim csvpath As String
csvpath = Environ("UserProfile") & "\Documents\" & csvname
With Workbooks.Open(csvpath)
Debug.Print .Worksheets(1).Range("A2").Value
.Close False
End With
End Sub
上記のコードで「セルを参照する」というのがイミディエイトウィンドウに出力されます。ブックとして開くためRangeオブジェクトなども利用することができますし、さらにExcel形式へ保存しなおすこともできます。その際には書式設定したりグラフ化したりすることもできるので便利ですね！もちろんセルのコピー＆ペーストすることもできるのでまとまったセルの範囲を別ブックなどへ貼り付けることもできますよ。
テキストファイルとして使ってみる
次はExcelではない、WordやAccessなどから扱う場合です。
この時にはテキストファイルとして扱ったほうが便利なことも多いです。
テキストファイルとして扱うには少し面倒なエラーへの対処が必要になりますが、汎用的です。
マクロからテキストファイルを扱うには大きく分けて「Openステートメント」を利用する方法とCOMを利用する方法の2種類があります。まずはOpenステートメントを扱う方法です。
ちょっと不思議な概念が登場するので、もしかすると今まで使っていた方もウェブ上の情報からまるまるコピーしていた方も多いかもしれません。
Sub note_mu()
Const csvname As String = "nalulabo.csv"
Dim csvpath As String
Dim line As String
Dim fno As Integer
Dim csv As Variant
csvpath = Environ("UserProfile") & "\Documents\" & csvname
fno = FreeFile
Open csvpath For Input Access Read As #fno
Do Until EOF(fno)
Line Input #fno , line
csv = Split(line, ",")
Debug.Print csv(0)
Loop
Close #fno
End Sub
大事なのは「fno = FreeFile」というところです。「FreeFile関数」は組み込みの関数で、マクロで扱うことのできるファイルの空き番号を探してくれる関数です。ファイルを開くときにはいずれ閉じますし、書き込んだり読み込んだりするのでどのファイルなのかをはっきりと知っておく必要があるのです。それをVBAは番号で管理していますが、全部で511個までしか開くことができません。順次閉じていくとしてもどの番号が空いたかを探さないと管理している番号で別のファイルを開くことができません。わけがわからなくなるためです。ですからこの関数を使ってどの番号で開くべきなのかを調べているのです。
といっても上記の例では１つだけなので、必ず１が返されます：）
あとはこの番号を利用して読み込みをします。Openステートメントはファイルのパス、どのモードで開くか（上記は入力、Inputとしてあります）、データのアクセス方法（上記は先頭から読み込み、Readとしてあります）、そしてファイル番号というようにしていきます。
あとは「EOF関数」が真になるまで繰り返せばファイルの最後まで読み込むことができます。「EOF」というのは「End Of File」の略称で、ファイルの最後、ということですね。
そして最後に必ずCloseステートメントでファイルを閉じてください。閉じないと、ずっとファイルが開いたままになってしまいます。ほかのアプリから書き込んだりができなくなりますので、閉じましょう。もしも実行時にエラーが発生したときのために、開いているときのエラー処理ラベルで必ず閉じるようにしてください。
Sub note_mu()
Const csvname As String = "nalulabo.csv"
Dim csvpath As String
Dim line As String
Dim fno As Integer
Dim csv As Variant
csvpath = Environ("UserProfile") & "\Documents\" & csvname
fno = FreeFile
'' ファイルが存在しなかったら終了する
If Dir(csvpath) = "" Then
GoTo Normal_End
End If
Open csvpath For Input Access Read As #fno
'' 読み込みでエラーなら読み込みエラーへ
On Error GoTo ReadError
Do Until EOF(fno)
Line Input #fno , line
csv = Split(line, ",")
Debug.Print csv(0)
Loop
ReadError:
If Err.Number <> 0 Then
MsgBox "読み込みのときにエラーが発生しました。" _
& vbCrLf & Err.Description
End If
FileClose:
Close #fno
Normal_End:
Application.StatusBar = "終了しました。"
DoEvents
Application.Wait _
TimeSerial(Hour(Now), Minute(Now), Second(Now) + 3)
Application.StatusBar = False
DoEvents
End Sub
テキストファイルとして扱う（COMで）
では次はCOMから利用してみましょう。メニューの「ツール」＞「参照設定」から「Microsoft Scripting Rumtime」にチェックを入れて「OK」をクリックします。これでCOMからテキストファイルを扱うことができるようになります。
Sub note_mu()
Const csvname As String = "nalulabo.csv"
Dim csvpath As String
Dim csv As Variant
Dim fso As New FileSystemObject
Dim ts As TextStream
csvpath = Environ("UserProfile") & "\Documents\" & csvname
'' ファイルが存在しなかったら終了する
If Not fso.FileExists(csvpath) Then
GoTo Normal_End
End If
Set ts = fso.OpenTextFile(csvpath, ForReading)
'' 読み込みでエラーなら読み込みエラーへ
On Error GoTo ReadError
Do Until ts.AtEndOfLine
csv = Split(ts.ReadLine, ",")
Debug.Print csv(0)
Loop
ReadError:
If Err.Number <> 0 Then
MsgBox "読み込みのときにエラーが発生しました。" _
& vbCrLf & Err.Description
End If
FileClose:
ts.Close
Set ts = Nothing
Normal_End:
Set fso = Nothing
Application.StatusBar = "終了しました。"
DoEvents
Application.Wait _
TimeSerial(Hour(Now), Minute(Now), Second(Now) + 3)
Application.StatusBar = False
DoEvents
End Sub
「FileSystemObject」はファイル操作のためのオブジェクトで、ファイルを開いたりコピーや名前変更などいろいろ操作することができます。ファイルがあるかどうかを「FileExists」メソッドという具体的なものでチェックすることができ、これは真偽値を返すので直観的な操作をすることができます。
ファイルを開くにも「OpenTextFile」というものがあってファイル番号を探さなくてもよろしくやってくれるというところが便利なところです。また読み込みも行単位の「ReadLine」、指定しただけ読み込む「Read」、ファイル全体を一度に読み込む「ReadAll」と複数の方法が用意されているので扱いやすいです。
Excelで保存したCSVの扱い
Excelで保存したCSVをメモ帳などで開いたことがあるとわかるのですが、ダブルクォーテーション「"」で各データが囲まれています。これはここまでご覧いただいた方法だとダブルクォーテーションで囲まれたままになってしまうということがあります。不便ですね。さらに恐ろしいのはExcelではせるにゅ力するときにAltキーを押しながらEnterキーを押すことで改行を含めることができます。こうした改行が入ってしまうとテキストファイルとして扱うのは非常に大変になってしまいます。だって、一行ずつ読み込むことができなくなるからです！カンマなどの区切り文字で区切って配列でデータにアクセスしようとしても、改行が入っているデータを読んでしまうと想定していないデータの数になるので実行時エラーが発生します。これはこまった…
もちろんブックとして開くことができたら、まったく問題ないのですが。
では別の方法をご提示します。それはすこしデータベースの知識が必要になりますが、CSVファイルをデータベースとして扱うという方法です。
Sub note_mu()
Const csvname As String = "nalulabo.csv"
Dim csvpath As String
Dim csv As Variant
Dim fso As New FileSystemObject
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
csvpath = Environ("UserProfile") & "\Documents\" & csvname
'' ファイルが存在しなかったら終了する
If Not fso.FileExists(csvpath) Then
GoTo Normal_End
End If
With db
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") _
= Text;HDR=YES;FMT=Delimited"
.Properties("Data Source") _
= Environ("UserProfile") & "\Documents\"
.Open
End With
On Error GoTo ReadError
Set rs = db.Execute("select * from " & csvname)
Do Until rs.EOF
Debug.Print rs(rs.Fields(0).Name).Value
rs.MoveNext
Loop
ReadError:
If Err.Number <> 0 Then
MsgBox "読み込みでエラーが発生しました。" _
& vbCrLf & Err.Description
End If
CloseConnection:
rs.Close
db.Close
Normal_End:
Set fso = Nothing
Set rs = Nothing
Set db = Nothing
Application.StatusBar = "終了しました。"
DoEvents
Application.Wait _
TimeSerial(Hour(Now), Minute(Now), Second(Now) + 3)
Application.StatusBar = False
DoEvents
End Sub
かなり長くなりましたが、上記の例で大事なところは「With db」からのブロックです。ここでデータプロバイダやファイルの形式などを指定しています。
これによってCSV形式のテキストファイルをデータベースとみなして参照することができます。
データの参照は「db.Execute」メソッドで行っています。続く文字列のことをSQLと呼びます。これはデータをデータベースから取り出したり更新したりするときに利用するものです。ここでは「nalulabo.csvファイルからすべての列を取り出す」という内容になっています。もしも条件をつけて取り出すのであれば、「Where 項目名 条件」という感じに記述することでフィルタをかけることもできます。
こうして取り出されたデータはレコードセットと呼ばれるオブジェクトになります。ただ、１つ１つのデータを参照していくには先頭からになるので次の行へ移動するには「MoveNext」メソッドを利用してください。
また、レコードセットもファイルへの接続も切断する必要があります。必ずCloseメソッドを呼び出して切断してください。
ちょっと面倒ですが、でも難しいことを考えなくても項目中に改行のあるデータも扱うことができるようになります！
最後に
今回はCSVについてご覧いただきました。便利で作りやすいデータなのにで、あまりにも頻繁に登場するのですが、扱うとなるといろいろと気にしなければならないことも多いのは確かです。
どんなデータ形式なのか（Excelで保存した形式なのか、とか）、またどんな用途に利用するのかによって扱い方は選択するようにしてください。
Excelで利用するのであればブックとして扱うのが最も簡単です。CSVファイルの特定の列だけを抜き出して別のCSVファイルを作るのであればテキスト形式で扱うのが便利かもしれません。さらに、フィルターしながら使いたい場合にはデータベースとして扱うのがよいでしょう。
ということで、今回はこのあたりで失礼します。
良い一日を！
この記事が気に入ったらサポートをしてみませんか？