エクセルVBAで献立自動化、栄養士さんお助けツール、csvファイルと二次元配列

CSVファイルを取り込んで二次元配列を通してセル範囲に展開する
コード解説
まとめ
ダウンロード

CSVファイルを取り込んで二次元配列を通してセル範囲に展開する

■事業所管理の自動化でデータ操作の元になるCSVファイルを取扱うことは多くあります。

今回はCSVファイルをシートのセル範囲に取り込む処理を考えてみます。

■前提条件

今回は取扱うCSVファイルに文部科学省が作成している、「日本食品栄養成分表」を少し加工して使用します。（このCSVファイルを呼び出す側のエクセルファイルのファイルパスと同じフォルダーに置きます。）

■ねらう効果

このCSVファイルには、61列、2551行のデータがあります。取り扱うデータ量としてはあまり多くない分量だと思いますが、それでもシートにデータベースとして格納しておくにはスペースをとります。できればいちいちシートのセルにアクセスするより、配列に格納しておいてメモリへのアクセスのほうが効率がいいと思います。

今回は、このCSVファイルデータを二次元配列に取りこむ処理をやってみることにします。

■ExcelのSheet1に二個のボタンを貼り付けます。一つのボタンではCSVファイルをオープンして直接シートのセル範囲にダイレクトにデータを展開する処理を実行します。そして、もう一つのボタンでは一旦、二次元配列にデータを取り込んでから、そのデータ全体が入るセル範囲に一気に展開する処理を実行します。それぞれのボタンの下には処理時間を表示しています。

シートのデザイン

■赤枠8行目以降は読込んだCSVファイルの状態です。

■青枠二つのボタンは、同じデータを読み込む処理をしますが、右のボタンは配列を使用し、左は配列を使わないで処理をしています。

結論

■どちらの処理でも時間的には0.2～0.3秒程度でほどんど瞬間に処理が終わります。二次元配列のほうが若干時間がかかるようですが、その差は微間なものです。だったら処理のしやすさ、読みやすさの利便性を取ったほうがよさそうだという結果になりました。

■やってみればわかりますが配列を使わない場合は転記されるデータは数値も文字列として扱われるのでセルの左上に緑の三角がついて回るので表がものすごく見ずらいです。これを取る方法はあると思いますが、今はそこに時間を割きたくないので、このままにしておきます。配列に一旦取り込んだ場合の転記したデータは数値は数値として入るので特に問題は起きませんね。

コード解説

左側緑のボタン処理

■こちらのボタンでは二次元配列を使わず、Openしたファイルから一行ずつデータを取り出しておいて、その一行分のデータが入るシートのセル範囲を指定してデータを転送します。

Sub det_in2()
'For～Nextの繰り返し処理を使って
'１行分ずつセルにデータを書き出す
    Dim csvName As String
    Dim myDataLine As Variant
    Dim ccnt As Long
    Dim i As Long, j As Long
    Dim line As String
    Dim T As Variant
    T = Timer '0時からの時間を取得
    csvName = ActiveWorkbook.Path & "\seibuncsv.csv"
    Open csvName For Input As #1
    j = 0
    Application.ScreenUpdating = False
    Do Until EOF(1)
        Line Input #1, line
        myDataLine = Split(line, ",")
        ccnt = UBound(myDataLine)
        '一行分のデータを都度書き出す
        ActiveSheet.Range(Cells(j + 8, 1), Cells(j + 8, ccnt)).Value = myDataLine  '0.24秒
        j = j + 1
    Loop
    Application.ScreenUpdating = True
    Close #1
    '計測した時間を表示
    ActiveSheet.Cells(7, "G") = Timer - T & " 秒"

End Sub

右側のピンクボタン処理

■このプロシージャでは二次元配列を使います。

二次元配列は使用するとき要素数を指定した宣言をする必要があります。そのためファンクション関数から行数と列数をもらってデータの範囲を宣言しています。（11行目のコードです）

Variant型のyousoはファンクションcall_yousoSuuが返す行数と列数を格納します。

12行目で二次元配列の要素数を再設定しています。

■18行目のDo～Loopはファイルの行数をカウントしながら繰り返し処理をして行データが無くなったら繰り返しを終了します。Doが繰り返すたびにmyDataLine に行データを渡します。

■20行目For～Nextは、myDataLineを受け取ってmyDataLine の列数分繰り返してその列にあるデータを二次元配列myDataに格納します。

■28行目でシートのセル範囲に二次元配列データを渡します。

範囲の指定の仕方

■セル範囲と配列の関係

.Range(.Cells(8, 1), .Cells(youso(0), youso(1))).Value = myData

.Cells(8, 1)はセル範囲の先頭行、先頭列を表しています。

youso(0)は11行目で取得した配列の行の要素数です

youso(1)は同じく配列の列要素数になります。

.Cells(youso(0), youso(1))はセル範囲の最終行、最終列を指定しています。

Sub det_in()
'FSOファイルシステムオブジェクト使用
'二次元配列から全範囲指定でセルへ書き出す
    Dim fso As Object, ffile As Object                   'FileSystemObject
    Dim csvName As String
    Dim j As Long, i As Long
    Dim myData() As Variant, myDataLine As Variant
    Dim youso As Variant, T As Variant
    T = Timer '0時からの時間を取得
    csvName = ActiveWorkbook.Path & "\seibuncsv.csv"     'ファイルアドレス取得
    youso = call_yousoSuu(csvName)
    ReDim myData(youso(0), youso(1))
    'オブジェクト作成
    Set fso = CreateObject("Scripting.FileSystemObject")
    '二次元配列にファイルデータを格納
    Set ffile = fso.OpenTextFile(csvName, 1)    'ファイル開き直し
    j = 0
    Do Until ffile.AtEndOfStream
        myDataLine = Split(ffile.readline, ",")
        For i = 0 To UBound(myDataLine) - 1
            myData(j, i) = myDataLine(i)
        Next i
        j = j + 1
    Loop
    ffile.Close
'    '二次元配列データをシートに展開
    With ActiveSheet
        .Range(.Cells(8, 1), .Cells(youso(0), youso(1))).Value = myData   '0.3秒
        .Cells(7, "L") = Timer - T & " 秒" '計測した時間を表示
    End With
End Sub

呼び出されて配列要素数を返すファンクション

■Sub　det_in()プロシージャの11行目（赤下線）で呼び出しています

■このファンクションでは引数でもらったCSVファイルの行数と列数を返す処理をしています。

call_yousoSuu（０）＝行数

call_yousoSuu（１）＝列数

を格納して戻ります

Function call_yousoSuu(csvName As String) As Variant
    Dim fso As Object                 'FileSystemObject
    Dim ffile As Object               'File
    Dim result(1) As Long
    Dim myDataLine As Variant
    'オブジェクト作成
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' 二次元配列要素上限設定
    Set ffile = fso.OpenTextFile(csvName, 1)
    myDataLine = Split(ffile.readline, ",")
    ffile.readall
    result(0) = ffile.line - 1               '行数
    result(1) = UBound(myDataLine)          '列数
    ffile.Close
    '返却
    call_yousoSuu = result
End Functionま