导入CSV文件到Postgre [英] Import CSV file to Postgre

问题描述

我正在编写一些PHP代码，以将CSV文件导入Postgre DB，但出现以下错误.你能帮我吗?

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?

警告:pg_end_copy():查询失败:错误:在数据提示中找到文字换行符:使用"\ n"表示换行符.上下文:COPY t_translation，第21行的C:\ xampp \ htdocs \ importing_csv \ importcsv.php中的第2行

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);
?>

推荐答案

您需要在 file()函数中将 FILE_IGNORE_NEW_LINES 标志指定为第二个参数，否则默认为将在每个数组项的末尾包含换行符char.这可能是导致此问题的原因.

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.

因此只需添加此标志 FILE_IGNORE_NEW_LINES ，这样从csv文件提取的行就不会在每行的末尾包含换行符了.

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);

我也建议您使用 fgetcsv()来读取csv文件.

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

这篇关于导入CSV文件到Postgre的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！