CSVのデータを扱うとき、次のようにブックとして開いていませんか。
Sub Sample1()
Workbooks.Open "C:\Data\Sample.csv"
End Sub
CSVファイルはアイコンの絵がExcelでかつ、ダブルクリックするとExcelが起動して開かれます。そのことから「CSVファイルはExcelのデータファイル」と勘違いしているユーザーも多いですが、CSV形式のファイルは単なるテキスト形式のファイルです。
CSV形式は、Excelが登場するはるか以前の、MS-DOS時代から使われていた汎用のファイル形式です。決して、Excel専用のデータ形式などではありません。
Sample1のように、CSVファイルをブックとして開くと「001」が「1」になったり、「2-1」が「2月1日」のシリアル値に変換されるなど、自動変換機能が働いて読み込みと同時にデータが変換されてしまいます。
こうした問題に頭を悩ます声をたびたび聞きますが、そもそもCSV形式ファイルをブックとして開くことが原因です。
CSVファイルはテキスト形式ですから、テキストファイルとして扱えば、自由に操作できます。
テキストファイルからデータを読み取るには、
(1)テキストファイルを開く(Openステートメント)
(2)1行分のデータを読み込む(Line Inputステートメント)
(3)読み込んだデータをセルに代入する
(4)開いたファイルを閉じる(Closeステートメント)
という流れで処理します。
■テキストファイルを開く
テキストファイルを開くOpenステートメントは、ブックを開くWorkbooks.Openメソッドとは違います。
Excelなどのアプリケーションで対象のファイルを読み込むのではなく、いわば、ファイルを管理しているWindows(OS)に対して「使用許可」を得るようなものです。
Openステートメントの書式は次のとおりです。
Open ファイル名 For 目的 As 番号
ファイル名にパスを指定しないと、カレントフォルダが対象になるので、できるだけパスを指定した方が良いでしょう。
「目的」には、そのファイルに対して何をするかを指定します。
Input:ファイルから読み込む
Output:ファイルに上書きする
Append:ファイルに追記する
もし間違った「目的」を指定すると「ファイルモードが不正です」というエラーが発生するので、エラーになったら正しく直してください。
ただし、Outputと「書き込む」目的で開いたファイルに「Line Input」を実行すると、エラーになると同時に、ファイルのデータが消えてしまうので注意してください。
「番号」には数値を指定します。
たいていは「#1」を指定します。複数のファイルを同時に開く場合は、もちろん数値が重複してはいけません。
そのため、現在使用可能な「番号」を返すFreeFile関数もありますが、一般的にはファイルを10個も20個も開くことは希です。
たいていは1つしか開きませんので「#1」と覚えておけばいいでしょう。
Openしたファイルはこれ以降その番号で特定します。
そのとき「1」と数値だけ指定するときと「#1」とナンバー記号をつけて指定するときの2通りがあります。
たとえば、Line Input #1 は、#をつけないとエラーになります。
EOF(1) は、#をつけるとエラーになります。
Close #1 は、#をつけてもつけなくても正常に動作します。
どの命令で#が必須かは、使っていれば自然と覚えます。エラーになったら修正してください。
■データを読み込む
さて、Openステートメントで開いたファイルから1行分のデータを読み込むにはLine Inputステートメントを使います。
Line Inputステートメントの書式は次のとおりです。
Line Input #番号, 変数
Line Inputステートメントは、読み込んだデータを必ず変数に格納します。
Line Input #1, Range("A1")
のように、直接セルに代入することはできません。
Line Inputステートメントは、改行コードまで1行分のデータを読み込みます。
読み込むと、読み込みポイントが次行に移ります。読み込みポイントがファイルの終端に達するまでLine Inputステートメントを実行すれば、すべてのデータを読み込むことができます。
これには、Do Loopを使います。
Do 読み込みポイントがファイルの終端ではない間
Line Input #1, 変数
Loop
読み込みポイントがファイルの終端に達したかどうかは、EOF関数でわかります。
EOFは「End Of File」の頭文字です。EOF関数は、引数にファイルの番号を指定すると、そのファイルの読み込みポイントが「終端に達している」ときTrueを返します。
Do Until EOF(1)
Line Input #1, 変数
Loop
これで、CSVファイルの全データを「1行ずつ」読み込むことができます。
読み込みが終わったら、最後にファイルを閉じます。
Close #1
ここまでをまとめると、次のようになります。
Sub Sample2()
Dim buf As String
Open "C:\Data\Sample.csv" For Input As #1
Do Until EOF(1)
Line Input #1, buf
''読み込んだデータをセルに代入する
Loop
Close #1
End Sub
CSVファイルの実体は、Excel専用のデータ形式ではなく、単なるテキストファイルです。
CSVファイルをブックとして開くと「001」が「1」に自動変換されてしまうなどの問題があるので、CSVは「テキストファイルとして開いて」自分でセルに代入すると良いでしょう。
■読み込んだデータをワークシートに出力する
では、読み込んだ1行分のデータをカンマで分割して、各セルに代入する動作を考えてみましょう。ここでは次のような1行分のデータを例にして解説を進めます。
---- Sample.csv-----
001,モグタン,平成12年1月10日,冬眠はしない
--------------------
ある文字列が、ある文字で区切られているとき、文字列を区切り文字で分割してくれるのがSplit関数です。
Split(文字列, 区切り文字)
CSVデータの区切り文字はカンマ(,)なので、次のように指定します。
Split(文字列, ",")
Split関数は、分割した各データを配列形式で返します。
しかし、その配列の要素数は事前にわからないことが多いので、受け取る変数はバリアント型で宣言しておくのが一般的です。
Dim tmp As Variant
tmp = Split(文字列, ",")
文字列が「001,モグタン, 平成12年1月10日,冬眠はしない」のときは、
tmp(0):001
tmp(1):モグタン
tmp(2):平成12年1月10日
tmp(3):冬眠はしない
という配列になります。配列の先頭が0から始まる点に留意してください。
このような一次元配列を「横方向のセル範囲」に代入するときは、次のように一括代入が可能です。
Sub Sample3()
Dim tmp As Variant
tmp = Split("001,モグタン,平成12年1月10日,冬眠はしない", ",")
Range("A1:D1").Value = tmp
End Sub
このとき、代入するセル範囲(ここではRange("A1:D1"))と、配列の要素数を一致させるのがポイントです。
配列の要素数はUbound関数で取得できるので、次のように書くことも可能です。
Sub Sample4()
Dim tmp As Variant
tmp = Split("001,モグタン,平成12年1月10日,冬眠はしない", ",")
Range("A1").Resize(1, UBound(tmp) + 1).Value = tmp
End Sub
しかし、ここはあえて、次のように1セルずつ代入する方法で話を進めましょう。
また、このように、対象のセルを行と列で指定する場合は、RangeではなくCellsを使うのがセオリーです。
Sub Sample5()
Dim tmp As Variant
tmp = Split("001,モグタン,平成12年1月10日,冬眠はしない", ",")
Cells(1, 1).Value = tmp(0)
Cells(1, 2).Value = tmp(1)
Cells(1, 3).Value = tmp(2)
Cells(1, 4).Value = tmp(3)
End Sub
Cellsで指定する行位置を変数とし、1ずつ増加すれば、CSVデータをセルに分割して代入することができますね。まとめると、こんな感じです。
Sub Sample6()
Dim buf As String, tmp As Variant, n As Long
Open "C:\Data\Sample.csv" For Input As #1
Do Until EOF(1)
Line Input #1, buf
tmp = Split(buf, ",")
n = n + 1
Cells(n, 1).Value = tmp(0)
Cells(n, 2).Value = tmp(1)
Cells(n, 3).Value = tmp(2)
Cells(n, 4).Value = tmp(3)
Loop
Close #1
End Sub
しかし、これでは相変わらず「001」が「1」になってしまいます。
「平成12年1月10日」はシリアル値ではなく文字列として代入されてしまいます。
こうした問題には、代入するセルを個別に操作します。
たとえば「001」を「001」のまま代入するには、セルの表示形式を文字列にしてから代入します。
また、「平成12年1月10日」をシリアル値にするにはDateValue関数を使います。
Sub Sample7()
Dim buf As String, tmp As Variant, n As Long
Open "C:\Data\Sample.csv" For Input As #1
Do Until EOF(1)
Line Input #1, buf
tmp = Split(buf, ",")
n = n + 1
With Cells(n, 1)
.NumberFormat = "@"
.Value = tmp(0)
End With
Cells(n, 2).Value = tmp(1)
Cells(n, 3).Value = DateValue(tmp(2))
Cells(n, 4).Value = tmp(3)
Loop
Close #1
End Sub
このように、個々のデータに対して、好みの設定をすることで、どんなCSVデータであっても、望む形式でワークシートに取り込むことができます。
● 補足 ●
Line Inputステートメントは、CR(キャリッジリターン)+ LF(ラインフィールド)コード、またはCRコードのみで改行したテキストファイルは1行ずつ読むことができますが、LFコードのみで改行したテキストファイルは1行ずつ読み込むことができません。
LFコードで改行したテキストファイルを読む方法については「LFコードで改行したファイルを読み込む」を参照してください。