Обсуждение: \Copy and "Copy With Headers" -- every day these bug me.
These are two things that bug me every single day: 1) "\copy" equivalent. psql has \copy to copy from a local file. Using the JDBC driver I also often copy data into thedatabase using the equivalent COPY .. FROM STDIN… With psycopg2 I also use COPY FROM STDIN to have the local pythonapp feed the file to the postgres COPY command. In PGAdmin, however, to bulk load data into postgres we need to copythe CSV file to the computer where the database resides and fiddle with permissions until the database can read the fileusing COPY. This should be easy to implement, it wasn't hard for me to do it with both psycopg2 and with the JDBC driver,and psql has had it for years. The application (PGAdmin) needs to open the file and then feed the stream to the databaseCOPY command as STDIN. 2) copy with headers. I use this every day on SQL Server Management Studio to select data and copy them out, then pastethem into another app (usually Excel.) With PGAdmin I select and copy the data, paste them into Excel, then *manually*type the column headers. I think both of these should be easy to implement, so I hope they can be implemented. -- John Abraham jea@hbaspecto.com PS when I say "every single day" I mean a lot of days, probably over 250 days per year. Yes I use PGAdmin all the time andgenerally prefer it to SQL Server Management Studio and other similar programs. So great work everyone. There are justa couple of things...
On Wed, 2012-02-15 at 10:36 -0700, John Abraham wrote: > These are two things that bug me every single day: > > 1) "\copy" equivalent. psql has \copy to copy from a local file. Using the JDBC driver I also often copy data into thedatabase using the equivalent COPY .. FROM STDIN… With psycopg2 I also use COPY FROM STDIN to have the local pythonapp feed the file to the postgres COPY command. In PGAdmin, however, to bulk load data into postgres we need to copythe CSV file to the computer where the database resides and fiddle with permissions until the database can read the fileusing COPY. This should be easy to implement, it wasn't hard for me to do it with both psycopg2 and with the JDBC driver,and psql has had it for years. The application (PGAdmin) needs to open the file and then feed the stream to the databaseCOPY command as STDIN. > I agree that this doesn't appear difficult to implement. Some kind of an import data menu in the table's contextual menu. > 2) copy with headers. I use this every day on SQL Server Management Studio to select data and copy them out, then pastethem into another app (usually Excel.) With PGAdmin I select and copy the data, paste them into Excel, then *manually*type the column headers. > If I'm correct, what you want is that, when you paste into Excel, there would be some kind of automatic typing of columns. I don't see that as "easy to implement" for three reasons: 1. I guess it'll be windows only, which doesn't please me. 2. I suppose they use some kind of Excel specific format, which we don't know. 3. I don't own a copy of Excel, meaning I cannot test such a change. > I think both of these should be easy to implement, so I hope they can be implemented. > Your first idea is interesting, and I agree such feature would be great. The second one, well, anyone's free to implement it. I don't see how I would be able to test it. Unless it also works with LibreOffice. > [...] > PS when I say "every single day" I mean a lot of days, probably over 250 days per year. Yes I use PGAdmin all the timeand generally prefer it to SQL Server Management Studio and other similar programs. So great work everyone. There arejust a couple of things... Usually, it's the couple little things that make your life easier that are really interesting to have. So, sure, send your wishes, I welcome them. But a day is only 24 hours, so I need to be really picky on the things I want to implement. But others are welcome to help. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Feb 15, 2012, at 11:01 AM, Guillaume Lelarge wrote:
If I'm correct, what you want is that, when you paste into Excel, there
would be some kind of automatic typing of columns. I don't see that as
"easy to implement" for three reasons:
No sorry you misunderstand. I just want an additional row at the top with the column names in it. Nothing complex.
I suppose it should be "Copy with column names". (It's just called "Copy with Headers" in SQL Server Management Studio.)
1. I guess it'll be windows only, which doesn't please me.
No, same as copying data out of the Data Output pane now, works on mac and windows and probably every other platform. Format is controlled with "Result copy quote character" and "Result copy field separator".
2. I suppose they use some kind of Excel specific format, which we don't
know.
No, the same format that data is copied out, just text with some separators and quote characters. Just an additional row at the top with the column names.
3. I don't own a copy of Excel, meaning I cannot test such a change.
Paste it into a text editor to test it.
Thanks a lot!
--
John Abraham
On Wed, 2012-02-15 at 11:11 -0700, John Abraham wrote: > > On Feb 15, 2012, at 11:01 AM, Guillaume Lelarge wrote: > > > If I'm correct, what you want is that, when you paste into Excel, > > there > > would be some kind of automatic typing of columns. I don't see that > > as > > "easy to implement" for three reasons: > > No sorry you misunderstand. I just want an additional row at the top > with the column names in it. Nothing complex. > Oh, my bad. You want the header. I thought we already had that, but I can't find it anymore. I may be wrong. Anyway, I agree that such feature would be great too. > I suppose it should be "Copy with column names". (It's just called > "Copy with Headers" in SQL Server Management Studio.) > "Copy with headers" is fine with me. My other points are moot since I didn't understand your initial point. Thanks for your answer. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Wed, 2012-02-15 at 19:54 +0100, Guillaume Lelarge wrote: > On Wed, 2012-02-15 at 11:11 -0700, John Abraham wrote: > > > > On Feb 15, 2012, at 11:01 AM, Guillaume Lelarge wrote: > > > > > If I'm correct, what you want is that, when you paste into Excel, > > > there > > > would be some kind of automatic typing of columns. I don't see that > > > as > > > "easy to implement" for three reasons: > > > > No sorry you misunderstand. I just want an additional row at the top > > with the column names in it. Nothing complex. > > > > Oh, my bad. You want the header. I thought we already had that, but I > can't find it anymore. I may be wrong. > > Anyway, I agree that such feature would be great too. > > > I suppose it should be "Copy with column names". (It's just called > > "Copy with Headers" in SQL Server Management Studio.) > > > > "Copy with headers" is fine with me. > BTW, is it on the query tool or on the data editor tool? -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On 2/15/2012 1:01 PM, Guillaume Lelarge wrote: > On Wed, 2012-02-15 at 10:36 -0700, John Abraham wrote: > >> 2) copy with headers. I use this every day on SQL Server >> Management Studio to select data and copy them out, then paste them >> into another app (usually Excel.) With PGAdmin I select and copy >> the data, paste them into Excel, then *manually* type the column >> headers. 1+++++. I've used this forever in Toad and it is very handy for a quick copy and paste into a spreadsheet or email. Very annoying with PgAdmin to have to repeatedly type in column headers by hand. While we're on the subject :), another feature from Toad I miss is the ability to select a copy format (HTML, delimited text, etc.) While Toad has many options, I've never used more than those two, so adding an HTML option would be quite handy for pasting an already-formatted table into HTML email. Thanks. -- Guy Rouillier
On Wed, Feb 15, 2012 at 19:54, Guillaume Lelarge <span dir="ltr"><<a href="mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>></span>wrote:<br /><div class="gmail_quote"><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><divclass="im">> No sorry you misunderstand. I just want an additional row at the top<br /> >with the column names in it. Nothing complex.<br /> ><br /><br /></div>Oh, my bad. You want the header. I thoughtwe already had that, but I<br /> can't find it anymore. I may be wrong.<br /></blockquote></div><br />It's there,but you have to be willing to save the file.<br /><br />After a query, choose File | Export... and check "Column names".<br/><br />
Hello All, This is my first message to the list son I'll do a little introduction about myself. My name is Jorge Tornero and I am a research assistant at Spanish Institute of Oceanography laboratory at Cadiz, Spain. I use pgadmin mainly to mantain the data I store into several postgresql databases because, if I tell you the truth, pgadmin is nowadays (for me) the best tool to edit my data (openoffice ad ?dbc drivers don't behave very well from me) My question is: Is it possible to change the behavior of the enter keys when you view/edit data? The keypad enter key and the other enter key (in the alphanumeric zone) behave different: While keypad enter key makes the editable "cell" go down after you push it, the other key (alphanumeric) makes it go right and then, when the final field of a row is reached, jumps to first column of the next row. I think that this could be improved if the behavior is configurable, because numeric data is mainly typed with the keypad. But I have looking around and looks like there is no chance of changing it, at least by mean of the GUI Anyone can help? Best regards Jorge Tornero
On Wed, 2012-02-15 at 19:54 +0100, Guillaume Lelarge wrote: > On Wed, 2012-02-15 at 11:11 -0700, John Abraham wrote: > > > > On Feb 15, 2012, at 11:01 AM, Guillaume Lelarge wrote: > > > > > If I'm correct, what you want is that, when you paste into Excel, > > > there > > > would be some kind of automatic typing of columns. I don't see that > > > as > > > "easy to implement" for three reasons: > > > > No sorry you misunderstand. I just want an additional row at the top > > with the column names in it. Nothing complex. > > > > Oh, my bad. You want the header. I thought we already had that, but I > can't find it anymore. I may be wrong. > > Anyway, I agree that such feature would be great too. > > > I suppose it should be "Copy with column names". (It's just called > > "Copy with Headers" in SQL Server Management Studio.) > > > > "Copy with headers" is fine with me. > > My other points are moot since I didn't understand your initial point. > > Thanks for your answer. > I added tickets to work on these later. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Wed, 2012-02-15 at 19:01 +0100, Guillaume Lelarge wrote: > On Wed, 2012-02-15 at 10:36 -0700, John Abraham wrote: > > These are two things that bug me every single day: > > > > 1) "\copy" equivalent. psql has \copy to copy from a local file. Using the JDBC driver I also often copy data intothe database using the equivalent COPY .. FROM STDIN… With psycopg2 I also use COPY FROM STDIN to have the local pythonapp feed the file to the postgres COPY command. In PGAdmin, however, to bulk load data into postgres we need to copythe CSV file to the computer where the database resides and fiddle with permissions until the database can read the fileusing COPY. This should be easy to implement, it wasn't hard for me to do it with both psycopg2 and with the JDBC driver,and psql has had it for years. The application (PGAdmin) needs to open the file and then feed the stream to the databaseCOPY command as STDIN. > > > > I agree that this doesn't appear difficult to implement. Some kind of an > import data menu in the table's contextual menu. > I worked on this one all this afternoon. I now have an import window which is capable of doing everything that COPY... FROM... does. You can see some screenshots at: http://developer.pgadmin.org/~guillaume/importtool/ And if you want to test it, code is available on: https://github.com/gleu/pgadmin3/tree/importcsv I would appreciate any comments on it before I apply it. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
Hi Guillaume,
I think - we should have some default values in some text controls.
i.e. Delimiter, Quote, Escape
I did not follow the whole thread.
But - the first thought came in to mind, when I saw the screen shots.
What do you think?
--
--
Thanks & Regards,
Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company
On Mon, Feb 20, 2012 at 4:35 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
On Wed, 2012-02-15 at 19:01 +0100, Guillaume Lelarge wrote:I worked on this one all this afternoon. I now have an import window
> On Wed, 2012-02-15 at 10:36 -0700, John Abraham wrote:
> > These are two things that bug me every single day:
> >
> > 1) "\copy" equivalent. psql has \copy to copy from a local file. Using the JDBC driver I also often copy data into the database using the equivalent COPY .. FROM STDIN… With psycopg2 I also use COPY FROM STDIN to have the local python app feed the file to the postgres COPY command. In PGAdmin, however, to bulk load data into postgres we need to copy the CSV file to the computer where the database resides and fiddle with permissions until the database can read the file using COPY. This should be easy to implement, it wasn't hard for me to do it with both psycopg2 and with the JDBC driver, and psql has had it for years. The application (PGAdmin) needs to open the file and then feed the stream to the database COPY command as STDIN.
> >
>
> I agree that this doesn't appear difficult to implement. Some kind of an
> import data menu in the table's contextual menu.
>
which is capable of doing everything that COPY... FROM... does.
You can see some screenshots at:
http://developer.pgadmin.org/~guillaume/importtool/
And if you want to test it, code is available on:
https://github.com/gleu/pgadmin3/tree/importcsv
I would appreciate any comments on it before I apply it.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Hi Ashesh, On Mon, 2012-02-20 at 10:20 +0530, Ashesh Vashi wrote: > [...] > I think - we should have some default values in some text controls. > i.e. Delimiter, Quote, Escape > We can do exactly like the frmExport window: replacing text control with combobox. Comboboxes will have some usual values (like tab, comma, etc for delimiter), but offer the possibility to change them by anything a user would like. I was also wondering if this window should offer a way to export the contents of a table (in which case, it would also be available for foreign tables, and views). -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Mon, Feb 20, 2012 at 1:01 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
Hi Ashesh,
On Mon, 2012-02-20 at 10:20 +0530, Ashesh Vashi wrote:
> [...]> I think - we should have some default values in some text controls.We can do exactly like the frmExport window: replacing text control with
> i.e. Delimiter, Quote, Escape
>
combobox. Comboboxes will have some usual values (like tab, comma, etc
for delimiter), but offer the possibility to change them by anything a
user would like.
+1
I was also wondering if this window should offer a way to export the
contents of a table (in which case, it would also be available for
foreign tables, and views).
On Mon, 2012-02-20 at 13:05 +0530, Ashesh Vashi wrote: > On Mon, Feb 20, 2012 at 1:01 PM, Guillaume Lelarge > <guillaume@lelarge.info> wrote: > Hi Ashesh, > > On Mon, 2012-02-20 at 10:20 +0530, Ashesh Vashi wrote: > > [...] > > I think - we should have some default values in some text > controls. > > i.e. Delimiter, Quote, Escape > > > > > We can do exactly like the frmExport window: replacing text > control with > combobox. Comboboxes will have some usual values (like tab, > comma, etc > for delimiter), but offer the possibility to change them by > anything a > user would like. > +1 > > I was also wondering if this window should offer a way to > export the > contents of a table (in which case, it would also be available > for > foreign tables, and views). > > > Are you thinking about something like a preview dialog (Fetch first > few rows...)? > Nope. You can already do this with the editdata window. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Mon, Feb 20, 2012 at 7:31 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > Hi Ashesh, > > On Mon, 2012-02-20 at 10:20 +0530, Ashesh Vashi wrote: >> [...] >> I think - we should have some default values in some text controls. >> i.e. Delimiter, Quote, Escape >> > > We can do exactly like the frmExport window: replacing text control with > combobox. Comboboxes will have some usual values (like tab, comma, etc > for delimiter), but offer the possibility to change them by anything a > user would like. +1 > I was also wondering if this window should offer a way to export the > contents of a table (in which case, it would also be available for > foreign tables, and views). We already have frmExport for that. That could probably be extended fairly easily to be a context menu option for tables and views etc, as well as working off the SQL Query tool. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Feb 20, 2012 at 7:39 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > >> Are you thinking about something like a preview dialog (Fetch first >> few rows...)? >> > > Nope. You can already do this with the editdata window. Plus it wouldn't really make much sense. Viewing a few rows is helpful when browsing an unfamiliar schema to get an idea of what/how data is stored but when you want to export something, you are typically looking for some specific data in which you are interested, rather than a random set of rows. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, 2012-02-20 at 09:17 +0000, Dave Page wrote: > On Mon, Feb 20, 2012 at 7:39 AM, Guillaume Lelarge > <guillaume@lelarge.info> wrote: > > > >> Are you thinking about something like a preview dialog (Fetch first > >> few rows...)? > >> > > > > Nope. You can already do this with the editdata window. > > Plus it wouldn't really make much sense. Viewing a few rows is helpful > when browsing an unfamiliar schema to get an idea of what/how data is > stored but when you want to export something, you are typically > looking for some specific data in which you are interested, rather > than a random set of rows. > I completely agree. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Feb 15, 2012, at 11:36 AM, John Abraham wrote:
These are two things that bug me every single day:
1) "\copy" equivalent. psql has \copy to copy from a local file. Using the JDBC driver I also often copy data into the database using the equivalent COPY .. FROM STDIN… With psycopg2 I also use COPY FROM STDIN to have the local python app feed the file to the postgres COPY command. In PGAdmin, however, to bulk load data into postgres we need to copy the CSV file to the computer where the database resides and fiddle with permissions until the database can read the file using COPY. This should be easy to implement, it wasn't hard for me to do it with both psycopg2 and with the JDBC driver, and psql has had it for years. The application (PGAdmin) needs to open the file and then feed the stream to the database COPY command as STDIN.
2) copy with headers. I use this every day on SQL Server Management Studio to select data and copy them out, then paste them into another app (usually Excel.) With PGAdmin I select and copy the data, paste them into Excel, then *manually* type the column headers.
I think both of these should be easy to implement, so I hope they can be implemented.
--
John Abraham
jea@hbaspecto.com
PS when I say "every single day" I mean a lot of days, probably over 250 days per year. Yes I use PGAdmin all the time and generally prefer it to SQL Server Management Studio and other similar programs. So great work everyone. There are just a couple of things...
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
+1 from me.
This would allow us to jettison a competing commercial tool that causes us pain.
Sheldon E. Strauch
Manager, Database Development
sstrauch@enova.com
Manager, Database Development
sstrauch@enova.com
On Mon, 20 Feb 2012 09:15:07 +0000, Dave Page <dpage@pgadmin.org> wrote: > On Mon, Feb 20, 2012 at 7:31 AM, Guillaume Lelarge > <guillaume@lelarge.info> wrote: >> Hi Ashesh, >> >> On Mon, 2012-02-20 at 10:20 +0530, Ashesh Vashi wrote: >>> [...] >>> I think - we should have some default values in some text controls. >>> i.e. Delimiter, Quote, Escape >>> >> >> We can do exactly like the frmExport window: replacing text control with >> combobox. Comboboxes will have some usual values (like tab, comma, etc >> for delimiter), but offer the possibility to change them by anything a >> user would like. > > +1 > This is done. >> I was also wondering if this window should offer a way to export the >> contents of a table (in which case, it would also be available for >> foreign tables, and views). > > We already have frmExport for that. That could probably be extended > fairly easily to be a context menu option for tables and views etc, as > well as working off the SQL Query tool. Not easily, at least if we want to use the COPY API. Right now, frmExport exports a grid or a set (pgSet). I want to avoid using a set because it means we would have to execute a SELECT. I much prefer using COPY with all its features. Anyway, noone seems interested, so I guess it won't be done. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
On Mon, 20 Feb 2012 21:11:17 +0100, Guillaume Lelarge <guillaume@lelarge.info> wrote: > On Mon, 20 Feb 2012 09:15:07 +0000, Dave Page <dpage@pgadmin.org> wrote: >> On Mon, Feb 20, 2012 at 7:31 AM, Guillaume Lelarge >> <guillaume@lelarge.info> wrote: >>> Hi Ashesh, >>> >>> On Mon, 2012-02-20 at 10:20 +0530, Ashesh Vashi wrote: >>>> [...] >>>> I think - we should have some default values in some text controls. >>>> i.e. Delimiter, Quote, Escape >>>> >>> >>> We can do exactly like the frmExport window: replacing text control > with >>> combobox. Comboboxes will have some usual values (like tab, comma, etc >>> for delimiter), but offer the possibility to change them by anything a >>> user would like. >> >> +1 >> > > This is done. > I've made more progress tonight. Now, the user can select which columns in the table will be filled (of course, the file should only contain these columns). He can also select which columns will be ignored for the NULL string. New screenshots at http://developer.pgadmin.org/~guillaume/importtool/ I'm now wondering if I should add a SQL tab, to show the query before it gets executed. Seems it's almost ready to get commited. Comments welcomed. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
On Tue, 2012-02-21 at 23:25 +0100, Guillaume Lelarge wrote: > On Mon, 20 Feb 2012 21:11:17 +0100, Guillaume Lelarge > <guillaume@lelarge.info> wrote: > > On Mon, 20 Feb 2012 09:15:07 +0000, Dave Page <dpage@pgadmin.org> wrote: > >> On Mon, Feb 20, 2012 at 7:31 AM, Guillaume Lelarge > >> <guillaume@lelarge.info> wrote: > >>> Hi Ashesh, > >>> > >>> On Mon, 2012-02-20 at 10:20 +0530, Ashesh Vashi wrote: > >>>> [...] > >>>> I think - we should have some default values in some text controls. > >>>> i.e. Delimiter, Quote, Escape > >>>> > >>> > >>> We can do exactly like the frmExport window: replacing text control > > with > >>> combobox. Comboboxes will have some usual values (like tab, comma, etc > >>> for delimiter), but offer the possibility to change them by anything a > >>> user would like. > >> > >> +1 > >> > > > > This is done. > > > > I've made more progress tonight. Now, the user can select which columns in > the table will be filled (of course, the file should only contain these > columns). He can also select which columns will be ignored for the NULL > string. > > New screenshots at http://developer.pgadmin.org/~guillaume/importtool/ > > I'm now wondering if I should add a SQL tab, to show the query before it > gets executed. > > Seems it's almost ready to get commited. > > Comments welcomed. > No comments, so I commited the patch (actually with a few fixes that I think were required). The import tool will be available with 1.16. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com