Importing Spreadsheets into Ohana

[devinbalkind]

Hi folks.

NYC:Prepared has been converting information about Superstorm Sandy related services into spreadsheet designed to match the OR spec. That spreadsheet is here.

We've got deployments of the Ohana API Admin and Ohana API Dev Portal.

We'd like to get our spreadsheet imported into the data platform and so we can set up the Ohana Web Search tool and get people access to services information!

To achieve this @monfresh advised us to add an issue here. He also said we should make our spreadsheets more closely resemble the OR spec. To do that we have a few questions:

• is the spec finalized?
• is each heading on the spec a separate sheet?
• how do we format arrays of strings & arrays of objects?
• do we need to know any taxonomies or do we create our own. this question is related to fields like legal_status, service_languages, resource_type, etc.

Please let me know if you can help with this task, and/or if you have advice, tools, templates, etc that could be useful.

Thanks.

[spara]

1. No the spec is not finalized. In addition, Ohana does not consume the Human Services Data Standard directly. The HSDS is an exchange format, that means that Ohana will need a translator to consume the spec as will other programs.
2. At the moment, each 'heading' is a separated file. The file structure closely resembles normalized tables in a relational database.
3. Arrays of strings are comma delimited at the moment. Arrays of objects are represented as individual records in a CSV file.
4. There are no taxonomies used or implied in HSDS. It borrows heavily from the AIRS specification but the only schema (at the moment) is that there are organizations, locations, and services. Organizations can have 1 to n locations and organizations can have 1 to n services. That's it.

For your immediate problem of importing data into Ohana, it's probably best to look directly at the sql for building the tables. You can find the sql here. If you need help with writing the sql, feel free to email me and I can take a look at your spreadsheets.

[spara]

1.Tthe spec is far from finalized. The spec is a data interchange specification and it is not consumed directly by Ohana. As it gets closer to being finalized, we will write an Ohana importer.

2. Yes, each entity and property with more than one attribute represents a sing CSV file organized to represent a normalized database
3. Arrays of strings are comma delimited, and arrays of objects are a single record in the appropriate file, e.g. multiple phone numbers for organizations
4. the Human Services Data Standard does not have or imply any taxonomy, but it borrows heavily from AIRS, so there are similar terms used.

For you immediate need for importing data, I would suggest using the Admin interface since there are only 27 entries. If you want to do a batch import, I suggest looking at the sql to understand the Ohana database structure for writing a sql importer. Let me know if you need help with writing the sql.

[spara]

This is an Ohana API issue

[greggish]

@devinbalkind please let us know whether you have the information you need, especially w/r/t formatting arrays.

[monfresh]

The original question @devinbalkind had about formatting arrays of strings and arrays of objects is no longer applicable. Those terms came from the old way of importing data into the API. Ohana API is now fully documented in terms of importing OR-compliant CSV files. You don't have to worry about arrays. You just need to follow the instructions in the Google Spreadsheets template and in the Ohana API Wiki.