Postgresql CSV ファイル読み込み 書き出し

CSV 読み込み

http://www.ksknet.net/postgresql/csv.html

 

COPYコマンドでCSVからの読み込みが可能。

COPY table_name FROM 'path_to_csv_file' delimiters ',';

 

CSV 書き出し

http://blog.nomupro.com/entry/20110516/1305562798

まずはDBの中身はこんな感じ。testという名前のDBに↓の様なデータを入れておきます。

test=# SELECT * FROM test_table;
 id |   name    |    tel     
----+-----------+------------
  1 | yamada    | 012345678
  2 | takahashi | 123456789
  3 | suzuki    | 234567890
  1 | takeda    | 3456789012
(4 rows)

コマンドは、以下、1行だけ。

psql <DB名> -c "COPY <テーブル名> TO '<ファイル名>' CSV
---------------------------------------------------
【例】
[root@Melchior ~]# psql test -c "COPY test_table TO '/tmp/hoge.csv' CSV"
COPY 4
[root@Melchior ~]# cat /tmp/hoge.csv 
1,yamada,012345678
2,takahashi,123456789
3,suzuki,234567890
4,takeda,3456789012

条件を指定することも出来ます。

[root@Melchior ~]# psql test -c "COPY (SELECT * FROM test_table WHERE id = 1) TO '/tmp/hoge.csv' CSV"
COPY 1
[root@Melchior ~]# cat /tmp/hoge.csv
1,yamada,012345678

複数のテーブルがある時に、テーブルによって条件を変えてCSV化する必要があったので簡単なシェルスクリプトを作りました。仕様はこんな感じ。

  • 引数に、DB名、テーブルのリスト、出力先のディレクトリを指定。
  • テーブルのリストには、テーブル名、WHERE句以降の条件がパイプ区切りで記載。
  • 出力するファイル名は<テーブル名>.csvとする。
#!/bin/sh

usage() {
    MESSAGE=$1
    echo "Usage: $0 <db name> <db list> <dump dir>"
    echo "       ** $MESSAGE"
    exit 1
}

DB_NAME=$1
DB_LIST=$2
DUMP_DIR=$3
CURDIR=`pwd`
DUMP_DIR="$CURDIR/$DUMP_DIR"

if [ -z $DB_NAME ]; then
    usage "DB NAME is empty!"
fi
if [ -z $DB_LIST  ]; then
    usage "DB LIST is empty!"
fi
if [ -z $DUMP_DIR ]; then
    usage "DUMP DIR is empty!"
fi
if [ ! -f $DB_LIST ]; then
    usage "DB LIST is not file!"
fi
if [ ! -d $DUMP_DIR ]; then
    usage "DUMP DIR is not directory!"
fi

cat $DB_LIST | while read line
do
    TABLENAME=`echo $line | awk -F'|' '{print $1}'`
    CONDITION=`echo $line | awk -F'|' '{print $2}'`
    if [ ! -z "$CONDITION" ]; then
	psql "$DB_NAME" -c "COPY (SELECT * FROM $TABLENAME WHERE $CONDITION) TO '$DUMP_DIR/$TABLENAME.csv' CSV"
    else
	psql "$DB_NAME" -c "COPY $TABLENAME TO '$DUMP_DIR/$TABLENAME.csv' CSV"
    fi
    if [ $? -ne 0 ]; then
	echo "Error occurred"
	exit 1
    fi
done

dblist.txtの中身。

test_table|id = 1
test_table2

DBの中身。

test=# select * from test_table;
 id |   name    |    tel     
----+-----------+------------
  1 | yamada    | 012345678
  2 | takahashi | 123456789
  3 | suzuki    | 234567890
  4 | takeda    | 3456789012
(4 行)

test=# select * from test_table2;
 id  | name | tel 
-----+------+-----
 100 | aaa  | 000
 101 | bbb  | 111
 102 | ccc  | 222
(3 行)

以下、実行例。

[root@Melchior ~]# ./db2csv.sh test dblist.txt tmp/
COPY 1
COPY 3
[root@Melchior ~]# 
[root@Melchior ~]# 
[root@Melchior ~]# ll tmp/
total 4
-rw-r--r--  1 pgsql  wheel  19  5 17 01:12 test_table.csv
-rw-r--r--  1 pgsql  wheel  36  5 17 01:12 test_table2.csv
[root@Melchior ~]# 
[root@Melchior ~]# more tmp/*.csv
1,yamada,012345678
...skipping...
100,aaa,000
101,bbb,111
102,ccc,222

