Rails-在PostgreSQL中将CSV条目作为数组导入 [英] Rails - import CSV entry as array in PostgreSQL

问题描述

我正在使用Ruby on Rails抽空任务将.csv文件导入到我的PostgreSQL数据库中。我的代码供参考，如下所示：

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

在.csv文件中，我有一个格式为 cat; dog; foo; bar的条目。现在，该条目将作为文本（即数据类型）导入PostgreSQL。但是，我希望将文本拆分为;，并将拆分结果作为文本 array 存储在PostgreSQL中。

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.

我该怎么做？

编辑说明：

我的数据看起来像这样：

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

现在，当我运行rake任务时， John以文本形式进入PostgreSQL。 Laura; Bob也以文本形式进入PostgreSQL。但是，我希望 Laura; Bob（以及类似的 Jack; Carol， Seth; Annie; Doug和 Jim; Cassie; Jane）进入PostgreSQL作为文本阵列。这样的数组看起来像[Jack，Carol]。

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].

推荐答案

尝试一下

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

澄清后编辑：

假设-您的多个值始终用;
您可能需要检查每个单元格是否具有; ，然后转换为数组

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"]}

这篇关于Rails-在PostgreSQL中将CSV条目作为数组导入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！