Re: Table column headings PgAmin4

[Anthony DeBarros]

Hi, all,

My favorite solution to this issue is to use the very handy Python library
csvkit: https://csvkit.readthedocs.io/en/1.0.2/

It includes the command line utility csvsql (
https://csvkit.readthedocs.io/en/1.0.2/scripts/csvsql.html), which will
read a CSV and generate a CREATE TABLE statement. It does a fairly good job
of inferring data types from the column values in the CSV.

It works with PostgreSQL and other DBs as well.

Best,
Anthony DeBarros


On February 7, 2018 at 3:07:21 PM, Strauch, Sheldon (sstra...@enova.com)
wrote:

Ted and David,

Howdy! I too desperately crave this feature in pgAdmin. In light of the
fact that there are now three of us interested in this, I have created the
feature request in RedMine: https://redmine.postgresql.org/issues/3092

Hopefully, this helps the cause...

On Wed, Feb 7, 2018 at 12:53 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Feb 7, 2018 at 11:47 AM, Ted Jones  wrote:
>
>> Hi David
>>
>> I'm sorry but I'm not sure what you mean! Can you give me a simple
>> example? Thanks.
>>
>>
> ​Something like in the attached image.​
>
>  [image: Inline image 1]
>



--

Assumptions validated by consistent data from actual experiments enable the
creation of real value.

Sheldon E. Strauch
*Data Architect, Data Services *
*O* 312-676-1556
*M* 224-723-3878

*Enova International, Inc.*
*This transmission is confidential and may be privileged or proprietary. If
you are not the intended recipient, you are not authorized to use the
information in this transmission in any way. Please inform the sender
immediately if you have received this transmission in error and permanently
delete and destroy the original and any copies of the information.*


ii_161719cb9e54f2e1
Description: Binary data

Re: Table column headings PgAmin4

[Strauch, Sheldon]

Ted and David,

Howdy! I too desperately crave this feature in pgAdmin. In light of the
fact that there are now three of us interested in this, I have created the
feature request in RedMine: https://redmine.postgresql.org/issues/3092

Hopefully, this helps the cause...

On Wed, Feb 7, 2018 at 12:53 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Feb 7, 2018 at 11:47 AM, Ted Jones  wrote:
>
>> Hi David
>>
>> I'm sorry but I'm not sure what you mean! Can you give me a simple
>> example? Thanks.
>>
>>
> ​Something like in the attached image.​
>
>  [image: Inline image 1]
>



-- 

Assumptions validated by consistent data from actual experiments enable the
creation of real value.

Sheldon E. Strauch
*Data Architect, Data Services *
*O* 312-676-1556
*M* 224-723-3878

*Enova International, Inc.*
*This transmission is confidential and may be privileged or proprietary. If
you are not the intended recipient, you are not authorized to use the
information in this transmission in any way. Please inform the sender
immediately if you have received this transmission in error and permanently
delete and destroy the original and any copies of the information.*

Re: Table column headings PgAmin4

[David G. Johnston]

On Wed, Feb 7, 2018 at 1:00 PM, Strauch, Sheldon  wrote:

> Ted and David,
>
> Howdy! I too desperately crave this feature in pgAdmin. In light of the
> fact that there are now three of us interested in this, I have created the
> feature request in RedMine: https://redmine.postgresql.org/issues/3092
>
> Hopefully, this helps the cause...
>

I don't use pgAdmin - and would am more inclined to learn a tool that
facilitates CLI access for this kind of thing in any case.  Its just that
when I do have a need I can add the CREATE TABLE to a psql script easily
enough that having to run the import routine through anything other than
COPY (or \copy) doesn't seem worth the added dependency.

David J.
​

Re: Table column headings PgAmin4

[David G. Johnston]

On Wed, Feb 7, 2018 at 11:47 AM, Ted Jones  wrote:

> Hi David
>
> I'm sorry but I'm not sure what you mean! Can you give me a simple
> example? Thanks.
>
>
​Something like in the attached image.​

 [image: Inline image 1]

Re: Table column headings PgAmin4

[David G. Johnston]

On Wed, Feb 7, 2018 at 11:01 AM, Ted Jones  wrote:

> Hi Murtuza
>
> Thank you for your reply. Unfortunately it is not practical to use this
> approach when there may be 100s of columns! I will look at pgfutter.
>

​When faced with this situation, and feeling unmotivated to go learn a new
tool, I resort to a spreadsheet​.  You can easily build a CREATE TABLE
statement in the spreadsheet after split-copy-transpose-pasting the header
row (appending "text," to each row's column label is a simply formula).

David J.

Re: Table column headings PgAmin4

[Ted Jones]

Hi Murtuza

Thank you for your reply. Unfortunately it is not practical to use this 
approach when there may be 100s of columns! I will look at pgfutter.

regards

Ted Jones
  - Original Message - 
  From: Murtuza Zabuawala 
  To: TedJones 
  Cc: pgAdmin Support 
  Sent: Wednesday, February 07, 2018 5:33 PM
  Subject: Re: Table column headings PgAmin4


  You can use builtin COPY tool to load the CSV data into the table but the 
destination table must be present before you load CSV.

  COPY your_table(column_1, column_2, column_3...column_N) 

  FROM 'C:\tmp\mydata.csv' DELIMITER ',' CSV HEADER;



  If you wish then you can try third party tool called pgfutter.


  --
  Regards,
  Murtuza Zabuawala
  EnterpriseDB: http://www.enterprisedb.com
  The Enterprise PostgreSQL Company




  On Wed, Feb 7, 2018 at 8:18 PM, TedJones <t...@mentra.co.uk> wrote:

I am quite new to PostgreSQL and I am having difficulty with the following:

I am trying to input a csv file into a table that has a very large number of
columns. This means that it is impractical to create a table and specify the
name of all the columns. Can the column headings be taken from the first
line of the csv file?

Additionally, I have tried to do this with smaller number of columns with
CREATE table but the column headings appear in a different order to what is
in the csv file (that cannot be edited). I then cannot edit (cut and paste
as you would expect!) the SQL to change the order of the columns. Surely
there must be a way of doing this!! I have no control over the input csv
files that contain the data.



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html

Re: Table column headings PgAmin4

[Melvin Davidson]

First, please note that PgAdmin4 is just an interface to PostgreSQL, so it is 
very helpful if you state the actual version of PostgreSQL and O/S you are 
working with.IE: SELECT version(); 

>Can the column headings be taken from the first line of the csv file?
Yes. 

>Additionally, I have tried to do this with smaller number of columns with
>CREATE table but the column headings appear in a different order to what is
>in the csv file (that cannot be edited

What you are trying to do can only be done with a SQL statement (IE: Query Tool)
So you need to specify the actual columns and order you will be providing.

https://www.postgresql.org/docs/9.6/static/sql-insert.html

EG:
CREATE TABLE many_col (
a integer,
b varchar(10),
c text,
d varchar(5),
e integer,
f text,
CONSTRAINT many_col PRIMARY KEY (a)
);

INSERT INTO many_col
( b, c, a, e)
VALUES
('first b', 'first text value', 1, 11),
)'secon b', 'first text value', 2, 22);

Melvin Davidson 
 I reserve the right to fantasize.  Whether or not you 
 wish to share my fantasy is entirely up to you. 
www.youtube.com/unusedhero/videos
Folk Alley - All Folk - 24 Hours a day 
www.folkalley.com

 

On Wednesday, February 7, 2018, 10:06:49 AM EST, TedJones 
 wrote:  
 
 I am quite new to PostgreSQL and I am having difficulty with the following:

I am trying to input a csv file into a table that has a very large number of
columns. This means that it is impractical to create a table and specify the
name of all the columns. Can the column headings be taken from the first
line of the csv file?

Additionally, I have tried to do this with smaller number of columns with
CREATE table but the column headings appear in a different order to what is
in the csv file (that cannot be edited). I then cannot edit (cut and paste
as you would expect!) the SQL to change the order of the columns. Surely
there must be a way of doing this!! I have no control over the input csv
files that contain the data.



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html