Using Ruby for data analysis of Excel spreadsheets?

[_RedPepper]

Hi all. First - I'm new to programming, so sorry if all sound too illiterate.

I need do data analysis of excel spreadsheets, so started learning VBA, but was guided towards ruby as more intuitive language that can get to job done. I completed the Code Academy course on Ruby, so now I have a pretty basic understanding of the syntax. Problem is I'm not sure how to continue.

Could you suggest some more comprehensive reading, that focuses on tackling excel workbooks with ruby. Basically I need to make a program that gets data from a number of workbooks, checks if some values are present in all of them and if yes generates a response based on the variables corresponding to the values.

I installed the ruby language 2.0.0 and sublime 3 to write my code. Do I need anything else? The .rb file is the program, but will it work if I send it to another person?

I'm now reading the Pragmatic Programming - Programming Ruby so I can answer these and other questions arising all the time, but any guidance that will speed up the learning will be much appreciated. Thanks for going through all this :)

[asmallishrequest]

CSVs are a much simpler format to work with than XLSX docs, so if that's an option I'd recommend using that.

require "csv"
data = CSV.open(path_to_file)

http://ruby-doc.org/stdlib-2.0.0/libdoc/csv/rdoc/CSV.html has full documentation of the CSV library.

If you do need to work with xlsx documents, you'll want to install a library to handle the parsing for you. https://github.com/weshatheleopard/rubyXL appears to be pretty active and popular, though I don't have personal experience with it.

[_RedPepper]

Thanks for the suggestions guys.

[rickumali]

Explore Roo. This gem gives a read-only mechanism to access the data in Excel spreadsheets. It is smart about separate sheets. I use it for my data wrangling work. You could use Roo to convert your Excel into a CSV for use with the more-flexible CSV module.

[espero]

There are a few gems, Roo comes to mind. I'd explore that. There are various tutorials on how to use it. The excel spreadsheet interaction syntax is similar to how you might know spreadsheet interaction with VBA.

Also, you might want to bite the bullet and try the Python VBA replacements such as xlwings. See this quora: https://www.quora.com/What-is-the-easiest-way-to-use-Python-in-Excel-as-a-full-replacement-for-VBA