How to Create Table from CSV

I would like to create a table from a CSV file (the first line is
headers which I want to use as column names) saved from Excel. I have
a new database which I have been able to create tables from a
tutorial. But I haven’t been able to produce this new table. The
following are my attempts:

CREATE TABLE Equpment_List_Sheet2 FROM 'Equipment List, reference
r1_Sheet2.csv' WITH DELIMITER ',' NULL '' CSV HEADER;
ERROR: syntax error at or near "FROM"
LINE 1: CREATE TABLE Equpment_List_Sheet2 FROM 'Equipment List,
refe...

^
********** Error **********
ERROR: syntax error at or near "FROM"
SQL state: 42601
Character: 35
____________
copy TABLE Equpment_List_Sheet2 FROM 'Equipment List, reference
r1_Sheet2.csv' WITH DELIMITER ',' NULL '' CSV HEADER;
ERROR: syntax error at or near "TABLE"
LINE 1: copy TABLE Equpment_List_Sheet2 FROM 'Equipment List,
refere...
^
********** Error **********
ERROR: syntax error at or near "TABLE"
SQL state: 42601
Character: 6
______________
copy Equpment_List_Sheet2 FROM 'Equipment List, reference
r1_Sheet2.csv' WITH DELIMITER ',' NULL '' CSV HEADER;
ERROR: relation "equpment_list_sheet2" does not exist
********** Error **********
ERROR: relation "equpment_list_sheet2" does not exist
SQL state: 42P01
_______________

Thanks for any help,
ray

On Mar 7, 2011, at 12:13 AM, ray wrote:

>
> CREATE TABLE Equpment_List_Sheet2 FROM 'Equipment List, reference
> r1_Sheet2.csv' WITH DELIMITER ',' NULL '' CSV HEADER;
> ERROR: syntax error at or near "FROM"
> LINE 1: CREATE TABLE Equpment_List_Sheet2 FROM 'Equipment List,
> refe...
>
> ^
> ********** Error **********
> ERROR: syntax error at or near "FROM"
> SQL state: 42601
> Character: 35
> ____________
> copy TABLE Equpment_List_Sheet2 FROM 'Equipment List, reference
> r1_Sheet2.csv' WITH DELIMITER ',' NULL '' CSV HEADER;
> ERROR: syntax error at or near "TABLE"
> LINE 1: copy TABLE Equpment_List_Sheet2 FROM 'Equipment List,
> refere...
> ^
> ********** Error **********
> ERROR: syntax error at or near "TABLE"
> SQL state: 42601
> Character: 6

You can't create a table using CSV. However try following:
1. Create structure of table (Parse the column using some script or manually)
2. Import data using COPY Command.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor(dot)kumar(at)enterprisedb(dot)com
Blog:http://vibhork.blogspot.com

On Sun, 6 Mar 2011, ray wrote:

> I would like to create a table from a CSV file (the first line is
> headers which I want to use as column names) saved from Excel. I have
> a new database which I have been able to create tables from a
> tutorial. But I haven?t been able to produce this new table. The
> following are my attempts:

As mentioned, write the table structure to a file using the postgres DDL.
That is, 'CREATE TABLE <name> (
column1...
);'

Then using psql you can '\copy to <tablename> from <filename> with delimiter
as ',' null as '' CSV' (without the quotes and with appropriate delimiter
and null values. The backslash is needed to make it work.

Rich

ray <ray(at)aarden(dot)us> writes:
> I would like to create a table from a CSV file (the first line is
> headers which I want to use as column names) saved from Excel. I have

You have to manually create the table and its columns, as other said.
The tricky part that is hard (or impossible) to automate is deciding
which data type to use for each column.

Once you've done that, actually importing the data is a matter of using
the COPY command or the pgloader tool.

In PostgreSQL 9.1 you will be able to use CREATE FOREIGN TABLE to
achieve that in one step, see:

CREATE FOREIGN TABLE

http://developer.postgresql.org/pgdocs/postgres/ddl-foreign-data.html
http://developer.postgresql.org/pgdocs/postgres/sql-createforeigndatawrapper.html
http://developer.postgresql.org/pgdocs/postgres/file-fdw.html

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

I appreciate all the information

Thank you,
ray

-----Original Message-----
From: Dimitri Fontaine [mailto:dimitri(at)2ndQuadrant(dot)fr]
Sent: Sunday, March 06, 2011 3:19 PM
To: ray
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] How to Create Table from CSV

ray <ray(at)aarden(dot)us> writes:
> I would like to create a table from a CSV file (the first line is
> headers which I want to use as column names) saved from Excel. I have

You have to manually create the table and its columns, as other said.
The tricky part that is hard (or impossible) to automate is deciding
which data type to use for each column.

Once you've done that, actually importing the data is a matter of using
the COPY command or the pgloader tool.

In PostgreSQL 9.1 you will be able to use CREATE FOREIGN TABLE to
achieve that in one step, see:

CREATE FOREIGN TABLE

http://developer.postgresql.org/pgdocs/postgres/ddl-foreign-data.html

http://developer.postgresql.org/pgdocs/postgres/sql-createforeigndatawrapper
.html
http://developer.postgresql.org/pgdocs/postgres/file-fdw.html

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support