Thread: Table column headings PgAmin4
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
>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);
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
www.youtube.com/unusedhero/videos
Folk Alley - All Folk - 24 Hours a day
www.folkalley.com
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
----- Original Message -----From: Murtuza ZabuawalaTo: TedJonesCc: pgAdmin SupportSent: Wednesday, February 07, 2018 5:33 PMSubject: Re: Table column headings PgAmin4You 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,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
Hi MurtuzaThank 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.
----- Original Message -----From: David G. JohnstonTo: Ted JonesSent: Wednesday, February 07, 2018 6:28 PMSubject: Re: Table column headings PgAmin4Hi MurtuzaThank 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.
Hi DavidI'm sorry but I'm not sure what you mean! Can you give me a simple example? Thanks.
![Inline image 1](/media/maillist_attaches/pgadmin-support/2018/02/7/CAKFQuwa9dUYsrs9DLFYgFF7RbHtSr7n6n9gZzF=8x6xWjNON5w@mail.gmail.com/image.png)
Attachment
Hi DavidI'm sorry but I'm not sure what you mean! Can you give me a simple example? Thanks.Something like in the attached image.
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
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 February 7, 2018 at 3:07:21 PM, Strauch, Sheldon (sstrauch@enova.com) wrote:
On Wed, Feb 7, 2018 at 11:47 AM, Ted Jones <ted@mentra.co.uk> wrote:Hi DavidI'm sorry but I'm not sure what you mean! Can you give me a simple example? Thanks.Something like in the attached image.
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
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
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
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
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