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

>and now I can see my data saved in the database without those extra double quotes.
.. and with my comma intact as well.

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