Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text file that contains comma and double quotes

<s400t(at)yahoo(dot)co(dot)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