| Table of Contents | ||||||||
|---|---|---|---|---|---|---|---|---|
|
| Warning |
|---|
This module has been rewritten and the new documentation can be found here: Spreadsheet Import Module Version 2 The module, however, supports only the xls format which has a limit of 65k rows of data. |
Overview
The Spreadsheet Import Module allows users allows users to import data via XLS files. It is related to a request submitted by Evan Waters (PIH Implementer in Malawi).
A second version has been released for OpenMRS 1.9, see the child page below for it.
Download
- Download the Spreadsheet Import module
- Check out source code: http://svn.openmrs.org/openmrs-modules/spreadsheetimport
- View source code: http://source.openmrs.org/Modules/spreadsheetimport
- Get test data http://tickets.openmrs.org/secure/attachment/34647/testdata.zip
Release Notes
- Version 0.2
- Fixed not-null property references a null or transient value: org.openmrs.module.spreadsheetimport.SpreadsheetImportTemplateColumnColumn.columnImportFirst error after trying to create a template in some cases
- Version 0.1
- Initial release
Instructions
Overview
Spreadsheets are imported based on "Spreadsheet Import Templates." These templates specify the columns and pre-specified values that are present in the given spreadsheet.
...
- In the Spreadsheet Import Template List, check the checkbox next to the corresponding spreadsheet import template.
- Click on "Delete."
Technical Notes
Implementation is at:
http://tickets.openmrs.org/browse/TRUNK-1650
You will find the omod:
http://tickets.openmrs.org/secure/attachment/34646/spreadsheetimport-0.1.omod
as well as a testdata.zip
http://tickets.openmrs.org/secure/attachment/34647/testdata.zip
To use the test data, go into the directory where you downloaded and type:
unzip testdata.zip
You will see four files created.
testdata/testdata.sql - SQL file containing test spreadsheetimport templates
testdata/testdata.xls - XLS file to be used with test spreadsheetimport templates
testdata/import - shell script to import data from testdata.sql (assumes ~/.OpenMRS/openmrs-runtime.properties configured correctly)
testdata/dump - shell script to export data into testdata.sql (assumes ~/.OpenMRS/openmrs-runtime.properties configured correctly)
To quick start, load the pre-formulated spreadsheetimport template data:
cd testdata
./import
(this has been tested on Ubuntu 10.04 amd64)
Under Administration, you will see a Spreadsheet Import Module, with a Spreadsheet Import Tempate List link underneath.
You can use this link to edit the module as well as to import data. Feel free to try both.
Some notes:
* The module has been designed to allow user to import into _any_ table.value inside the openmrs database
* Currently duplicate value checking is handled in a simplistic fashion (each column can be marked as "Disallow Duplicate Values"; there could be potential problems for this, e.g., if multiple sites allow patients with the same Patient Identifier)
* I have made the test data per Darius' ideas here:
http://openmrs-mailing-list-archives.1560443.n2.nabble.com/Re-OpenMRS-Create-module-that-allows-users-to-import-data-via-XLS-files-td5434367.html#a5454907
Specifically, we import patient data along with two observations for the patient (both weights in this case).
Small caveat: under Administration -> Manage Observations, I get a Javascript error when trying to view the Observations (on trunk). However, I tried adding new observations through the GUI and get the same exact values in the obs table, so I do not believe the problem lies in my code. Please excuse if this is indeed something I am missing.
Potential changes/fixes:
* messages.properties currently not used. I got sick of this after all my rewrites of the code. Once there is a good version that works well and everyone is happy with, it will prob be fairly easy to move messages here and/or translate.
* current design is based on columns and "prespecified values" (e.g., concept). The current model uses dependencies on columns with other columns. In reality, this dependency really is on _tables_. I thought about this long and hard, but it seems to me, for an end user, the GUI is more straightforward if one thinks in terms of _columns_ within the spreadsheet, and not _tables_ in the OpenMRS database. Look forward to comments.
* the current model is quite simple in terms of privileges. I _do not_ check privileges for individual tables. Rather, I use a Import Spreadsheet Import Template privilege. My take on this was that, probably, at each site, only a few users would be allowed to import spreadsheets. In any case, if I am mistaken, it is pretty easy to change to a table by table version.
Other comments/questions:
* I have looked for a good Hibernate tool for Eclipse. Hibernate synchronizer http://sourceforge.net/projects/hibernatesynch/ seems quite nice but is quite old and, although has the capability to produce hibernate XML files automatically! from a database, in practice this did not seem to get great results for me. I spent about 30-45 minutes trying to get Hibernate Tools http://www.hibernate.org/subprojects/tools.html to work, but: a) the 3.6.0 version on Eclipse fails to install for me (http://download.jboss.org/jbosstools/updates/development update site) gets stuck at Calculating Requirements and dependencies...; I even tried installing Eclipse 3.3 but Hibernate Tools did not seem to install cleanly on that).
* All code is found at:
http://svn.openmrs.org/openmrs-modules/spreadsheetimport/trunk/
along with test data.
References
...