Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Postgres: CSV file as a table using FDW
-- Installs "file_fdw" extension and creates foreign table to work with data from CSV file.
-- See also the comment below which helps to automate the process for Google Spreadsheets
-- Another option would be using Multicorn for Google Spreadsheets, but it requires additional steps
-- (see https://wiki.postgresql.org/wiki/Foreign_data_wrappers).
CREATE EXTENSION file_fdw;
CREATE SERVER import FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE table1_import (
col1 text,
col2 text,
...
) SERVER import OPTIONS ( filename '/path/to/file.csv', format 'csv' );
--
@NikolayS

This comment has been minimized.

Copy link
Owner Author

commented Sep 28, 2016

The full procedure for a Google Spreadsheet file available in public (uses list of Pokemons as an example):

  1. Install file_fdw to your database (run in psql):
CREATE EXTENSION file_fdw;
CREATE SERVER import FOREIGN DATA WRAPPER file_fdw;
  1. Download as csv (run in bash):
wget https://docs.google.com/spreadsheets/d/14mIpk_ceBWVnjc1cPAD7AWeXkE8-t729pcLqwcX_Iik/export?format=csv -O pokemons.csv
  1. Get rid of \r (run in vim):
:%s/\r\+$//e
:w
  1. Take the first line of csv file and use it as the list of columns for FDW table (run in bash, and don't forget to change the path to csv file!):
head -n 1 pokemons.csv  | tr '[:upper:]' '[:lower:]' | sed -e 's/[^,a-zA-Z0-9\r]/_/g' | sed -e 's/,/ text, /g' \
  | awk '{print "CREATE FOREIGN TABLE pokemon_import (" $0 "  text) SERVER import OPTIONS ( filename \47/path/to/pokemons.csv\47, format \47csv\47 );"}' \
  | psql 

Now it should work: try SELECT * FROM pokemon_import; in psql.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.