Re: Creating 2D arrays for pg_copy_from, reading tab-delimted textfile that contains comma and double quotes - Mailing list pgsql-general

      <s400t@yahoo.co.jp> wrote:

> When I save that Excel as a tab delimited text file, I get this:rec_no
> item1    item2    item3    item4    item5
> 1    Denny's    orange juice    "1,500 yen"    """Dear John"""    "32""
> TV"(As seen when I opened that file with Notepad)

This looks good. Fields are properly enclosed and double quotes
in contents are doubled, as expected in CSV.

> 5．while (($line = fgetcsv($fileRead, 0, "\t")) !== FALSE) { //0 means I can
> read row whatever its length
> 6．    if($row == 1){ $row++; continue; } //skip header
> 7．    $line = implode(" ",$line). "\n";
> 8．    $twoDarray[] = $line;
> ...
> 14．if (pg_copy_from($con, $tableName, $twoDarray) !== FALSE) {

It goes wrong at line 7. pg_copy_from() expects lines in the
COPY "text format" documented at
https://www.postgresql.org/docs/current/sql-copy.html

It implies that:
- since your call to pg_copy_from() doesn't specify a delimiter
it uses tab, not a space, so implode() must be passed a tab,
not a space.
- if there are backslashes in the contents they must be quoted
by doubling them.
- if there are newline or carriage return characters in the contents
they must be replaced by \n and \r respectively, so as to
not be confused with an end of record.
- if there are tabs in the contents they must be replaced by \t.

These replacements can all be done by a single strtr() call in php.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite