Roc's blog

About web development

Import Csv Into Postgres With Copy Command

When deal with data related project, if often need to import data from csv or excel, you can write loop to import with active record model one by one or by transaction, you can also use database command to do the bulk import, in mongodb there’s mongoimport, for postgresql you can use copy.

Here I want to import crimes data (http://data.police.uk) into postgresql, I wrote follow sql command to import:

sql = "copy crimes (crime_id, month, reported_by, falls_within, lon, lat, location, lsoa_code, lsoa_name, crime_type, last_outcome_category, context) from '/Users/rociiu/sandbox/ukpolice/uk-crimes/xx.csv' DELIMITERS ',' CSV HEADER;"

ActiveRecord::Base.connection.execute(sql) # run with active record connection

You can find more document with the link above, here I list column names following the table name to match the columns in csv, also specify the csv delimiters and tell the command that there’s header in csv.

Comments