2014/07/03
PostgreSQL
 >  COPY + DIR コマンドでファイル一覧表
昨日と同様、PostgreSQL の COPY … FROM PROGRAM で外部コマンドを使う例。ファイル一覧を得る DOS コマンド(今回は Windows なので DIR)を実行し、結果を PostgreSQL の一時テーブルに入れ、適当に整形してファイル一覧テーブルを作る。実行環境は 2014/04/17 を参照。各種ユーザ権限(コマンド実行や対象フォルダへのアクセス)は適宜設定済みの前提。なお COPY … FROM PROGRAM は PostgreSQL 9.3 で追加された機能。

↓ 例として、多数のファイルに分割されたデータを想定。


このファイル群は、↓ の市区町村一覧をもとに PostgreSQL でダミーデータを入れて一括出力した。その経過はいずれ別記事にする。ファイル名に日本語も入れる際、Windows の文字コードが SJIS で PostgreSQLからは直接出力できないため PL/Python を使った。

■ 2014/02/21 全国の市区町村一覧(コード付)を作る例
http://kenpg.seesaa.net/article/389246425.html

↓ コマンドプロンプトから単純に DIR した結果。今回は、この結果を丸ごと PostgreSQL の一時テーブルに取り込む。


Windows の DIR コマンドの仕様で、ファイルのタイムスタンプのうち秒が出力されない。きっちり秒まで含むファイル一覧が必要な時は、DIR の代わりに秒まで出力する VBScript 等を自作する必要がある。例えば ↓ のように。

■ Windows Script Programming : dirコマンドで更新日時の秒まで表示する。
http://scripting.cocolog-nifty.com/blog/2010/01/dir-c6fe.html

↓ COPY コマンドの前に、投入先の一時テーブルを作る。列 rid が行番号シリアル、列 str が DIR の結果の一行ずつを入れる所。DIR の結果にタブ文字がなく、COPY がデフォルトでタブ区切りを想定しているので、結果的に一行一列で取り込めるはず。
CREATE TABLE "201407"."03_files_tmp" (rid serial PRIMARY KEY, str text) ;

↓ 本題の COPY + DIR コマンドを実行した所。なぜか対象フォルダ名が数字から始まるとエラーになり、フォルダ名を英字_数字に変えたら成功した。



↓ 一時テーブルの中身。確かに DIR の結果が一行ずつ入っている。


一時テーブルをそのまま使ってもよいが、今回は新しいテーブルに整理する。↓ がその例。a ブロックの WHERE 句で .txt だけ抽出し、正規表現関数 regexp_matches でファイル名、サイズ、タイムスタンプを切り出す。
CREATE TABLE "201407"."03_files_list" AS
WITH a AS (
SELECT regexp_matches(str
, '^(\d{4}/\d{2}/\d{2} +\d{2}:\d{2}) +([\d,]+) (.+)$') reg
FROM "201407"."03_files_tmp"
WHERE str ~* '\.txt$'
)
SELECT reg[3] fname
, replace(reg[2], ',', '') :: int fsize
, reg[1] ftime
FROM a ;

↓ 作成した新しいテーブルの中身。行数(最左列)が元にあったファイル数と同じ。列 ftime は PostgreSQL のタイムスタンプ型に直接キャストできるが、前述のとおり秒が省かれているので全て 00 秒になってしまい、本来のタイムスタンプと矛盾が出る。今回はそのまま。


↓ ファイル名の先頭のコードで検索する例。


実際は、ファイル一覧を作ること自体が目的というより、多数のファイルを一括インポートする際に役立つ場合が多い。その例を明日書く予定。
<< COPY + DO で複数ファイル一括イン…
COPY コマンドから ExifTool を使う >>
×

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