From: | <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> |
Subject: | Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text file that contains comma and double quotes |
Date: | 2018-12-17 17:27:08 |
Message-ID: | 485865655.585358.1545067628456.JavaMail.yahoo@mail.yahoo.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Daniel,
Thank you for the comments.
I changed my line 7 to this:
$line = implode("\t",$line). "\n";
Now, I get the following.
Warning: pg_copy_from(): Copy command failed: ERROR: extra data after last expected column CONTEXT: COPY test_table, line 1: "1 Denny's orange juice "1 500 yen" """Dear John""" "32"" TV"" in
----- 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2018-12-17 17:57:08 | Re: loading jdbc Driver in servlet |
Previous Message | Tom Lane | 2018-12-17 17:02:12 | Re: NL Join vs Merge Join - 5 hours vs 2 seconds |