Ruby on Rails Postgresql Array CSV upload

Question

This Rails app (using a postgresql db) imports data from a CSV to create new records of a model "Client." The import in Models/client.rb looks like this:

    def self.import(file, industry)
    CSV.foreach(file.path, headers: true, encoding:'iso-8859-1:utf-8') do |row|
        industry.clients.create! row.to_hash
    end

This works properly in creating the records and populating each record's attributes, per the CSV data, for all record types except for an Array.

Clients have an array type attribute of "emails" (among other array attributes). The array attributes were created in a migration like this:

add_column :clients, :emails, :text, array: true, default: []

In the CSV, they are stored in cells like this:

["[email protected]", "[email protected]", "[email protected]"]

Upon uploading, these emails would show on my local server:

INSERT INTO "clients"... ["emails", "{\"[email protected]",\"[email protected]\"}"]

As you can see, it chops off the third element of the array "[email protected]", and this is true for the last element of all Arrays uploaded from the CSV.

My guess is that the Postgresql array type is having trouble with the format that the array is saved in the CSV (the - ["element1", "element2", ...] )- I have tried several different formats, but no success yet. Any thoughts on how to do this?

that seems wrong can you show us an example of row.to_hash? — Anthony
– Anthony, Commented May 31, 2018 at 16:02
Interestingly, I just ran to_hash outside of the rails app and this is the result I got, which contains all expected elements of the array. Is there something Postgresql doesn't like about this format? - {"apn"=>"4315004014", "firstname"=>"xxx", "lastname"=>"xxx", "sitestreetaddress"=>"xxx ", "sitecitystatezip"=>"Los Angeles, CA 90064", "sitecity"=>"Los Angeles", "emails"=>"[[\"[email protected]\", \"[email protected]\", \"[email protected]\", \"[email protected]"]]"} — user1510700
– user1510700, Commented May 31, 2018 at 18:32
If each CSV cell points to a string - is there a way to take a cell that contains something like: ["element1", "element2", "element3"] and import that value in a way that postgresql can understand it as an array? — user1510700
– user1510700, Commented May 31, 2018 at 18:41
If you need to convert the string containing what's looking as an array, try check this post. But your string seems to miss an escape char at the end: \"[email protected]" — iGian
– iGian, Commented May 31, 2018 at 20:15

user1510700 · Accepted Answer · 2018-06-01 04:02:25Z

0

Instead of trying to upload these attributes as an array I changed the migration to a normal string.

add_column :clients, :emails, :string

After I upload the CSV data to the rails app with:

def self.import(file, industry)
CSV.foreach(file.path, headers: true, encoding:'iso-8859-1:utf-8') do |row|
    industry.clients.create! row.to_hash
end

I am now just taking that string and using this to manipulate the data:

JSON.parse(@client.emails)

Because the data uploaded from the CSV cell is already in a format that works with the JSON.parse: ["element1", "element2", "element3",... ] this was an effective method.

*NOTE This does not achieve the exact result desired in my posted question, but is functionally serving the same purpose for what is needed in this rails app.

answered Jun 1, 2018 at 4:02

user1510700

434 bronze badges

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

Collectives™ on Stack Overflow

Ruby on Rails Postgresql Array CSV upload

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related