PostgreSQLに複数のCSVをインポート

Question

###前提・実現したいこと
毎日他のシステムで作成される日本語ヘッダ付きCSVをDBサーバーに置き
夜間バッチでディレクトリ内のCSV全てインポートしたいです。

ご教示くださいますよう、お願い致します。
(サンプルコードをいただけると幸いです)

###発生している問題・エラーメッセージ
PostgreSQL9.0なので、COPYオプションにENCODING が指定できません。

SET client_encoding TO 'SJIS';
COPY table_name FROM E'C:\test\importdata1.csv' (format csv, header true );

と2行にしないと取り込めません。
バッチファイルではpsql.exe以降1行で記述しないとならないようなので困っています。
他サイトで回答のあった
set IMPCMD=-q -t SET client_encoding TO 'SJIS';COPY table_name FROM E'C:\test\importsuru.csv' (format csv, header true ); \q
echo %IMPCMD% | psql.exe -U postgres -d [データベース名] -h localhost
を試したところ
ERROR: syntax error at or near "-"
陦・1: -q -t SET client_encoding TO 'SJIS';
^
ERROR: invalid byte sequence for encoding "UTF8": 0x8a
CONTEXT: COPY table_name, line 1

と出てしまいます。

###試したこと
インポートバッチ.bat
@echo off
psql.exe -U postgres -d dbname -h localhost -q -t -f C:/test/importCSV.sql
exit

importCSV.sql
SET client_encoding TO 'SJIS';
COPY table_name FROM E'C:\test\importdata1.csv' (format csv, header true );

は動きます。
どちらかのファイルでループできればいいのですが。

###補足情報(言語/FW/ツール等のバージョンなど)
DBサーバー
OS:Windows Sever 2008 R2 Standard
PostgreSQL9.0

Comments

[A.Ichi]

一行で記述とありますがpsql.exe -U user_name db_name <c:\test.txt　textにスクリプトを書いてSTDINから入力する方法ではできませんでしょうか？Windowsは環境が無くて確認がとれていませんが

[s55188827]

知識不足で申し訳ありません。具体的にtest.txtに何を書けばよろしいのでしょうか？STDINから入力というと全てのCSVファイルの先頭にcopy table_name from stdin;を入れる(CSVを書き換える)ということでしょうか？

[A.Ichi]

sqlへのコマンドをpsqlに入力するイメージにてファイルを作成します。　copyはサーバサイド、\copyはクライアントサイドのコマンドとなります。　copyでもSTDIN、STDOUTを指定してクライアントからのIOとする事ができます。

Answer 1

回答ありがとうございます。勉強になりました。
ただ私の理解不足でヒントをいただいてもその通りに試すことができませんでした。
結局バッチでファイル取得のループを実行し、set～copy～コマンドのファイルにファイル名のパラメータをつけて呼び出すことが出来ました。
パラメータの受渡のサンプル見つからず苦労しました。
できれば１つのファイルにしたかったですが、このバージョンでは諦めます。

インポートバッチ.bat

@echo off

for %%A in (C:\test\*.csv) do call :importproc %%~fA
:importproc
psql.exe -U postgres -d [データベース名] -h localhost -q -t -f importCSV.sql -v csvname=%1

exit /b

importCSV.sql

SET client_encoding TO 'SJIS';
COPY keiyaku FROM :'csvname' (format csv, header true );

おかげさまで1ファイルに出来ました。
インポートバッチ.bat

@echo off
set PGCLIENTENCODING=SJIS
for %%A in (C:\test\*.csv) do call :importproc %%~fA
:importproc
psql.exe -U postgres -d [DB名] -h localhost -c"\copy keiyaku from %1 (format csv, header true );"
exit /b

---コマンドプロンプト---
C:\test>C:\test\インポートコマンド.bat
(format: No such file or directory
↑のメッセージがなぜ出るかが分かりませんが、一応インポートは完了しました。
皆様、本当にありがとうございます。

Comments

[kuniku]

過去に自動化したわけではありませんが、
UTF-8のCSVでデリミタがタブ文字で、それをcopy文で読み込むことができず
Windowsのコマンドプロント上にコピー＆貼り付けで対応したときは

コマンドプロントを起動して、以下をコピー＆貼り付けで実行した

<pre>
cmd.exe /F:OFF

set PGCLIENTENCODING=UTF8

psql -U dbuser -p 5432 -d dbname -c "truncate table tablename01;"
psql -U dbuser -p 5432 -d dbname -c "\copy tablename01 from 'file01' (FORMAT csv, DELIMITER ' ' ,NULL '\N', HEADER true)"
</pre>

DELIMITERの後の''の間がタブ文字です.

PGCLIENTENCODING という環境変数で、client側の文字コード（ファイルのエンコード）を強制しました。

[sazi]

ああ、バッチのみで行いたかったという事なのですね。
コマンドプロンプトは基本SJISなので、質問にある変数経由の場合、文字コード相違によるエラーを引き起こしているようですね。
その中で-q -t \q などの記述がみられますが、その他サイトのurlを教えてもらえますか？
パイプも良いですけど、plsql には -c によるコマンド指定があるのでそれに変数指定でも良い気がします。
変数には単に;で区切った文字列でよいのではないでしょうか。（手元に9.0環境が無いので試せてませんが）

[s55188827]

質問の仕方が分かりずらく、反省します。
最初に質問したのは知恵袋です。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11178822146
色々検索し、どこかでコピーした起動のサンプルコードそのままのオプションをつけていました。
確かに不要ですね。公式文書を読み流していました。

Answer 2

先ず、バッチでファイル取得のループを実行し、SQLを実行する場合に
COPYコマンドは標準入力(STDIN)に対応しているので、リダイレクトでできないですかね？（試してはいないですが。）

COPY table_name [ ( column [, ...] ) ]
    FROM { 'filename' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

それが駄目なら、処理するファイル名をパラメータにしたストアドにして呼び出す。

次に、SQLの方で複数ファイルに対応する方法として、
アクセスできるフォルダが限定されますが、SQLでファイル情報を取得する関数があります。
こちらの9-60. 汎用ファイルアクセス関数にそれらの関数があります。

複数のファイルを扱う場合には、pg_ls_dir()とpg_stat_file()を組み合わせる必要がありますが、
pg_stat_fileでググればサンプルが見つかると思います。

Comments

[s55188827]

> バッチでファイル取得のループ

バッチでディレクトリ内のCSVを全て取得するのは
インポートバッチ.bat
@echo off
for %%A in (C:\test\*.csv) do call :importproc %%~fA
:importproc
echo %1
psql.exe -U postgres -d dbname -h localhost -q -t -f <<%1
exit /b

*.csvには
SET client_encoding TO 'SJIS';
COPY table_name FROM stdin (format csv, header true );
あ,い,う
1,b,c
2,f,g
:
ではないですよね？
大変恐縮ですが、具体的にどう書いたらいいか
教えていただけないでしょうか。

[sazi]

.batの方は確認できませんが、.csvについて、set～copy～はコマンドですから、データである.csvには含めず、.sqlなどとして別ファイルにして下さい。