Обсуждение: Importing tab delimited text file using phpPgAdmin 5.1 GUI
Hello Community!
I am trying to use phpPgAdmin (ver. 5.1 with PHP 5.6.39, server CentOS 7, client Win-10 (Japanese)) to import a tab delimited text file (Excel -> save as tab delimited text; also used notepad to save the same text file UTF-8 encoding) .
Earlier. I had created a table (no columns have double quotes, not mixed case), for example, like this:
CREATE TABLE spec (
rec_id VARCHAR(32)PRIMARY KEY NOT NULL,
title_category VARCHAR(255),
doc_type VARCHAR(255),
etc);
In my tab delimited text file, first row has column names (without "") and starting from second row, data, some with double quotes, some with comma, some with brackets.
When I uploaded the file for the phpPgAdmin to import, I got the following error:
//------------------------------------------------------------------
ERROR: column "rec_id" of relation "spec" does not exist
LINE 1: INSERT INTO "public"."spec" ("rec_id","title_c...
//------------------------------------------------------------------
Why that extra "" in the field name? I don't have it in my text file!
How/where can I tell the phpPgAdmin not to add that extra "" around the field name?
Thanks for reading and suggestions.
On 12/6/18 7:54 PM, s400t@yahoo.co.jp wrote:
> Hello Community!
>
> I am trying to use phpPgAdmin (ver. 5.1 with PHP 5.6.39, server CentOS
> 7, client Win-10 (Japanese)) to import a tab delimited text file (Excel
> -> save as tab delimited text; also used notepad to save the same text
> file UTF-8 encoding) .
>
> Earlier. I had created a table (no columns have double quotes, not mixed
> case), for example, like this:
>
> CREATE TABLE spec (
> rec_id VARCHAR(32)PRIMARY KEY NOT NULL,
> title_category VARCHAR(255),
> doc_type VARCHAR(255),
> etc);
>
> In my tab delimited text file, first row has column names (without "")
> and starting from second row, data, some with double quotes, some with
> comma, some with brackets.
>
> When I uploaded the file for the phpPgAdmin to import, I got the
> following error:
>
> //------------------------------------------------------------------
> ERROR: column "rec_id" of relation "spec" does not exist
> LINE 1: INSERT INTO "public"."spec" ("rec_id","title_c...
> //------------------------------------------------------------------
>
> Why that extra "" in the field name? I don't have it in my text file!
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.
--
Adrian Klaver
adrian.klaver@aklaver.com
Hello, On Thu, 2018-12-06 at 21:29 -0800, Adrian Klaver wrote: > On 12/6/18 7:54 PM, s400t@yahoo.co.jp 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
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.
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.
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@gmail.com>
To: Adrian Klaver <adrian.klaver@aklaver.com>; s400t@yahoo.co.jp; "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
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@yahoo.co.jp 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
On 12/7/18 12:28 AM, s400t@yahoo.co.jp 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@gmail.com> > *To:* Adrian Klaver <adrian.klaver@aklaver.com>; s400t@yahoo.co.jp; > "pgsql-general@lists.postgresql.org" > <pgsql-general@lists.postgresql.org> > *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@yahoo.co.jp <mailto:s400t@yahoo.co.jp> > 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@aklaver.com
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?
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.
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...
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.
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@aklaver.com>
To: s400t@yahoo.co.jp; rob stone <floriparob@gmail.com>; "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
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@yahoo.co.jp 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@gmail.com>
> *To:* Adrian Klaver <adrian.klaver@aklaver.com>; s400t@yahoo.co.jp;
> "pgsql-general@lists.postgresql.org"
> <pgsql-general@lists.postgresql.org>
> *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@yahoo.co.jp <mailto:s400t@yahoo.co.jp>
> 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@aklaver.com
On 12/7/18 9:04 AM, s400t@yahoo.co.jp 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@aklaver.com>
> *To:* s400t@yahoo.co.jp; rob stone <floriparob@gmail.com>;
> "pgsql-general@lists.postgresql.org"
> <pgsql-general@lists.postgresql.org>
> *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@yahoo.co.jp <mailto:s400t@yahoo.co.jp> 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@gmail.com
> <mailto:floriparob@gmail.com>>
> > *To:* Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>; s400t@yahoo.co.jp
> <mailto:s400t@yahoo.co.jp>;
> > "pgsql-general@lists.postgresql.org
> <mailto:pgsql-general@lists.postgresql.org>"
> > <pgsql-general@lists.postgresql.org
> <mailto:pgsql-general@lists.postgresql.org>>
> > *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@yahoo.co.jp
> <mailto:s400t@yahoo.co.jp> <mailto:s400t@yahoo.co.jp
> <mailto:s400t@yahoo.co.jp>>
> > 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@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Just a side comment: Why use phpPgAdmin when pgAdmin 4.6 is current, free and readily available?
It also has a graphical table-from-file loader as well.
On Fri, 7 Dec 2018 at 23:35, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 12/7/18 9:04 AM, s400t@yahoo.co.jp 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@aklaver.com>
> *To:* s400t@yahoo.co.jp; rob stone <floriparob@gmail.com>;
> "pgsql-general@lists.postgresql.org"
> <pgsql-general@lists.postgresql.org>
> *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@yahoo.co.jp <mailto:s400t@yahoo.co.jp> 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@gmail.com
> <mailto:floriparob@gmail.com>>
> > *To:* Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>; s400t@yahoo.co.jp
> <mailto:s400t@yahoo.co.jp>;
> > "pgsql-general@lists.postgresql.org
> <mailto:pgsql-general@lists.postgresql.org>"
> > <pgsql-general@lists.postgresql.org
> <mailto:pgsql-general@lists.postgresql.org>>
> > *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@yahoo.co.jp
> <mailto:s400t@yahoo.co.jp> <mailto:s400t@yahoo.co.jp
> <mailto:s400t@yahoo.co.jp>>
> > 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@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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@aklaver.com>
To: s400t@yahoo.co.jp; rob stone <floriparob@gmail.com>; "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
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@yahoo.co.jp 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@aklaver.com>
> *To:* s400t@yahoo.co.jp; rob stone <floriparob@gmail.com>;
> "pgsql-general@lists.postgresql.org"
> <pgsql-general@lists.postgresql.org>
> *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@yahoo.co.jp <mailto:s400t@yahoo.co.jp> 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@gmail.com
> <mailto:floriparob@gmail.com>>
> > *To:* Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>; s400t@yahoo.co.jp
> <mailto:s400t@yahoo.co.jp>;
> > "pgsql-general@lists.postgresql.org
> <mailto:pgsql-general@lists.postgresql.org>"
> > <pgsql-general@lists.postgresql.org
> <mailto:pgsql-general@lists.postgresql.org>>
> > *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@yahoo.co.jp
> <mailto:s400t@yahoo.co.jp> <mailto:s400t@yahoo.co.jp
> <mailto:s400t@yahoo.co.jp>>
> > 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@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com