Best way to convert Excel Files to Open Data Formats

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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

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

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

I would look at csvkit

http://csvkit.readthedocs.org/en/0.9.1/

in particular the in2csv tool documented here

http://csvkit.readthedocs.org/en/0.9.1/scripts/in2csv.html

Answer 8

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.

Answer 9

On the understanding that I do work for this company, I'll mention a product called FME that is capable of converting Excel into any of several hundred formats. It's a commercial (i.e. non-open) product, but does support many open formats of data.

There is also a special website section on FME and Open Data Initiatives

Since I work for them I won't go into detail. But it is a definite solution to this problem, so I think (hope) it's acceptable to mention it here.

Answer 10

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.

EDIT:
saving them in google drive allows exportation in ods, and csv, as well as offers a pretty big space of free storage for your data.
Note: this option is only optimal when doing one or two conversions at a time, but keeping that in mind, its a free, easy, cloud hosted/converter.

Answer 11

If you are trying to convert Excel to JSON there is a free tool that will do it

http://www.exceltojson.com/

Answer 12

I'd use Perl, I know there is a CPAN lib for that ant your webserver may also be able to use Perl out of the box.

Search for it and you're done. Easier is Spreadsheet::Read + JSON::XS.

It will end up in a code looking like

use Spreadsheet::Read;
use JSON::XS;

open dest,">>$1.json";
$json = JSON::XS->new->ascii;

print $dest,$json->encode(ReadData($1));

If you need it prettyified, just add ->pretty after the ->new->ascii