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

From
Subject Re: Creating 2D arrays for pg_copy_from, reading tab-delimted textfile that contains comma and double quotes
Date
Msg-id 369015105.594386.1545110138095.JavaMail.yahoo@mail.yahoo.co.jp
Whole thread Raw
In response to Re: Creating 2D arrays for pg_copy_from, reading tab-delimted textfile that contains comma and double quotes  ("Daniel Verite" <daniel@manitou-mail.org>)
Responses Re: Creating 2D arrays for pg_copy_from, reading tab-delimted textfile that contains comma and double quotes
List pgsql-general
Hello Daniel!

This afternoon, I tried it again, and yes, your suggestion that I use a tab
delimited symbol ("\t") instead of a space for the implode works flawlessly.

Yesterday, I reported I got "Warning: pg_copy_from(): Copy command failed: ERROR: extra data after last expected..".
That was because while reading file, I had used this:
     while (($line = fgetcsv($fileRead, "\t")) !== FALSE) {


Today, I used this:
     while (($line = fgetcsv($fileRead, 0, "\t")) !== FALSE) {


.. and now I can see my data saved in the database without those extra double quotes.

It made my day!

Thank you.





----- Original Message -----
> From: Daniel Verite <daniel@manitou-mail.org>
> To: s400t@yahoo.co.jp
> Cc: pgsql-general@lists.postgresql.org
> Date: 2018/12/18, Tue 00:35
> Subject: Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text file  that contains comma and double
quotes
>
>       <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
>



pgsql-general by date:

Previous
From: Raghavendra Rao J S V
Date:
Subject: Does idle sessions will consume more cpu and ram? If yes,how to control them
Next
From:
Date:
Subject: Re: Creating 2D arrays for pg_copy_from, reading tab-delimted textfile that contains comma and double quotes