Take the 2-minute tour ×
Open Data Stack Exchange is a question and answer site for developers and researchers interested in open data. It's 100% free, no registration required.

We have plenty of data buried in a lot of Excel files (.xls, .xlsx). The data comprises mainly statistics (first row comtains colum headers, first column contains row headers, rest of data are decimal numbers).

We want to provide this data in various file formats that better follow the open data best practices than Excel files. Excel has just 2 stars, we want more (see http://5stardata.info).

Since we live in an Linux world, we are looking for batch data conversion tools (libraries, scripts, tools, etc.) - PHP prefered, but this is no must - that convert these Excel files to

  • CSV (decimal point, decimal comma)
  • TSV (tab separated values)
  • JSON
  • XML (schema file and data file)
  • RDF
  • ODF
  • Maybe there are other open data formats, too.

...or at least some of them.

The Excel files are updated periodically (yearly, monthly,...), so the conversion process should be repeatable.

Some Excels have headers in multiple rows. How can we deal with that?

Thanks for your help.

share|improve this question

8 Answers 8

I don't believe that you will find a "ready" library that will be the case for all your excel files. My advice is to create a script by yourself in base of your file structures. Especially if the updates will still have the same structure.

I cannot help you in PHP, but in Python you can find an answer for what you need here: A Python guide for open data file formats. If you decide to do it, you can update your question with an example of your files and let users help you with the code.

share|improve this answer
    
Thanks. I edited the question and I am already happy with some of the above mentioned data formats. –  WeSee May 25 at 16:56

You may want to look at this previous stackoverflow question on converting Excel to CSV in batch mode on Linux. Once it is in CSV, you have lots of options of converting to other formats - but CSV is the most common format for open datasets

http://stackoverflow.com/questions/10557360/convert-xlsx-to-csv-in-linux-command-line

share|improve this answer
2  
Yes, I would especially recommend Libreoffice (in headless mode), which I have used myself for such tasks. It is a more portable solution than Gnumeric. Just write a PHP function that calls libreoffice --headless --convert-to csv $filename --outdir $outdir on command line. –  Nicolas Raoul May 26 at 3:48

unoconv can convert CSV to OpenDocument spreadsheet:

<?php
exec('unoconv -f ods -o output.ods input.xlsx');
?>

unoconv is based on LibreOffice, and has features for batch processing.

share|improve this answer

For example with Python, using Pandas:

import pandas as pd

data = pd.from_excel("path_to.xls", sheetname="sheet1")
data.to_csv("path_to.csv", encoding="utf8")

(As simple as that.)

Pandas can read and write from/to more formats.

However, for typical tabular data CSV is the best option for open data.

share|improve this answer

That sounds like you'll do fine with a CSV format. (Assuming the datasets are not too large.)

The problem, as pointed out before, is that you may have to design a process for each format/version etc of Excel sheet. They are optimised for human-readability, not machines.

You can use your tool of your choice to convert them, but there will be some manual work to codify the conversion.

If you're thinking of publishing data, also consider aspects that go beyond technical aspects; more e.g. in our Open Data Certificates.

share|improve this answer
    
Cool, I didn't know about Open Data Certificates - nice idea. Coming to CSV: We want to provide multiple data formats for open data consumers. They should not have to convert the data but simply use it as we provide it. So we strive to provide multiple formats in parallel. –  WeSee May 27 at 20:01

Have you tried the xlrd package in python? you can use it to import your file. exporting requires the library specific to your output filetype.

Example links.

share|improve this answer

Talend Data Integration can help you go convert Excel files, including those with more than 1 sheet, to any format or your choice for free. Have à look at www.talend.com download the tool and enjoy.

share|improve this answer

simpleexcelphplibrary is the answer you seek:
http://faisalman.github.io/simple-excel-php/api/0.3/
JSON!!!!!!!!!, HTML, CSV, and XML

EDIT:
seriously just github search this if you want/need more. there are so many conversion scripts its ridiculous. you could do the same with npm, but that's not php...i know codeplex (ms) has a php/excel library. also google code, bit bucket, and sourceforge are going to have a similar findings, although perhaps not as up to date.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.