※作ったコードそのままではありません。

注意

  • ファイルのパスは絶対パスでないとダメ。
  • ファイルのパスはシングルクオートで囲まないとダメっぽい。特にシェルスクリプトを作る時は注意。
  • PostgreSQLのユーザで実行されるため、出力先がPostgreSQLのユーザで書き込めるところでないとダメ。↑の場合、rootでmkdirすると、↓みたいに怒られる。
ERROR:  could not open file "/root/tmp/test_table.csv" for writing: Permission denied

今回のシェルスクリプトに関わらず、シェルスクリプトの定石みたいなのは覚えておくと便利だと思います。
(↑のcat してwhileでreadを回すみたいなところ)

 

http://dbweb.0258.net/wiki.cgi?page=CSV%A4%AB%A4%E9SQL%A4%D8%CA%D1%B4%B95

LinuxのシェルでCSVをPostgreSQLデータにする

 最近、Linuxを中心に簡単な講習をしています。シェルスクリプトの活用の簡単な例としてSambaの共有フォルダに毎日出力される売上データをcronによる定期実行などを想定してシェルスクリプトで自動処理する手順について考えてみたときの簡単な資料です。

 CSVファイルは完成されたシステムから出力されるデータで、数値項目に数値に変換できない文字が存在したりすることは無いものとします。

 一例としてpsqlのcopyコマンドを利用する時の概要です。足りない部分は自分で補足してください。

 資料は7ページのPDF形式です。→ 20110713_シェルスクリプト復習.pdf(674)

 

 

文字列中に半角カンマが入ったときを考慮

  • 2011.09.20

 上記の補足になりますが、文字列中にカンマが入るとその分データ項目が余計になってしまい、ずれます。それに考慮した例です。

  • CSVデータは、Windowsの業務アプリなどから出力し、文字コードはShift-JIS
  • Ubuntuは、10.04
  • PostgreSQLは、8.4.x
  • PostgreSQLのデータベースは初期設定のままUTF-8

よく動作確認していませんが、もしおかしくても2,3箇所直せば動いてくれるとおもいます。

 

 

csv2postgres.sh

#!/bin/bash

 

WORKDIR=`/bin/pwd`/

WORKFILE=$0.work

CSV2TAB=./csv2tab.pl

CSVFILE=$1

TABLE=urimei

 

# cd $WORKDIR

 

# nkf: WindowsのCSV文字コード(SJIS)をUTF-8へ変換

#      改行はLFへ変換

# sed: 1行目のカット

# tr: , をタブ文字に変換

nkf -S -x -w -Lu $1  | \

sed '1d' | \

$CSV2TAB > $WORKDIR$WORKFILE

/usr/bin/psql -U postgres gyoumu << eosql

\copy $TABLE from ${WORKDIR}${WORKFILE}

eosql

csv2tab.pl

#!/usr/bin/perl

 

while (<STDIN>) {

        chomp;

        @flds = splitcsv($_);

        $data=join ("\t", @flds);

        print qq($data\n);

}

 

1;

 

sub splitcsv {

        local($csvstr) = @_;

 

        $csvstr .= ',';

        $csvstr =~ s/("([^”]|””)*”|[^,]*),/$1$;/g;

        $csvstr =~ s/"([^$;]*)"$;/$1$;/g;

        $csvstr =~ s/""/"/g;

        return split(/$;/, $csvstr);

}

csv2postgres.sh を少し修正

  • 処理の冒頭で、wcコマンドにより行数を表示し、最後に結果テーブルが何レコードになったかを表示。(見栄えは調整していない)
  • テーブルの指定部分は2番目のパラメータを使ってもいいのかな~という気がします。
  • パラメータなどのチェックしていません。

#!/bin/bash

 

WORKDIR=`/bin/pwd`/

WORKFILE=$0.work

CSVFILE=$1

CSV2TAB=./csv2tab.pl

DATABASE=gyoumu

TABLE=urimei

 

wc $CSVFILE

 

# nkf: WindowsのCSV文字コード(SJIS)をUTF-8へ変換

#      改行コードもLFへ変換

# sed: 1行目のカット

# tr: , をタブ文字に変換

nkf -S -x -w -Lu $CSVFILE  | \

sed '1d' | \

$CSV2TAB > $WORKDIR$WORKFILE

/usr/bin/psql -U postgres $DATABASE << eosql

\copy $TABLE from ${WORKDIR}${WORKFILE}

SELECT count(*) FROM $TABLE;

eosql

関連ページ

Leave a Reply

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です