Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how can I upload and parse an Excel file in Rails?

I want to be able to upload an Excel file that contains contact information. I then went to be able to parse it and create records for my Contact model.

My application is a Rails application.

I am using the paperclip gem on heroku, I've been able to parse vim cards into the Contact model, and am looking for something similar, but will go through all lines of the Excel file.

Gems that simplify the task and sample code to parse would be helpful!

like image 466
Satchel Avatar asked Jan 28 '11 06:01

Satchel


People also ask

How do I parse an Excel spreadsheet?

Click the “Data” tab in the ribbon, then look in the "Data Tools" group and click "Text to Columns." The "Convert Text to Columns Wizard" will appear. In step 1 of the wizard, choose “Delimited” > Click [Next]. A delimiter is the symbol or space which separates the data you wish to split.

Can you upload Excel to DocSend?

Excel file support is available for all DocSend users, no matter what plan you're on.

What is Excel parsing?

Parsing data means you break it down into separate components. For example, you split a column of full names into one column for first names and one for surnames. There is more than one way to extract data from Excel and send it elsewhere.


1 Answers

Spreadsheet is the best Excel parser that I have found so far. It offers quite a lot of functionality.

You say you use Paperclip for attachments which is good. However, if you store the attachments in S3 (which I assume since you use Heroku) the syntax for passing the file to spreadsheet is a little different but not difficult.

Here is an example of the pure syntax that can be used and not placed in any classes or modules since I don't know how you intend to start the parsing of contacts.

# load the gem
require 'spreadsheet'

# In this example the model MyFile has_attached_file :attachment
@workbook = Spreadsheet.open(MyFile.first.attachment.to_file)

# Get the first worksheet in the Excel file
@worksheet = @workbook.worksheet(0)

# It can be a little tricky looping through the rows since the variable
# @worksheet.rows often seem to be empty, but this will work:
0.upto @worksheet.last_row_index do |index|
  # .row(index) will return the row which is a subclass of Array
  row = @worksheet.row(index)

  @contact = Contact.new
  #row[0] is the first cell in the current row, row[1] is the second cell, etc...
  @contact.first_name = row[0]
  @contact.last_name = row[1]

  @contact.save
end
like image 131
DanneManne Avatar answered Oct 27 '22 01:10

DanneManne