スモールデータ集計の技

VBSを使ってExcelやCSVを操作し、ハイレベルなデータ集計技をお伝えします。

Excelのデータ2次元取り込み

今回のテーマ

Excelの１シート分のデータをひとつのデータ構造に取り込んで、列名とキーの組み合わせで値を簡単に参照できるようにします。

お題

次ようなExcelファイルがあるものとします。
端末機Noコンピュータ名資産情報収集日OSバージョンIPアドレスデフォルトゲートウェイ
5255RAS152052020/11/4 12:25180910.1.8.23
5262RAS221212020/11/4 12:37190910.1.2.110
2589RAS132052020/11/4 12:17180310.1.2.125
5931RAS134402020/11/5 8:51190910.1.2.83
3297RAS124292019/5/20 14:28180910.1.6.72
.......................
このExcelファイルを読み取って、コンピュータ名と列名から任意のデータを取り出せるようにします。

サンプルスクリプト

option Explicit
Dim fso, ExcelApp,  args, f, oFolder, ofile, Data1, IP, key
const xlToLeft = -4159
const xlUp = -4162
const DebugFlg = true
const ExcelHeaderLine = 1   'ヘッダー行
const ExcelColHeader = 1   'ヘッダー列
const ReadFromFile = 1    ' 1: ファイル指定、2: フォルダ指定
Set fso = CreateObject("Scripting.FileSystemObject")
Set ExcelApp = CreateObject("Excel.Application")
set Data1 = CreateObject("Scripting.Dictionary")  '結果を保存するデータ

set args = WScript.Arguments

ReadExcelFile  args(0), Data1, "コンピューター名"
IP = SearchData( Data1, "RAS18393", "IPアドレス" )     
WScript.echo "IP=" & IP

ExcelApp.Quit
WScript.Quit

Sub ReadExcelFile( filepath, Data1, KeyRecord )
  dim obook, ofile, row, oSheet,maxrow, schema, oneLine, UserName, col, key
  if fso.FileExists( filepath ) then
     WScript.echo filepath
	  set ofile = fso.getFile( filepath )
	  set schema = CreateObject("Scripting.Dictionary")  '列名-列番号
	  set oneLine = CreateObject("Scripting.Dictionary")  '列名-データ
	  set oBook = ExcelApp.Workbooks.Open(ofile.path)    ' Excelファイルを開く
	  set oSheet = oBook.WorkSheets(1)                   '１番左のシートを取り出す。
	  maxrow =  oSheet.Cells(oSheet.Rows.count,ExcelColHeader).end(xlUp).row
	  ParseSchemaExcel oSheet, ExcelHeaderLine, ExcelColHeader, schema 
	  for row = ExcelHeaderLine + 1 to maxrow
	    ParseLineExcel oSheet, row, schema, oneLine     'データ行の解析
	    'ロジックエリア　開始
	    Dict_Pivot Data1, oneline, schema, KeyRecord
	    'ロジックエリア　終了
	  next
	  oBook.close   
  else
     WScript.echo "Can't file " & filepath
  end if
end sub

Function SearchData( oData, key1, key2 )
  dim subdata, ret
  if oData.exists( key1 ) then
     set subdata = oData.item( key1 )
     if subdata.exists( key2 ) then
        ret = subdata.item( key2 )
     else
        WScript.echo key2 & "が見つかりません。"
     end if
  else
     WScript.echo key1 & "が見つかりません。"
  end if
  SearchData = ret
end function

Sub Dict_Pivot( oData, oneline, schema, Title )
  dim col, coltitle, colData, key
  coltitle = oneline.item( title )
  if coltitle <> "" then
    set ColData = CreateObject("Scripting.Dictionary")
    for each col in schema.keys
  	  if col <> Title then  'タイトル列を除外
  	     colData.add col, oneline.item( col )
	  end if
    next
    if oData.exists( coltitle ) then
      WScript.echo "キーが重複しています。" & coltitle
    else
      oData.Add coltitle, colData
    end if
  end if
end sub


Sub ParseSchemaExcel(oSheet, row, colline, schema)
  dim col, maxcol
  schema.removeall
  maxcol = oSheet.Cells(row,oSheet.Columns.count).end(xlToLeft).column
  for col = colline to maxcol
    if oSheet.cells( row, col ).value <> "" then
      schema.add Trim(oSheet.cells( row, col ).value), col
    end if
  next
end sub


Sub ParseLineExcel( oSheet, row, schema, oneline )
  dim key
  oneline.removeall
  for each key in schema.keys
    oneLine.add key, oSheet.Cells( row, schema.item(key) ).value
  next
end Sub

使い方は
>cscript <スクリプトファイル名>
です。コンソールにコンピュータ名が”RAS18393"のIPアドレスが出力されます。

解説

ReadExcelFile args(0), Data1, "コンピューター名"
で、ファイル名、データ変数（ディクショナリ）、縦軸のキーとなる列名を与えて、Excelファイルからデータを読み出し、あとは取り出したデータを
IP = SearchData( Data1, "RAS18393", "IPアドレス" )
のようにキーの値と、参照したい列名を指定するだけで取り出しています。
Excelを開いてフィルタ等を使えば簡単にできる内容ですが、プログラムとして任意のデータをとりだせるため、そのあとで自由にデータの加工ができます。複数のシートを別々のデータオブジェクトにとりだして、組み合わせて加工してもいいでしょう。
このスクリプトのポイントはReadExcelFileを呼び出すときに、キーとなる列名を渡しているだけで、それ以降の処理にはデータ構造にかかわる情報は一切与えていないということです。なので、ほぼブラックボックスの関数として利用することができます。
列番号や行番号を意識する必要がないので、バグが生じにくくすばやくデータ検索、処理ができます。
マスターテーブルの読み込み等ではこの手の処理は多いので、活用いただければ幸いです。
*Excelは、米国Microsoft Corporationの米国およびその他の国における登録商標または商標です。