dhis2-users team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

Hi Alison

XML is not really a better file format.  There's nothing really wrong with
CSV for tabular data.

Whereas we don't always have control over the format of the CSV that we
get, sometimes it is possible to process your CSV with a small script to
convert it into dxf which can be imported.  The benefit of doing it this
way is that dhis2 will take care of the constraint violations Randy has
referred to.  Here's a small example (also from Rwanda):

1.  the file format:
FOSA Code       Year    Month   Indicator ID    Value   Facility Name
Indicator Name
54      2013    2       181     1       ClinicA       Number of new
discordant couples where male partners are HIV positive
54      2013    2       182     1       ClinicA       Number of new
discordant couples where female partners are HIV positive
54      2013    2       183     2       ClinicA       Number of new
discordant couples registered during this month
54      2013    2       188     37      ClinicA       Number of
serodiscordant couples followed at Health Facility this month
429     2013    2       181     1       ClinicB      Number of new
discordant couples where male partners are HIV positive
429     2013    2       182     0       ClinicB      Number of new
discordant couples where female partners are HIV positive
429     2013    2       183     1       ClinicB      Number of new
discordant couples registered during this month
...

Note that these fields are actually tab separated.  And, to make things
more awkward, the file is UTF-16 encoded :-(

2.  The 'FOSA Code' column corresponds the code field on our orgunits in
the dhis2 database.  And we have dataelements in our database which we have
assigned codes like 'DE_181', 'DE_182' etc corresponding to 'Indicator ID'
column in the CSV.

3.  We can use a short script to convert this into the following valid dxf
2.0 file:

<dataValueSet orgUnitIdScheme='code' dataElementIdScheme='code' xmlns='
http://dhis2.org/schema/dxf/2.0'>
 <dataValue orgUnit='54' period='2013-02' dataElement='TN181' value='1'/>
 <dataValue orgUnit='54' period='2013-02' dataElement='TN182' value='1'/>
 <dataValue orgUnit='54' period='2013-02' dataElement='TN183' value='2'/>
 <dataValue orgUnit='54' period='2013-02' dataElement='TN188' value='37'/>
 <dataValue orgUnit='429' period='2013-02' dataElement='TN181' value='1'/>
 <dataValue orgUnit='429' period='2013-02' dataElement='TN182' value='0'/>
 <dataValue orgUnit='429' period='2013-02' dataElement='TN183' value='1'/>
...
</dataValueSet>

4.  this can be imported into dhis2.

The script is shown below.  The requirement for iconv is a bit exotic and
its just because this file comes from a system which encodes the data in
UTF-16.  Normally you wouldn't need this.  Otherwise its using awk to
process the csv lines one by one.  You might be able to do something
similar with your csv file.

If you are not familiar with shell scripts or awk then this will look very
geekish.  But perhaps with this information you might get someone to help
you with the details of the conversion script.  The key thing is to have
the codes mapped in your dhis2 database.

#!/bin/bash
 #
--------------------------------------------------------------------------------------------
 # Convert from windoze utf16 format to ASCII, strip the ctrl-M characters
and generate dxf xml
 #   by processing csv using awk script
 #
--------------------------------------------------------------------------------------------
 iconv -f UTF-16 -t ASCII - |tr -d '\015' | awk -f <(cat - <<-'EOT'
 BEGIN {
   FS="\t";
   printf "<dataValueSet orgUnitIdScheme='code' dataElementIdScheme='code'
xmlns='http://dhis2.org/schema/dxf/2.0'>\n";
 }
 {
   if (NR!=1) {
       printf("<dataValue orgUnit='%s' period='%d-%02d' dataElement='TN%s'
value='%s'/>\n",$1, $2, $3, $4, $5)
   }
 }
 END {
   printf "</dataValueSet>\n"
 }
 EOT
 ) -


Regards
Bob



On 9 October 2013 17:10, Alison Perez <perez.alison@xxxxxxxxx> wrote:

> Greetings to the DHIS2 community. I wish to consult the group on strategy
> and general procedures on how to use a CSV file as a data source for DHIS2
> graphs. We have a local electronic medical record in our provincial
> hospital that generates a CSV file with each data set representing
> numerical values for an indicator. While XML might be a better file format,
> we are generating CSV due to the need to have the data be shared to another
> system.  I was hoping to utilize DHIS2's graphing capability by feeding the
> CSV to the DHIS2 engine. Is this possible and how?
>
> thank you very much.
>
>
>
> --
> Alison O. Perez
> Software Developer
> Wireless Access for Health (WAH)
> Province of Tarlac, Philippines
>
> Linkedin Profile: ph.linkedin.com/pub/alison-perez/56/44/a65/
> Personal Blog: http://darthblitzkrieg.blogspot.com
> ICT4D/H Blog: http://alisonperez.tk
> Skype / IM: darth_ali
>
> "Daring ideas are like chessmen moved forward; they may be beaten, but
> they
> may start a winning game." -- Johann Wolfgang Goethe*
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-users
> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-users
> More help   : https://help.launchpad.net/ListHelp
>
>