Skip to content

Instantly share code, notes, and snippets.

@akira345
Created June 19, 2018 15:15
Show Gist options
  • Save akira345/867855a9cdae7d72da28e3fac49849f0 to your computer and use it in GitHub Desktop.
Save akira345/867855a9cdae7d72da28e3fac49849f0 to your computer and use it in GitHub Desktop.
phpでpostgresqlのcopyを使ってcsv取り込み
<?php
// https://stackoverflow.com/questions/9261916/php-postgresql-pdo-copy-from-stdin より
function executeMetaCommand($dbUser, $dbName, $dbHost, $dbPort, $command)
{
$command = sprintf(
"psql -U %s -h %s -p %s -d %s -f - <<EOT\n%s\nEOT\n",
$dbUser, $dbHost, $dbPort, $dbName, $command
);
$streams = array(
array('pipe', 'r'),// stdin
array('pipe', 'w'),// stdout
array('pipe', 'w') // stderr
);
$process = proc_open($command, $streams, $pipes);
if (!is_resource($process)) {
throw new Exception("Cannot open process:\n$command");
} else {
list(, $stdout, $stderr) = $pipes;
$error = stream_get_contents($stderr);
fclose($stderr);
if (strlen($error) > 0) {
throw new Exception("Process error:\n$error");
} else {
$output = stream_get_contents($stdout);
fclose($stdout);
$returnCode = proc_close($process);
if ($returnCode === -1) {
throw new Exception("Process was completed incorrectly:\n$output");
} else {
return array(
$returnCode,
$output
);
}
}
}
}
//インポート時""をNULLとして取り込む項目を指定
$force_null="ins_date,upd_date";
//DB接続
$db_server= "127.0.0.1";
$db_user="postgres";
$db_name="testDb";
$csv_file="/tmp/test.csv";//絶対パス
$table_name="test";
$command = sprintf("\\copy %s FROM '%s' WITH (format csv,encoding 'utf-8',force_null(%s))", $table_name, $csv_file, $force_null);
//PostgreSQLへの接続は.pgpassかpg_hba.confでPWなし接続のこと。
$ret = executeMetaCommand($db_user, $db_name, $db_server, '5432', $command);
var_dump($ret);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment