CODEWITHSUNDEEP
phppostgresqlcsv
Russel Escalderon Lobrio
Russel Escalderon Lobrio

Reputation: 127

Import CSV file to Postgre

I'm writing some PHP code to import a CSV file to a Postgre DB, and I'm getting the error below. Can you help me?

Warning: pg_end_copy(): Query failed: ERROR: literal newline found in data HINT: Use "\n" to represent newline. CONTEXT: COPY t_translation, line 2 in C:\xampp\htdocs\importing_csv\importcsv.php on line 21

<?php
$connString = 'host = localhost dbname= importdb user=postgres password=pgsql';
$db = pg_connect($connString);

$file = file('translation.csv');

//pg_exec($db, "CREATE TABLE t_translation (id numeric, identifier char(100), device char(10), page char(40), english char(100), date_created char(30), date_modified char(30), created_by char(30), modified_by char(30) )");
pg_exec($db, "COPY t_translation FROM stdin");


foreach ($file as $line) {

    $tmp = explode(",", $line);

    pg_put_line($db, sprintf("%d\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n", $tmp[0], $tmp[1], $tmp[2], $tmp[3], $tmp[4], $tmp[5], $tmp[6], $tmp[7], $tmp[8]));

}

pg_put_line($db, "\\.\n");
pg_end_copy($db);
?>

Upvotes: 5

Views: 4086

Answers (2)

cazort
cazort

Reputation: 786

If you're willing to use PDO (necessitates a separate connection call), there's an elegant solution that does not require as much processing of the data by PHP, and that will also work with any combination of fields so long as their names in the CSV header match the names in the database. I'll assume you already have initialized PDO and have the object as $pdo, and the filename is $filename. Then:

$file=fopen($filename,'r');
$lines=explode("\n", fread ($file, filesize($filename)));
if (end($lines)=='') array_pop($lines); // Remove the last line if it empty, as often happens, so it doesn't generate an error with postgres
$fields=array_shift($lines); // Retrieve & remove the field list
$null_as="\\\\N"; // Or whatever your notation for NULL is, if needed
$result=$pdo->pgsqlCopyFromArray('t_translation',$lines,',',$null_as,$fields);

This is pretty minimal, there is no error handling other than $result returning success or failure, but it can be a starting point.

I like this solution better than the approach you are taking though because you don't need to specify the fields at all, it's all handled automatically.

If you don't want to use PDO, there's a similar solution using your setup and syntax, just for the last line replace it with:

pg_copy_from($db,'t_translation',$lines,',',$null_as)

This solution, however, does not dynamically adjust the field names, the fields of the CSV need to exactly match those in the table. However, the names don't need to line up, as the first line of the CSV file is ignored. I haven't tested this last line though because I don't use this type of connection, so there could be an error in it.

Upvotes: 0

Ulver
Ulver

Reputation: 905

You need to specify FILE_IGNORE_NEW_LINES flag in file() function as a 2nd parameter which otherwise by default will include the newline char at the end of the each array item. This is likely whats causing the issue here.

So just add this flag FILE_IGNORE_NEW_LINES so that lines extracted from csv file will not have newline char at the end of the each line:

$file = file('translation.csv', FILE_IGNORE_NEW_LINES);

Also I would recommend using fgetcsv() instead to read csv file.

Upvotes: 3

Related Questions