Programmatically extract data from an Excel spread

Question

Is there a simple way, using some common Unix scripting language (Perl/Python/Ruby) or command line utility, to convert an Excel Spreadsheet file to CSV? Specifically, this one:

http://www.econ.yale.edu/~shiller/data/ie_data.xls

And specifically the third sheet of that spreadsheet (the first two being charts).

Answer 1

There is a really good Perl library for xls reading: Spreadsheet::ParseExcel.

Answer 2

for ruby, the spreadsheet gem is excellent to read write modify, ...excell files

https://github.com/zdavatz/spreadsheet

Answer 3

For python, there are a number of options, see here, here and here. Note that the last option will only work on Windows with Excel installed.

Answer 4

I may have found an acceptable answer already:

xls2csv

But interested to hear what other options there are, or about tools in other languages.

Answer 5

With pyexcel library, you can do this:

>>> import pyexcel as p
>>> data_sheet=p.get_sheet(file_name='/Users/jaska/Downloads/ie_data.xls', sheet_name='Data')
>>> data_sheet.top_left()
pyexcel sheet:
+---------------------------------------------------------------------------------------------------------+---+---+---+------------+---+---+---+---+---+------------+---+---+---+---+---+---+
|                                                                                                         |   |   |   |            |   |   |   |   |   |            |   |   |   |   |   |   |
+---------------------------------------------------------------------------------------------------------+---+---+---+------------+---+---+---+---+---+------------+---+---+---+---+---+---+
| Stock Market Data Used in "Irrational Exuberance" Princeton University Press, 2000, 2005, 2015, updated |   |   |   |            |   |   |   |   |   | Cyclically |   |   |   |   |   |   |
+---------------------------------------------------------------------------------------------------------+---+---+---+------------+---+---+---+---+---+------------+---+---+---+---+---+---+
| Robert J. Shiller                                                                                       |   |   |   |            |   |   |   |   |   | Adjusted   |   |   |   |   |   |   |
+---------------------------------------------------------------------------------------------------------+---+---+---+------------+---+---+---+---+---+------------+---+---+---+---+---+---+
|                                                                                                         |   |   |   |            |   |   |   |   |   | Price      |   |   |   |   |   |   |
+---------------------------------------------------------------------------------------------------------+---+---+---+------------+---+---+---+---+---+------------+---+---+---+---+---+---+
|                                                                                                         |   |   |   |   Consumer |   |   |   |   |   | Earnings   |   |   |   |   |   |   |
+---------------------------------------------------------------------------------------------------------+---+---+---+------------+---+---+---+---+---+------------+---+---+---+---+---+---+
>>> data_sheet.save_as('ie_data.csv')

And for it to work, you need to install:

$ pip install pyexcel
$ pip install pyexcel-xls

What's more, you can install pyexcel-cli in addition and get your csv data in one command line:

$ pyexcel transcode --sheet-name 'Data' /your/home/Downloads/ie_data.xls ie_data.csv

Answer 6

This is quite late to the game, but I thought I'd add another option via Ruby using the gem "roo":

    require 'rubygems'
    require 'roo'

    my_excel_file = Excelx.new("path/to/my_excel_file.xlsx")
    my_excel_file.default_sheet = my_excel_file.sheets[2]
    my_excel_file.to_csv("path/to/my_excel_file.csv")