Import CSV file to Postgre
I am writing PHP code to import CSV file into Postgre DB and I am getting below error. Can you help me?
Warning: pg_end_copy (): request failed: ERROR: literal newline found in data TIP: Use "\ n" to represent a 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);
?>
source to share
You need to specify the flag FILE_IGNORE_NEW_LINES
in file()
as the second parameter, which by default will include a new char string at the end of each array element. This is likely causing the problem.
So, just add this flag FILE_IGNORE_NEW_LINES
so that lines fetched from the csv file don't have a newline char at the end of each line:
$file = file('translation.csv', FILE_IGNORE_NEW_LINES);
Also, I would recommend reading the csv file instead of fgetcsv ().
source to share