Thread: Table column headings PgAmin4

Table column headings PgAmin4

From
TedJones
Date:
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

From
Melvin Davidson
Date:

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 <ted@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.



--

Re: Table column headings PgAmin4

From
Murtuza Zabuawala
Date:
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 <ted@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

From
"Ted Jones"
Date:

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 -----
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 <ted@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

From
"David G. Johnston"
Date:
On Wed, Feb 7, 2018 at 11:01 AM, Ted Jones <ted@mentra.co.uk> 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

From
"Ted Jones"
Date:

Hi David
 
I'm sorry but I'm not sure what you mean! Can you give me a simple example? Thanks.
 
regards
 
Ted Jones
----- Original Message -----
Sent: Wednesday, February 07, 2018 6:28 PM
Subject: Re: Table column headings PgAmin4

On Wed, Feb 7, 2018 at 11:01 AM, Ted Jones <ted@mentra.co.uk> 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

From
"David G. Johnston"
Date:
On Wed, Feb 7, 2018 at 11:47 AM, Ted Jones <ted@mentra.co.uk> 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.​

 Inline image 1
Attachment

Re: Table column headings PgAmin4

From
"Strauch, Sheldon"
Date:
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.johnston@gmail.com> wrote:
On Wed, Feb 7, 2018 at 11:47 AM, Ted Jones <ted@mentra.co.uk> 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.​

 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.    

Attachment

Re: Table column headings PgAmin4

From
"David G. Johnston"
Date:
On Wed, Feb 7, 2018 at 1:00 PM, Strauch, Sheldon <sstrauch@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...

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

From
Anthony DeBarros
Date:
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 (sstrauch@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.johnston@gmail.com> wrote:
On Wed, Feb 7, 2018 at 11:47 AM, Ted Jones <ted@mentra.co.uk> 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.​

 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.    

Attachment

Re: able column headings PgAmin4

From
Johann Spies
Date:
On Wed, Feb 07, 2018 at 01:59:32PM -0800, Anthony DeBarros wrote:
> 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/

For the same purpose I have written a script in Julia which I regularly
use.  It creates an SQL-file from a csv similar to the output of a
pg_dump of a table.  Then I can run 'psql -f sqlfile.sql' and it will
create and populate the table.

I have attached the script.  You need to have Julia on your computer
(which is a good thing :)

Regards.

Johann
--
Johann Spies                            Telefoon: 021-808 4699
Databestuurder /  Data manager        Faks: 021-883 3691

Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie
Centre for Research on Evaluation, Science and Technology 
Universiteit Stellenbosch.


The integrity and confidentiality of this e-mail is governed by these terms / Die integriteit en vertroulikheid van
hierdiee-pos word deur die volgende bepalings gere��l. http://www.sun.ac.za/emaildisclaimer 
Attachment

Re: able column headings PgAmin4

From
TedJones
Date:
Hi Johann
 
I have installed Julia and I have a julia command prompt 'julia>' and I also
have Juno for JuliaPro which is an IDE.
 
I have loaded the file into the IDE and tried to run it. Initially I had
>julia csv_to_sql that produced an error - syntax: extra token 'csv_to_sql'
after end of expression.
 
I tried various things and eventually had 'Usage: julia csv_to_sql <csv
filename> <tablename for sql [>outputfile.sql]
however I could not input any file names!
 
What is the correct way to run the file? Thanks.




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


Re: able column headings PgAmin4

From
Johann Spies
Date:
On Thu, Feb 15, 2018 at 09:31:30AM -0700, TedJones wrote:
>  
> What is the correct way to run the file? Thanks.

The way I am using it is like this:

julia ~/bin/csv_to_sql.jl inputfile.csv tablename > tablename.sql

Apologies.  Recent changes in the fast changing Julia caused a problem
with the present code. Tests I have done today showed there is a bug
with the CSV-library which I could overcome using a workaround.  But the
rest of the code, although it still works, now is unreasonably slow.

I could so far not determine the cause.

If I cannot fix it soon, I will probably rewrite the script in Python3.

Regards
Johann



-- 
Johann Spies                            Telefoon: 021-808 4699
Databestuurder /  Data manager        Faks: 021-883 3691

Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie
Centre for Research on Evaluation, Science and Technology 
Universiteit Stellenbosch.

The integrity and confidentiality of this e-mail is governed by these terms / Die integriteit en vertroulikheid van
hierdiee-pos word deur die volgende bepalings gere��l. http://www.sun.ac.za/emaildisclaimer
 


Re: able column headings PgAmin4

From
Johann Spies
Date:
On 19 Feb 2018 I wrote
> On Thu, Feb 15, 2018 at 09:31:30AM -0700, TedJones wrote:
> >  
> > What is the correct way to run the file? Thanks.
> 
> Apologies.  Recent changes in the fast changing Julia caused a problem
> with the present code. Tests I have done today showed there is a bug
> with the CSV-library which I could overcome using a workaround.  But the
> rest of the code, although it still works, now is unreasonably slow.
> 
> I could so far not determine the cause.
> 
> If I cannot fix it soon, I will probably rewrite the script in Python3.

I have created a github repository.  The script to create a PostgreSQL
dumpfile from a csv is available at
https://github.com/johannspies/csv_to_sql.jl .

Initial tests seems to indicate that it is working now.

Regards
Johann

-- 
Johann Spies                            Telefoon: 021-808 4699
Databestuurder /  Data manager        Faks: 021-883 3691

Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie
Centre for Research on Evaluation, Science and Technology 
Universiteit Stellenbosch.

The integrity and confidentiality of this e-mail is governed by these terms / Die integriteit en vertroulikheid van
hierdiee-pos word deur die volgende bepalings gere��l. http://www.sun.ac.za/emaildisclaimer