Simple CSV file import
You have a CSV file called "data.csv". It has a header line, and is delimited using ";". You want to import it into Postgres and a table called "your_table":
Create the database table. Set column-types so the string fields in the CSV file, can be cast to values in columns.
CREATE TABLE your_table ( -- Your columns ); |
Execute COPY command:
COPY your_table FROM '/path/to/csv/file/data.csv' WITH DELIMITER ';' CSV HEADER; |
If the data is geospatial
Another option is using ogr2ogr, which has a CSV driver. The COPY command could be faster.
If you have point data, check out this howto: Loading Point Data from a CSV File into PostGIS
If using ogr2ogr you can also specify loading data into PostgreSQL with copy:
PG_USE_COPY: This may be “YES” for using COPY for inserting data to Postgresql. COPY is less robust than INSERT, but significantly faster.
http://www.gdal.org/ogr/drv_pg.html
I am using pgAdmin III. All I want to do is import a small table from Excel into my PostgreSQL database. I saved the table as a CSV. How do I import this? I don’t even see where to type the code you posted above. I am very new to this. Why is such a basic thing so seemingly hard to execute?
Hi David
You execute it like a query. You can use PgAdmin III for that. Connect to a database that contains a table that matches your CSV file, and press the “SQL” button that gives you the SQL query editor and write the query there. Hope this helps.
Kostas
It doesn’t work in pgadmin at all. you can write stored procedure to do it and then select procedure() if you want to use pgadmin. Another way is to use psql – then you can write those queries directly.
i want to know procedure for import spread sheet from suse linux to postgres sql(pg admin)ans plz within today itself…