Importer

Spreadsheets

2 options available:

• Open Office
• Excel via interactive UI (incomplete)

Open Office

Can import data from spreadsheets in .fods format (e.g. created by Open Office):

• ​http://myhost.com/eden/hms/hospital/create.fods?filename=myhospitals.fods

or from Web2Py CLI:

resource = s3xrc.resource("hms", "hospital")
template = os.path.join(request.folder, resource.XSLT_IMPORT_TEMPLATES, "fods.xsl")
resource.import_xml("myhospitals.fods", template=template)

Excel via interactive UI

• ​GSoC project - now merged with Trunk

Outline

• Spreadsheet importer, in its current form, can be used to import data in .XLS files to the Sahana Eden database
• It has a spreadsheet like interface to enable users to edit spreadsheets in the browser itself.
• Referenced resources, like pr_person in org_staff, can be imported as well.
• Records, which could not be validated, are not inserted into the database. These records are displayed to the users in a spreadsheet interface. Records have at least one field in red. This is the field with an incorrect value. The format in which errors are displayed is : <field-name>: <error> . You entered <value entered by user> . Records can then be corrected and re-imported.
• The spreadsheet importer uses the Jaro-Winkler metric to determine the similarity of two rows. Similarity between rows is checked two at a time. Similar rows are displayed consecutively in another spreadsheet like interface. The user can choose to edit, delete and then import these rows to the database. Errors in these rows are displayed in the same form as described previously.

Steps

​Video Demo

1. Import spreadsheet
   • The importer can be accessed at http://<web address>/<application name>/importer . This is the landing page for the importer.
   • Click on spreadsheet.
   • Upload a spreadsheet. Add a name to the spreadsheet record, and a comment if you wish.
   • Tips:
     • Keep a single worksheet in the spreadsheet.
     • Keep the number of rows around 100. More rows require more rendering and processing,therefore more time. This could also cause server requests to time out. (Please note, 100 rows is by no means a limitation, it is a simply a safe number of rows to render and import, keeping in mind the strength of internet connection. The number of rows isn't really a problem in an offline client of course)

2. Edit spreadsheet
   • You can make changes to the spreadsheet in this step. You can modify cells, delete and add rows.
   • You can search for data. Search returns the first occurrence of a the string in the spreadsheet. Substrings in cells are also matched.
   • Rows can be deleted by either clicking the cross on the right end of each row.
   • A checkbox on the left of each row selects that row. You can click the delete button in the tool bar to delete the select row. You can only select one row at a time.
   • Prior to clicking import, select a row which has titles for each column in the spreadsheet. You will be prompted to select one if you haven't done so already.

3. Select module and resource
   • This step of the spreadsheet importer wizard has two sections, so to speak.
   • On the left is a list of modules in the Sahana Eden installation. You can select a single module from this list.
   • As soon as a selection is made from the modules list, the panel on the right updates to show which resources are present in that module. You can select one resource.
   • When you click the next button, the module fetches resources and fields from the server. Depending upon server speed, this process could take some time. Please be patient during this step.

4. Mapping columns to fields
   • The column titles selected in the second step are displayed next to dropdowns in this step.
   • The drop down lists are populated with the fields present in selected resource. There are two types of fields.
     • Normal fields.
     • Reference fields. These fields are are actually references to different tables in the Eden schema. In the drop down list, they are represented as <field-name> --> <resource-name> --> <field in the nested resource>. Therefore, reference to a person in org_staff is represented in the drop down as person_id --> pr_person --> first_name etc.
   • All columns must be matched to fields. Otherwise, you cannot proceed.
   • The data is extracted from the spreadsheet and then processed for import to the database.

5. Edit invalid rows
   • The database only accepts those records/rows which satisfy validation rules.
   • In case some rows are present which do not conform to the validation enforced on the database, they are rejected. Rows in which all fields have valid values are accepted.
   • The spreadsheet interface shows only those rows which returned an error. The fields which could not be validated are shown in red. Each row will have at least one red field.
   • Errors are represented as <field-name> : <error message>. You entered <your value>. The text in the entire field has to be replaced with the correct value.
   • If you do not wish to import a row, make sure that it is deleted.
   • Click on import to move to the next step.

6. Edit similar rows
   • In large spreadsheets, it is possible that some rows may be similar. The spreadsheet importer determines such rows.
   • These rows are represented in a spreadsheet format are displayed. Two consecutive rows (like 1 & 2, 3 & 4) are similar.
   • You can edit these rows as you wish. You do not have to select a row with column titles.
   • Click the import button to import these rows.

7. Edit invalid rows (only those which were flagged as similar)
   • In case there are some errors in the fields in the similar rows, they are displayed in the same format as in step 5.
   • Operations that can be performed in this step are identical to step 5.

Limitation:

• You cannot import the same spreadsheet again. Causes duplicacy and outright rejection of some records from the database.
• Bugs, listed below

Some bugs and improvements:

• Need to remove console.log in the JS code. These are very helpful when debugging, so I'll remove them when the project is merged. Also, redirection is currently commented out, will be removed when importer is merged.
• Mapping to organisation_id --> org_organisation --> comments works in some cases and does not work in others.
• Better representation of nested resources.
• The UI of the stages in the wizard may be modified suitably.

OpenStreetMap

XSLT available to import Hospitals & Locations into Sahana Eden

• ​http://127.0.0.1:8000/eden/hms/hospital/create.osm?filename=c:\archives\gis-gps\data\americas\haiti\haiti_hospital.osm

ToDo: Provide a UI to select which levels the admin boundary levels correspond to:

• ProjectInformation#OSMImporterUI

Ushahidi

XSLT available to import Ushahidi reports into Sahana Eden's Incident Reporting system

• Access only available to Administrators: ​http://127.0.0.1:8000/eden/irs/ireport/ushahidi
• URL to enter is ​http://instancename.crowdmap.com/api/%3Ftask=incidents%26by=all%26resp=xml

See Also

• BluePrintImporter

---

UserGuidelines