Skip to content

Instantly share code, notes, and snippets.

@chriswhong
Created February 28, 2020 17:55
Show Gist options
  • Save chriswhong/067cb12d74fbb6297bdd657d1e4da00b to your computer and use it in GitHub Desktop.
Save chriswhong/067cb12d74fbb6297bdd657d1e4da00b to your computer and use it in GitHub Desktop.
Using ogr2ogr to load a CSV into Postgres

The absolute easiest way to get a CSV into a postgresql table is to use ogr2ogr with AUTODETECT_TYPE=YES.

I learned a while back that this is what cartoDB uses to import your CSV into postgis (with a lot of other parameters added)

ogr2ogr -f PostgreSQL PG:"host=localhost user=postgres dbname=postgres password=password"  docs.csv -oo AUTODETECT_TYPE=YES
@teo113
Copy link

teo113 commented Jul 13, 2021

agreed. Works well.
Furthermore, if you wanted to only load certain columns from the CSV, you could pass an -sql flag like this:
PostgreSQL PG:"host=localhost user=postgres dbname=postgres password=password" docs.csv -oo AUTODETECT_TYPE=YES -sql "SELECT col1, col2 FROM docs"
Notice how you must omit the file extension (.csv) from the SQL query.

@seabre
Copy link

seabre commented Feb 23, 2023

This interestingly doesn't work with CSVs with only one column. You have to "trick" it by adding a comma right after the first column in the header or by adding a stub second column in the header.

@teo113
Copy link

teo113 commented Feb 23, 2023

This interestingly doesn't work with CSVs with only one column. You have to "trick" it by adding a comma right after the first column in the header or by adding a stub second column in the header.

Sure, but you’d never need to use the -sql filter if the CSV only contains 1 column.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment