CODEWITHSUNDEEP
ruby-on-railspostgresqlcsv
AdamNYC
AdamNYC

Reputation: 20425

Copy data from CSV file to PostGresql with numeric and unquoted string

I have the following csv file:

NAME, DOB, SCORE
Robert Shine, 12/25/1980, 15
Marry Shine, , 15

I would like to import this to postgresql via Rails migration

In Rails:

class Cases < ActiveRecord::Migration
  def change
    create_table :cases do |t|
        t.string :name
        t.date :dob
        t.int :score
  end
end

and

class ImportData < ActiveRecord::Migration
   def change
      execute "COPY cases FROM 'path/to/file.csv'
               WITH (FORMAT csv, DELIMITER ',',  NULL '');"
   end
end

This doesn't work however, because the first column is text, but without quotation. How can I force postgresql to read this?

Upvotes: 1

Views: 2347

Answers (1)

mu is too short
mu is too short

Reputation: 434785

I don't think the lack of quoting is your problem. I see two problems:

  1. You haven't told PostgreSQL about the header row.
  2. null '' doesn't make ' ' a NULL.

The first problem is easy, just add header true to the options:

HEADER
Specifies that the file contains a header line with the names of each column in the file. On output, the first line contains the column names from the table, and on input, the first line is ignored. This option is allowed only when using CSV format.

The second one is a bit trickier. You have null '' but the second date will actually be seen as ' '. If the second line was this:

Marry Shine,, 15

then you'd get the expected/desired NULL out. If you use null ' ' that problem should go away. I'd also recommend switching to ISO 8601 date formats to avoid ambiguity and dependence on date settings.

Try using this COPY command:

COPY cases FROM 'path/to/file.csv' WITH (FORMAT csv, DELIMITER ',',  NULL ' ', HEADERS true)

Also keep in mind that COPY assumes that the file in question is in the database server's file system so this probably won't work in your production environment.

Upvotes: 3

Related Questions