Best way to load Rails table with specific data from certain columns in multiple CSV files?

Question

I have a CSV dataset with over 100 columns with headers split into two files, and a table in Rails/PostgreSQL with fields meant to take only specific column data, about 60 specific columns.

What is the best way to parse the CSVs and create a table entry filling up the keys in respect to the specific columns I need?

As a sample, this is what I have so far with :ID, :DESC, :PROGRAM, :PROGRAM_DESC as just a few of the column names from the dataset I need to pull from, and p_id, description, program_name, and program_description as the fields in my table.

Doesn't seem to work, the entries all get filled with nil values.

file = File.read(Rails.root.join('lib', 'assets', 'test.csv'))
fileCSV = CSV.parse(file, :headers => true)

fileCSV.each do |row|
    Program.create_or_find_by!(p_id: row[:ID], description: row[:DESC], program_name: row[:PROGRAM], program_description: row[:PROGRAM_DESC])
end

I plan on doing a create for each entry in the first file, then finding the entries and matching them to update the necessary columns from the second file. But what is the best way to write the logic to match the columns to the appropriate fields?

Edit: Think I got it to work, at least for these 4 columns.

I've seen row.to_hash and didn't get the logic at first, but the idea is to save the entire row into a hash, and then assign the values when creating the entry?

file = File.read(Rails.root.join('lib', 'assets', 'test.csv'))
fileCSV = CSV.parse(file, :headers => true)

fileCSV.each do |row|
    rowHash = row.to_hash
    Program.create_or_find_by!(p_id: rowHash['ID'], description: rowHash['DESC'], program_name: rowHash['PROGRAM'], program_description: rowHash['PROGRAM_DESC'])
end

I wasn't able to read ID when it was a symbol in rowHash, but it reads when it's a string, like rowHash['ID']. I checked .class on the ID values and it reads as string, will ID be entered properly eventhough the field accepts integer?

Still looking for an efficient way to load from multiple CSV's for a single entry...

user_10683678 · Answer

Try this, working for me

## services/programs/csv.rb
require 'csv'
module Programs
    class Csv
        def self.import(file)
            new(file).import
        end
        
        def import
            CSV.foreach(file.path, col_sep: ';', headers: true, encoding: 'bom|utf-8', strip: true) do |row|
                data = row.to_h
                p = Program.where(p_id: data['ID']).first_or_initialize
                p.description = data['Desc']
                p.program_name = data['PROGRAM']
                p.program_description = data['PROGRAM_DESC']
                p.save
            end
        end
        
        def initialize(file)
           @file = file
        end

    attr_accessor :file
    end
end

and call Programs::Csv.import(file), where you need it. This setup works for me, i hope it will be helpful to you.

Best way to load Rails table with specific data from certain columns in multiple CSV files?

1 Answers