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

Hello Rob,

Thank you for your comments.
As I wrote just a few minutes ago, using a tab delimiter inside the implode as suggested by Daniel (and specifying the
readlength ("0") for the fgetcsv has solved this problem. 

At one point, I had tried to use the last parameter - enclosure character - in your reply, but I got an error,
somethinglike, 
Warning: pg_copy_from(): Copy command failed: ERROR: missing data for column
"item1" CONTEXT: COPY test_table, line 1: "1 Denny's orange juice 1,500
yen" ...

Then Daniel's reply came and I tried that, still made errors here and there
(removed the read length- but forgot to put it back in subsequent
tests), and was looking for ways to strip off the leading and trailing
double quotes while leaving the comma intact.

FF now..


Anyway, crisis averted this time!

Thank you again for taking time to share your thoughts.

Cheers!




----- Original Message -----
> From: rob stone <floriparob@gmail.com>
> To: s400t@yahoo.co.jp; "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
> Cc:
> Date: 2018/12/17, Mon 23:38
> Subject: Re: Creating 2D arrays for pg_copy_from, reading tab-delimted text file that contains comma and double
quotes
>
> Hello,
>
> On Mon, 2018-12-17 at 21:34 +0900, s400t@yahoo.co.jp wrote:
>>  Hello Good People of the Forum!
>>
>>  I am trying to insert some data into a PostgreSQL database using PHP
>>  and struggling to create an array so that pg_copy_from function will
>>  accept and process the data.
>>
>>  I can insert data but not the way I want- my data this case contains
>>  comma, space, double quotes and unpaired double quote.
>>  I need to use Excel to create the data and save it as tab delimited
>>  text file, and then convert encoding to UTF-8 (from Excel's ANSI)
>>  before passing it to the PHP.
>>
>>  Here is my dummy data in Excel:
>>  rec_no    item1    item2    item3    item4    item5
>>  1    Denny's    orange juice    1,500 yen    "Dear John"   
> 32" TV
>>
>>  Explanation: the first row is header.
>>  The second row is data for each column.
>>  I tried to create variation like apostrophe, space between words,
>>  comma, double quotes and unpaired double quote (32" TV).
>>
>>  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)
>>
>>  Because my data also contains non-ascii, I saved the file with UTF-8
>>  encoding using the Notepad.
>>
>>  Then I created a two-dimensional array with PHP:
>>
>>  1．$file = 'test.txt'; //tab delimited file
>>  2．$fileRead = fopen($file, 'r');
>>            
>>  3．$row = 1;
>>  4．$twoDarray = array();
>>  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;
>>  9．}
>>  10．fclose($fileRead);
>>
>>  Then I passed that twoDarray to pg_copy_from.
>>
>>  $con=pg_connect("host=$host dbname=$dbname port=5432 user=$user
>>  password=$password");
>>
>>  11．if (!$con) {
>>  12．    die("Couldn't open..<br>\n");
>>  13．}
>>
>>  14．if (pg_copy_from($con, $tableName, $twoDarray) !== FALSE) {
>>  15．    print "OK!";
>>  16．}
>>  17．else{
>>  18．    print "Not OK.";   
>>  19．}
>>
>>  When I run the program, I have this error:
>>  Warning: pg_copy_from(): Copy command failed:
>>  ERROR: value too long for type character varying(32) CONTEXT: COPY
>>  test_table, line 1, column rec_no: "1 Denny's orange juice 1,500
> yen
>>  "Dear John" 32" TV" in testProgram.php line xx.
>>
>>  My table definition is:
>>  CREATE TABLE test_table (
>>  rec_no VARCHAR(32) PRIMARY KEY NOT NULL,
>>  item1 VARCHAR(255),..item2 .. until item5.);
>>
>>  Obviously, my program thinks everything in the data row is for the
>>  first field.
>>  No, no.
>>
>>  How to make it think that
>>  1 is for the 'rec_no' field,
>>  Denny's is for the 'item1' field,
>>  orange juice is for the 'item2' field,
>>  1,500 yen is for the 'item3' field,
>>  "Dear John" is for the 'item4' field and
>>  32" TV is for the 'item5' field?
>>
>>  When I tried removing '0' from line 5, that is,
>>  while (($line = fgetcsv($fileRead, "\t")) !== FALSE) {
> //without read
>>  length
>>
>>  I can see data written in the database, but with some extra double
>>  quotes and a missing comma!
>>  That is data was saved as
>>  Denny's,    orange juice,    "1 500 yen",   
> """Dear John""", and
>>      "32"" TV"
>>  into the respective fields.
>>  I cannot have those extra double quotes, and I cannot have missing
>>  comma in my data.
>
>
>
> fgetscsv returns an array from reading a record from a text file.
> So  $line = fgetcsv($fileRead, 0, "\t", '"') would
> use tab as the
> delimiter and remove any enclosure character equal to ".
>
> I don't use Excel. With Libreoffice you can set the delimiter to the
> pipe character and tell it not to use enclosures. So I don't know how
> to massage your Excel file so that 32" TV is rendered correctly.
>
> Secondly, in PHP implode takes an array and turns it into a string with
> a specified character used to delimit the values from the array.
>
> I can only suggest that you read the PHP manual, as well as do some
> searches for the use of pg_copy_from, although I doubt anything useful
> will turn up.
>
> Cheers,
> Rob
>