0

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:

["email1@domain.com", "email2@domain.com", "email3@domain.com"]

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

INSERT INTO "clients"... ["emails", "{\"email1@domain.com",\"email2@domain.com\"}"]

As you can see, it chops off the third element of the array "email3@domain.com", 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?

user1510700
  • 43
  • 4
  • that seems wrong can you show us an example of `row.to_hash`? – Anthony May 31 '18 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"=>"[[\"email1@domain.com\", \"email2@domain.com\", \"email3@domain.com\", \"email3@domain.com"]]"} – user1510700 May 31 '18 at 18:32
  • 1
    you can see `emails` points to a string not to an array – Anthony May 31 '18 at 18:33
  • 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 May 31 '18 at 18:41
  • 1
    If you need to convert the string containing what's looking as an array, try check [this post](https://stackoverflow.com/a/4477170/5239030). But your string seems to miss an escape char at the end: `\"email3@domain.com"` – iGian May 31 '18 at 20:15

1 Answers1

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.

user1510700
  • 43
  • 4