質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.34%

  • PostgreSQL

    1143questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 3,003

s55188827

score 2

前提・実現したいこと

毎日他のシステムで作成される日本語ヘッダ付き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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • A.Ichi

    2017/09/07 18:23 編集

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

    キャンセル

  • s55188827

    2017/09/08 11:43

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

    キャンセル

  • A.Ichi

    2017/09/08 14:17 編集

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

    キャンセル

回答 2

check解決した方法

+1

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

インポートバッチ.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
↑のメッセージがなぜ出るかが分かりませんが、一応インポートは完了しました。
皆様、本当にありがとうございます。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/09/12 10:32 編集

    過去に自動化したわけではありませんが、
    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側の文字コード(ファイルのエンコード)を強制しました。

    キャンセル

  • 2017/09/12 11:08

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

    キャンセル

  • 2017/09/12 11:34 編集

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

    キャンセル

0

先ず、バッチでファイル取得のループを実行し、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でググればサンプルが見つかると思います。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/09/08 16:53


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

    バッチでディレクトリ内の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
    :
    ではないですよね?
    大変恐縮ですが、具体的にどう書いたらいいか
    教えていただけないでしょうか。

    キャンセル

  • 2017/09/08 17:41

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.34%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

  • PostgreSQL

    1143questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。