Rails - import CSV entry as array in PostgreSQL

Question

I'm using a Ruby on Rails rake task to import a .csv file into my PostgreSQL database. My code, for reference, is below:

require 'csv'    

namespace :db do
    desc "Populate database"
    task populate_database: :environment do

        CSV.foreach("db.csv", :headers => true) do |row|
            Sale.create!(row.to_hash)
        end

    end
end

In the .csv file, I have an entry in the format "cat; dog; foo; bar". Right now, this entry is being imported into PostgreSQL as "text" (i.e. the data type). However, I'd like for the text to be split at "; " and the results of the split to be stored in PostgreSQL as a text array.

How do I go about doing this?

EDIT FOR CLARIFICATION:

My data looks something like this:

      column 1          | column 2    | column 3
row 1 John              | Jack; Carol | Seth; Annie; Doug
row 2 Jim; Cassie; Jane | Anne        | Laura; Bob

Right now, when I run my rake task, "John" goes into PostgreSQL as text. "Laura; Bob" also goes into PostgreSQL as text. However, I'd like for "Laura; Bob" (and, similarly, "Jack; Carol", "Seth; Annie; Doug", and "Jim; Cassie; Jane") to go into PostgreSQL as a text array. One such array would look like [Jack, Carol].

Nanda · Accepted Answer · 2014-03-27 06:12:17Z

0

Try this

csv_file = open("363Live data v0.csv")
CSV.parse(csv_file.read, {:headers => true}).each do |row|
  # row is an array
  puts row.inspect
  Sale.create!(row.to_hash)
end

EDIT AFTER CLARIFICATION:

Assumption - your multiple values are always separated by ; You may need to check for each cell if it has a ; and then convert into array

# row => {"col1" => "John", "col2" => "Seth; Annie; Doug"}

CSV.foreach("363Live data v0.csv", :headers => true) do |row|
    row.to_hash.map do |key, value|
        row[key] = value.split(';').map {|v| v.strip} unless value.index(';').nil?
    end
    Sale.create!(row)
end

# row => {"col1"=>"John", "col2"=>["Seth", "Annie", "Doug"]}

edited Mar 27, 2014 at 6:12

answered Mar 26, 2014 at 7:22

Nanda

4824 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user1274740 Over a year ago

Hi Nanda-- Thanks for the answer! I'm trying to turn an entry (such as "cat; dog; car") into an array. I don't actually want the entire row of data to be an array.

Nanda Over a year ago

In that case, you can use a="cat;dog;car"; a.split(";") this returns an array

user1274740 Over a year ago

"cat; dog; car" and "foo; bar; are example entries in the .csv file, though.

Nanda Over a year ago

that's alright. supposing you have row as a text and you want to split it by ; use row.split(";")

user1274740 Over a year ago

Do you think you can look at my clarification? Thanks again for your help!

|

Collectives™ on Stack Overflow

Rails - import CSV entry as array in PostgreSQL

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related