2013/06/03
PostgreSQL
 >  VBS で Excel データを取り込む
2013/06/02 の続き。ポスグレのストアド関数として VBScript(以下 VBS)のコードを格納し pgAdminV(以下 V は略)から起動する環境ができたので、今回は VBS で Excel ファイルをポスグレ内に取り込む。環境は Windows XP、PostgreSQL 9.2.4、pgAdmin 1.16.1、Excel 2007。Excel がインストールされていることが動作の前提。

なおコード上は一行だが表示上改行されているところがある。

1. VBS で Excel データを配列にコピー
取り込むファイルは 2013/06/02 で保存した C/test.xls とする。中身は下のとおり。


このデータを一括して VBS で配列にコピーする簡単なコードは次のようになる。
Option Explicit
Dim exo, wbo, rows, cols
Set exo = CreateObject("Excel.Application")
Set wbo = exo.Workbooks.Open("C:/test.xls")
rows = wbo.Sheets(1).UsedRange
wbo.Close
exo.Quit
Set exo = Nothing
Set wbo = Nothing

WScript.Echo "行数", UBound(rows, 1)
WScript.Echo "列数", UBound(rows, 2)

Excel を起動し、ファイルを開き、最初のシートで値が入力されている範囲(UsedRange)を一度に rows という名の配列に入れる。これで Excel は終了してよい。最後に確認で rows の行・列数を表示している。ファイルを開く Open メソッドには様々な引数があるが、ウェブ検索すれば情報が多く見つかるので省略。

2. Excel データの配列から INSERT 文を作成
上で作成した rows を行・列ごとにループして全セルの値を見るコードは、次のようになる。
Dim nrow, ncol
For nrow = 1 To UBound(rows,1)
For ncol = 1 To UBound(rows, 2)
WScript.Echo nrow, ncol, rows(nrow, ncol)
Next
Next

このループの中でデータをポスグレに取り込む訳だが、VBS で行・列番号からセルを絞ったり、取り込み先テーブルの構造を検討してそれに合わせ INSERT 文を作るのは面倒である。とりあえず全てのデータを、後で使えるように取り込みたい。そこで単純に取り込み先テーブルの構造を「行番号」「列番号」「セルの値」だけにし、全データを一括して INSERT する。セルの値は数値と文字が混在しているので文字列型に統一する。このクエリを作るコードは次のようになる。
Dim nrow, ncol, cell, sql
sql = "SET client_encoding = 'SJIS'; BEGIN; CREATE TABLE tb_excel (nrow int, ncol int, data text);"
For nrow = 1 To UBound(rows,1)
For ncol = 1 To UBound(rows, 2)
cell = rows(nrow, ncol)
If TypeName(cell) <> "Empty" Then sql = sql & "INSERT INTO tb_excel (nrow, ncol, data) VALUES (" & nrow & ", " & ncol & ", '" & cell & "');"
Next
Next
sql = sql & "COMMIT;"

テーブル名は tb_excel とした。Excel データの文字コードが Shift JIS なのでクエリの先頭で client_encoding を指定している。また Excel の UsedRange には空のセルも含まれているため TypeName(cell) <> "Empty" で判別し値がなければスキップする。

3. SQL をファイル保存し psql で実行
あとは sql を VBS から実行すればよい。ODBC 等のドライバを使う手もあるが、今回は sql をいったんローカルファイルに保存し psql で実行する。そのコードは次のようになる。
Dim fln, fso, tso, wso, cmd, res
fln = "C:/test.sql"
Set fso = CreateObject("Scripting.FileSystemObject")
Set tso = fso.OpenTextFile(fln, 2, True)
Set wso = CreateObject("WScript.Shell")
tso.Write sql
tso.Close
Set tso = Nothing
Set fso = Nothing

cmd = """C:/Program Files/PostgreSQL/9.2/bin/psql.exe -U postgres -w -d postgres -f """ & fln & """"
res = wso.Exec(cmd).StdOut.ReadAll
WScript.Echo res
Set wso = Nothing

VBS から FileSystemObject で C:/test.sql に書き込む。OpenTextFile の第一引数 2 は書き込み専用、第二引数 True は新規作成フラグ。変数 cmd に psql コマンドを入れ、Shell オブジェクトの Exec メソッドで実行し、結果の標準出力を ReadAll で受け取りウィンドウに表示する。ただし VBS の仕様で標準出力または標準エラーの量が 4KB 以上だとハングアップするので psql の出力が多い場合は表示方法を変更する。

4. テスト
↓ 以上のコードをまとめ、ポスグレのストアド関数に格納する。
CREATE OR REPLACE FUNCTION vbs_test_2() RETURNS text AS $BODY$
BEGIN
RETURN $V$
Option Explicit
Dim exo, wbo, rows, cols, nrow, ncol, sql, cell
Set exo = CreateObject("Excel.Application")
Set wbo = exo.Workbooks.Open("C:/test.xls")
rows = wbo.Sheets(1).usedRange
wbo.Close
exo.Quit
Set exo = Nothing
Set wbo = Nothing

sql = "SET client_encoding = 'SJIS'; BEGIN; CREATE TABLE tb_excel (nrow int, ncol int, data text);"
For nrow = 1 To UBound(rows,1)
For ncol = 1 To UBound(rows, 2)
cell = rows(nrow, ncol)
If TypeName(cell) <> "Empty" Then sql = sql & "INSERT INTO tb_excel (nrow, ncol, data) VALUES (" & nrow & ", " & ncol & ", '" & cell & "');"
Next
Next
sql = sql & "COMMIT;"

Dim fln, fso, tso, wso, cmd, res
fln = "C:/test.sql"
Set fso = CreateObject("Scripting.FileSystemObject")
Set tso = fso.OpenTextFile(fln, 2, True)
Set wso = CreateObject("WScript.Shell")
tso.Write sql
tso.Close
Set tso = Nothing
Set fso = Nothing

cmd = """C:/Program Files/PostgreSQL/9.2/bin/psql.exe"" -U postgres -w -d postgres -f """ & fln & """"
res = wso.Exec(cmd).StdOut.ReadAll
WScript.Echo res
Set wso = Nothing
$V$ ;
END ;
$BODY$ language plpgsql IMMUTABLE ;

↓ いつもどおり pgAdmin 上で関数を選択したままプラグインをクリック。


↓ クエリが実行され Excel データを取り込めた。


↓ 取り込み先テーブルの内容。このままでは使えないが・・・


↓ SQL を工夫すれば必要な列の抽出や演算が自在にできる。


今回はテストなので簡単な Excel ファイルを対象とした。取り込むまでの処理と取り込み後のクエリの方が複雑なので「何の意味があるwww」と笑われそうだが、この方法が役立つのは複雑な構造の Excel ファイルの場合や、構造がバラバラの複数の Excel ファイルを処理する場合である。その実例は追い追い紹介していこうと思う。
<< 国土数値情報の微妙な Shapefile を…
pgAdminV から VBScript を起動 >>
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。