Rails map columns of excel file to db columns

Question

I am working on a engine, where i am parsing a excel file using Roo gem. The problem i am facing is that i don't know how to map columns.

For e.g. my db-table have columns first_name, last_name, emp_id, designation etc. But my excel file can have any column name for e.g. it could be fname, lname, e_id, designation or it could be FirstName, LastName, EmployeeId etc.

Now how do i map data such that it will get stored in the respective column.

I have checked this question but how do i set alias as my excel column header can be anything. If can alias than please tell me how i can do that. Its my first time i am working on rails engine. Also i am new to ruby. Any help would be appreciated.

Rather than using dynamic database columns, it might be easier to serialize the csv into a string and store in the database that way — max pleaner
– max pleaner, Commented Aug 5, 2016 at 8:17

slowjack2k · Accepted Answer · 2016-08-10 08:03:37Z

2

For this kind of stuff I would use a service object.

class ImportExcel
   DEFAULT_MAPPING = {
      excel_col_name => db_colname
   }.freezy

   def initialize(mapping=nil, model_class=nil)
      @mapping = mapping || DEFAULT_MAPPING
      @model_class = model_class || DefaultModelClass
   end

   def import(excel_sheet)
      records = []
      header = excel_sheet.first_row 
      2.upto(excel_sheet.last_row) do |line|  
        record = @model_class.new
        records << record
        header.each_with_index do |name, col|
           record[@mapping[name]]= excel_sheet.row[line][col]
        end
      end
      records
   end
end

# Where ever
importer = ImportExcel.new({ a: :b , c: :d}, MyModel)
new_records = importer.import(sheet)
new_records.map(&:save!)

edited Aug 10, 2016 at 8:03

answered Aug 5, 2016 at 9:33

slowjack2k

2,5861 gold badge17 silver badges23 bronze badges

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

9 Comments

Sinscary Over a year ago

well i want to save the data in db. Your mapping seems to work but hw will you save array to db.

slowjack2k Over a year ago

You can call record.save! after header... end or imorter.import(...).map(&:save!)

Sinscary Over a year ago

I have one more doubt. Actually i am building a engine, so My engine will only give columns and data to model of app and than my engine will save that data to db. How do we map data in that case.

slowjack2k Over a year ago

I'm not shure what you mean. I think you mean the Importer is within the enginge und your app calls the Importer. But your engine knows nothing about the mapping, which has to take place. In this case I would make MAPPING an instance variable and fill it from within app to have the right mapping.

Sinscary Over a year ago

it didn't work. Engine is unable to recognize the table

|

Hardik Upadhyay · Accepted Answer · 2016-08-05 08:30:49Z

1

If excel column sequence is fixed than you can store data like this:

@xls = Roo::Spreadsheet.open(file, extension: :xls)
for i in [email protected]_row
  Table.create(first_name: @xls.row(i)[1],last_name: @xls.row(i)[2],..)
end

Hope it will help you...

answered Aug 5, 2016 at 8:30

Hardik Upadhyay

2,6592 gold badges21 silver badges35 bronze badges

2 Comments

Sinscary Over a year ago

your answer works for small excel files but when there are many columns in db than this code will get bigger. Is there any way that Table.create(first_name: @xls.row(i)[1],last_name: @xls.row(i)[2],..) this code can be optimized

Hardik Upadhyay Over a year ago

If we not sure for column name then we need to do this, but let me try for optimize..if i succeed I will comment here.

Collectives™ on Stack Overflow

Rails map columns of excel file to db columns

2 Answers 2

9 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related