17
A common strategy used by journalists I've worked with is to first FOIA a data schema or other explanation of how the data is managed by the government body. This allows you to be much more explicit in constructing your actual request.
And as rcackerman noted, you may not actually have to start with a FOIA -- but ask them what they have and how they have it,...
13
If you already have a nicely formatted CSV then why not publish that? You can publish in both formats if you really want to do the RDFa too.
9
The W3C offers a collection of tools that convert from CSV to RDF. However, there is no explicit mention of RDFa in any of these CSV converters. Personally, I'd give the RDF Refine plugin for OpenRefine a try.
Once you have your open data in an RDF format, you could use the RDF Translator to turn it into RDFa.
7
The most versatile tool for geo format conversion is ogr2ogr in gdal. Here's an online front end that uses ogr2ogr to convert to and from GeoJSON, and it supports GeoRSS.
5
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.
5
In general, it is good to have a sense of the most common ways machine-readable data is available (eg, API access, CSV/Excel, etc). With this knowledge, make a phone call to the appropriate government official and chat with them about what they have available. Verbally ask them if they have specific formats, or if they have a database (and if so, can they ...
5
I've not done this myself, but the US Census does provide datasets and information for mapping census data between 2010 and 2000. They call these the geography relationship files. Below is a link to the datasets/information.
http://www.census.gov/geo/maps-data/data/relationship.html
This is an excerpt from their website that I think would apply to your ...
5
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 ...
4
Missouri Census Data Center provides a (SAS-based) app that does this pretty smoothly.
4
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
4
Summarizing from a thread on NICAR-L,
Many reporters found that is was helpful to include technical terms in request, like "ASCII text", "manipulatable digital format", "Excel, CSV or other delimited text or spreadsheet format".
The journalists seemed to think this helped because such a request would be more likely routed to someone who understood how ...
3
I received some help on it. Something along these lines with xor in Python did the job:
with open('...dat', 'rb') as f:
with open('...txt', 'w') as out:
xor = 1
s = ""
while True:
c = f.read(1)
if c == "" or len(c) == 0:
break
c = chr(ord(c) ^ xor)
s += str(c)
...
3
I assume that your are talking about the 5-star Open Data from Tim-Berners Lee.
In this case, you can check this one about working with different kind of file types in Python.
3 star data is CSV and 4 star data is RDF.
From the linked answer:
Comma Separated Files (CSV) files can be a very useful format, because it is compact and thus suitable to ...
3
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.
2
Look at this question about official/unofficial data requests. Similarly, I've found it useful to ask questions about what data (or maybe just paper files) exist so you can ask for exactly what you want.
2
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.
2
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 ...
2
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
2
nces data are generally complex sample survey data, which means you'll eventually need a statistical package (sas, spss, stata, or R) to work with the microdata correctly. R is the only free option here, and you can load the ascii file directly into R with the SAScii package. good luck!
install.packages("SAScii") # if it is not already installed.
library(...
1
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(...
1
If you are trying to convert Excel to JSON there is a free tool that will do it
http://www.exceltojson.com/
1
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 ...
1
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 ...
1
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.
1
Yes, ogr2ogr does this job efficiently. Install, GDAL/OGR then run below command.
ogr2ogr -f GeoJSON target.geojson geoRSS.xml
(here target.geojson is the geojson file to be generated, while geoRSS.xml is actual feed file. You need to download it before you run above command.)
1
In the United Kingdom the law is currently being changed to provide a 'Right to Data' as part of the Freedom of Information legislation, so if requesting from a UK Authority then it may be useful to reference the draft Code of Practice (datasets) which states that an authority 'Disclose datasets in an electronic form which is capable of re-use'.
1
I agree with other answers but I also think that the first question would be : who is going to use those data ? If it's just for consultation html or csv seems great but if you want them to be re-use by developers, in ways you just don't know yet, maybe RDF gives more flexibility. If your data will just be download by big companies people, .xls or .xlsx will ...
1
This is indeed a common question! First step should consider what your aim and your audience is.
At the ODI we believe that good open data goes beyond technical standards when publishing. That's why we have build the Open Data Certificates. More on the about page.
It covers four areas:
Legal
Technical
Practical
Social
1
if you want to publish it on the web, publish it in .html, so it works on the web, aka web platform.
best course of action here is to have .json that you render in .html in the document, but also will render to .csv/.xls//.xlsx/.etc on the fly, for users to download
1
Perhaps asking for the data in a machine-readable format like X, Y, or Z and expressly requesting it not be in PDF.
(Disclaimer - I am the Sr. API Strategist for GSA)
Only top voted, non community-wiki answers of a minimum length are eligible
