To Adrian:
Your question: "The original encoding was Win-10 (Japanese) correct?"
Let me answer this way:
Yes, I created the file using used Win 10 (J)'s Excel (2016).
When I saved the file as tab delimited text, it seems it was saved as ANSI because when I opened it using notepad, I could see it was ANSI.
I then changed the encoding to UTF-8 using the notepad ....
But I gave up on importing using the phpPgAdmin.
Over the weekend, I found a way using PHP.
Here is a snippet:
(1) reading file and creating a 2D array:
$fileRead = fopen($file, 'r');
$row = 1;
$twoDarray = array();
while (($line = fgetcsv($fileRead, "\t")) !== FALSE) {
if($row == 1){ $row++; continue; } //skip header
$line = implode(" ",$line). "\n";
$twoDarray[] = $line;
}
fclose($fileRead);
(2)
$con=pg_connect("host=$host...");
if (!$con) {
die("...");
}
if (pg_copy_from($con, $tableName, $twoDarray) !== FALSE) {
print "Success!";
}
else{
print "Failed!";
}
pg_close($con);
//------------------------------
Oh, yes, I had to convert the tab delimited text file to UTF-8 encoding. For this purpose the notepad was enough.
Some version of Excel seem to offer the option to save file with UTF-8 encoding, but the one I am using does not have that option.
Time to move ahead.
Thanks!
----- Original Message -----
From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
To: s400t@xxxxxxxxxxx; rob stone <floriparob@xxxxxxxxx>; "pgsql-general@xxxxxxxxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Date: 2018/12/8, Sat 06:35
Subject: Re: Importing tab delimited text file using phpPgAdmin 5.1 GUI
On 12/7/18 9:04 AM, s400t@xxxxxxxxxxx wrote:
> I didn't specify any schema, so it was created in public schema.
> The error message also says "public"...
> //------------------------------------------------------------------
> ERROR: column "rec_id" of relation "spec" does not exist
> LINE 1: INSERT INTO "public"."spec" ("rec_id","title_c...
> //------------------------------------------------------------------
>
> Output of the \d spec:
>
>
> Table "public.spec"
> Column | Type | Modifiers
> ----------------------------+-------------------------+-----------
> rec_id | character varying(32) | not null
> title_category | character varying(255) |
> doctype | character varying(255) |
> ... goes on like this for other columns.
>
> What are you trying to see in the output of \d spec?
My basic procedure in troubleshooting is starting from the known and
working out to the unknown. So my questions about the schema(s) and the
table definition where to establish a know starting point. Also a common
issue that hit this list are multiple versions(across schemas) of an
object in a database and code hitting the wrong version. One of the
signs of that being error messages of the form you got.
>
> I don't understand what you mean by the import code is trying to insert
> in to wrong version of the table.
> I visually checked the left side "menu like" structure of the
> phpPgAdmin- there is no other table of that name.
See above.
>
> You mentioned that quoted identifiers are not the issue.
> This prompted me to test the process in a table with a few columns and
> ascii characters.
> Immediately it was clear that quoted identifiers were not to blame.
>
> I found that I got that error when I change encoding of the tab
> delimited file to UTF-8.
> Because my data contains non-ascii characters, if I don't use UTF-8, I
> get this error.
>
> ERROR: invalid byte sequence for encoding "UTF8": 0x82
>
>
> ... and I read somewhere that if I open the text file in notpad and save
> it with UTF-8 encoding, I can get rid of the error. (When inserting
> using pyDev (psycopg2)/Eclipse, that does get rid of the error...
Notepad is not a text editor to use in general and in particular for
data transformation work. It has limited knowledge of the text format.
If you need to do that on Windows use Wordpad or better yet Notepad++:
https://notepad-plus-plus.org/
>
> That's why I changed encoding.
>
> And now I am stuck with this error.
>
> But at least, now I am not blaming phpPgAdmin :)
> Thanks for the lead.
>
> BTW, both server and client encoding of my pg db are UTF8.
The original encoding was Win-10 (Japanese) correct?
>
> testdb=# SHOW SERVER_ENCODING;
> server_encoding
> -----------------
> UTF8
> (1 row)
>
> testdb=# SHOW CLIENT_ENCODING;
> client_encoding
> -----------------
> UTF8
> (1 row)
>
> testdb=#
>
>
> ----- Original Message -----
> *From:* Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
> *To:* s400t@xxxxxxxxxxx; rob stone <floriparob@xxxxxxxxx>;
> "pgsql-general@xxxxxxxxxxxxxxxxxxxx"
> <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
> *Date:* 2018/12/7, Fri 23:47
> *Subject:* Re: Importing tab delimited text file using phpPgAdmin
> 5.1 GUI
>
> On 12/7/18 12:28 AM, s400t@xxxxxxxxxxx <mailto:s400t@xxxxxxxxxxx> wrote:
> > Hello Adrian, Rob!
> >
> > Thank you for the comments.
> >
> > Oh, yes, I forgot to mention that I am using Postgresql version 9.6.
> > I had read somewhere that last supported version was 9.3 or
> something,
> > could be 9.2 as you say.
> >
> > I wanted to use phpPgAdmin, that's why I went back to 9.6 even if
> I had
> > installed ver. 10 first.
> > But if the phpPgAdmin quotes identifiers by defaults, I will need to
> > look for some other ways.
>
> I don't think the quoted identifiers are the issue. I am suspecting
> that
> the import code may be trying to INSERT into the wrong version of the
> table. Some questions:
>
> 1) Which schema did you create spec in?
>
> 2) In psql what does \d spec show?
>
>
> >
> > Strictly speaking, I don't need to use the phpPgAdmin, but I am
> trying
> > to find a GUI way to upload a file quickly (the file has
> thousands of
> > records.)
> >
> > Now, I am using pyDev in Eclipse to insert records, but I need to
> use a
> > web-based click and upload.
>
> ?
> https://www.pgadmin.org/
>
> >
> > If I could find a php version of my python code that would be great!
> > My Python code:
> >
> > cur = conn.cursor()
> >
> > with io.open(fileName,'r',encoding='utf8') as f:
> > next(f) # Skip header row.
> > cur.copy_from(f, tableName, sep='\t')
> > conn.commit()
> > cur.close()
> > f.close()
> >
> >
> > I have found PHP samples for line by line read and insert, but
> that will
> > be too slow for me.
> >
> > Cheers!
> >
> >
> >
> > ----- Original Message -----
> > *From:* rob stone <floriparob@xxxxxxxxx
> <mailto:floriparob@xxxxxxxxx>>
> > *To:* Adrian Klaver <adrian.klaver@xxxxxxxxxxx
> <mailto:adrian.klaver@xxxxxxxxxxx>>; s400t@xxxxxxxxxxx
> <mailto:s400t@xxxxxxxxxxx>;
> > "pgsql-general@xxxxxxxxxxxxxxxxxxxx
> <mailto:pgsql-general@xxxxxxxxxxxxxxxxxxxx>"
> > <pgsql-general@xxxxxxxxxxxxxxxxxxxx
> <mailto:pgsql-general@xxxxxxxxxxxxxxxxxxxx>>
> > *Date:* 2018/12/7, Fri 16:47
> > *Subject:* Re: Importing tab delimited text file using phpPgAdmin
> > 5.1 GUI
> >
> > Hello,
> >
> > On Thu, 2018-12-06 at 21:29 -0800, Adrian Klaver wrote:
> > > On 12/6/18 7:54 PM, s400t@xxxxxxxxxxx
> <mailto:s400t@xxxxxxxxxxx> <mailto:s400t@xxxxxxxxxxx
> <mailto:s400t@xxxxxxxxxxx>>
> > wrote:
> > >
> > > Most GUI tools I am familiar with quote identifiers by
> default.
> > > > How/where can I tell the phpPgAdmin not to add that
> extra "" around
> > > > the
> > > > field name?
> > >
> > > I don't know. I have been under the impression that
> phpPgAdmin was
> > > no
> > > longer maintained/in use.
> > >
> > > You might have more luck here:
> > >
> > > https://sourceforge.net/p/phppgadmin/discussion/115884
> > >
> > > >
> > > > Thanks for reading and suggestions.
> > >
> > >
> >
> > If you look at phppgadmin on sourceforge, the tarball files
> are all
> > dated April 15th., 2013.
> > It appears to have stalled at Postgres version 9.2.
> > I doubt if it will run on versions 10 or 11.
> >
> > Cheers,
> > Rob
> >
> >
> >
> >
> >
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
>
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
