Importing Organisation Units

Support - Assistance technique
1

Devs,

Please assist, I have a list of organisation units in csv format which number about 5000 and I want to import them into DHIS2 how do I go about it, I have been trying the native import export and also the Web API, unfortunately I cannot get a head start, is the a native DHIS2 way to achieve this automatically or some other way using the Web API. Please find attached my csv file.

Regards,

orgunits.csv (83 KB)

2

Hi Judge,
I am sitting here behind the computer doing such a task right now, so thought I would with you my approach.

There are two major ways to do this, either with the DXF importer, or by direct injection with SQL into the database. I personally prefer the second approach, but this is really a personal preference.

For the first approach, you will need to transform your data into an appropriate XML structure. If you look at the WepAPI and look into the organisation units (for instance on the demo database) you will get a good idea of how the XML needs to be structured. You could choose numerous different ways to do this, but likely a scripting language would be appropriate. I prefer R personally for this, while others may use which every language they may know (Python, TCL, etc) . Once you create the XML, you can upload it to your DHIS following this procedure from the docs.

The SQL route is basically the same. Just transform the CSV into INSERT statements to mimic the “organisationunit” table. There are some required fields (name, shortname and organisationunitid). If you are using Postgresql, you can use the “nextval(‘hibernate_sequence’::regclass)” structure to get an ID for your organisation unit. Some other fields like “active” and “openingdate” are not required on the database schema, but cause problems when they do not exist, so it would be best to put them in. Again, the choice of how you create the SQL is really up to you, but again, a scripting language would probably be a good alternative. Once you have the INSERT statements, you just execute them on your database, and then restart DHIS2. This route is a bit riskier, because you are working directly with the database, so the XML route (even though it is a bit more difficult to work up I think) is safer. Obviously, you will need access to the backend system.

I have heard of others using Access somehow to work up the data, and then transfer this over somehow, but I have never seen this in action, but maybe others can describe this process.

Lastly, if you have data in DHIS 1.4, you can just simply import the metadata as described here.

Those are the ways I know of anyway. Hope this helps.

Regards,

Jason

¡¡¡

On Wed, Aug 7, 2013 at 3:28 PM, Judge Muzinda jmuzinda@gmail.com wrote:

Devs,

Please assist, I have a list of organisation units in csv format which number about 5000 and I want to import them into DHIS2 how do I go about it, I have been trying the native import export and also the Web API, unfortunately I cannot get a head start, is the a native DHIS2 way to achieve this automatically or some other way using the Web API. Please find attached my csv file.

Regards,

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

1 Like
3

Hi Jason,

Thank you so much for the detailed explanation, I have had some

difficulties with DFXso I followed the SQL way and it has worked
well. By the way I am using Mysql.

Regards,
¡¡¡

On 07/08/2013 15:39, Jason Pickering
wrote:

Hi Judge,
I am sitting here behind the computer doing such a task
right now, so thought I would with you my approach.

      There are two major ways to do this, either with the DXF

importer, or by direct injection with SQL into the database. I
personally prefer the second approach, but this is really a
personal preference.

      For the first approach, you will need to transform your

data into an appropriate XML structure. If you look at the
WepAPI and look into the organisation units (for instance on
the demo database) you will get a good idea of how the XML
needs to be structured. You could choose numerous different
ways to do this, but likely a scripting language would be
appropriate. I prefer R personally for this, while others may
use which every language they may know (Python, TCL, etc) .
Once you create the XML, you can upload it to your DHIS
following this
procedure from the docs.

      The SQL route is basically the same. Just transform the CSV

into INSERT statements to mimic the “organisationunit” table.
There are some required fields (name, shortname and
organisationunitid). If you are using Postgresql, you can use
the “nextval(‘hibernate_sequence’::regclass)” structure to get
an ID for your organisation unit. Some other fields like
“active” and “openingdate” are not required on the database
schema, but cause problems when they do not exist, so it would
be best to put them in. Again, the choice of how you create
the SQL is really up to you, but again, a scripting language
would probably be a good alternative. Once you have the INSERT
statements, you just execute them on your database, and then
restart DHIS2. This route is a bit riskier, because you are
working directly with the database, so the XML route (even
though it is a bit more difficult to work up I think) is
safer. Obviously, you will need access to the backend system.

      I have heard of others using Access somehow to work up the

data, and then transfer this over somehow, but I have never
seen this in action, but maybe others can describe this
process.

      Lastly, if you have data in DHIS 1.4, you can just simply

import the metadata as described here.

Those are the ways I know of anyway. Hope this helps.

Regards,

Jason

      On Wed, Aug 7, 2013 at 3:28 PM, Judge

Muzinda jmuzinda@gmail.com
wrote:

Devs,

        Please assist, I have a list of organisation units in csv

format which number about 5000 and I want to import them
into DHIS2 how do I go about it, I have been trying the
native import export and also the Web API, unfortunately I
cannot get a head start, is the a native DHIS2 way to
achieve this automatically or some other way using the Web
API. Please find attached my csv file.

        Regards,



        _______________________________________________

        Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)

        Post to     : dhis2-devs@lists.launchpad.net

        Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)

        More help   : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
4

Jason’s explanation is very comprehensive, I would just like to add that you would not necessarily need to employ a scripting language, as it is relatively straightforward to generate the required XML or SQL strings e.g. by just having a formula with an INSERT string in one column and filling in OU name etc from other columns.

We also have plans for an app to facilitate the loading of metadata such as orgunit hierarchies directly from Excel.

¡¡¡

On Wed, Aug 7, 2013 at 4:20 PM, Judge Muzinda jmuzinda@gmail.com wrote:

Hi Jason,

Thank you so much for the detailed explanation, I have had some difficulties with DFXso I followed the SQL way and it has worked well. By the way I am using Mysql.



Regards,

  On 07/08/2013 15:39, Jason Pickering wrote:

Hi Judge,
I am sitting here behind the computer doing such a task right now, so thought I would with you my approach.

      There are two major ways to do this, either with the DXF importer, or by direct injection with SQL into the database. I personally prefer the second approach, but this is really a personal preference.

      For the first approach, you will need to transform your data into an appropriate XML structure. If you look at the WepAPI and look into the organisation units (for instance on the demo database) you will get a good idea of how the XML needs to be structured. You could choose numerous different ways to do this, but likely a scripting language would be appropriate. I prefer R personally for this, while others may use which every language they may know (Python, TCL, etc) . Once you create the XML, you can upload it to your DHIS following [            this procedure](http://www.dhis2.org/doc/snapshot/en/user/html/ch25s05.html) from the docs.

      The SQL route is basically the same. Just transform the CSV into INSERT statements to mimic the "organisationunit" table. There are some required fields (name, shortname and organisationunitid). If you are using Postgresql, you can use the "nextval('hibernate_sequence'::regclass)" structure to get an ID for your organisation unit. Some other fields like "active" and "openingdate" are not required on the database schema, but cause problems when they do not exist, so it would be best to put them in.  Again, the choice of how you create the SQL is really up to you, but again, a scripting language would probably be a good alternative. Once you have the INSERT statements, you just execute them on your database, and then restart DHIS2. This route is a bit riskier, because you are working directly with the database, so the XML route (even though it is a bit more difficult to work up I think) is safer. Obviously, you will need access to the backend system.

      I have heard of others using Access somehow to work up the data, and then transfer this over somehow, but I have never seen this in action, but maybe others can describe this process.

      Lastly, if you have data in DHIS 1.4, you can just simply import the metadata as described [here.](http://here.)

Those are the ways I know of anyway. Hope this helps.

Regards,

Jason

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

      On Wed, Aug 7, 2013 at 3:28 PM, Judge Muzinda <jmuzinda@gmail.com>
      wrote:

Devs,

        Please assist, I have a list of organisation units in csv format which number about 5000 and I want to import them into DHIS2 how do I go about it, I have been trying the native import export and also the Web API, unfortunately I cannot get a head start, is the a native DHIS2 way to achieve this automatically or some other way using the Web API. Please find attached my csv file.



        Regards,



        _______________________________________________

        Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)

        Post to     : dhis2-devs@lists.launchpad.net

        Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)

        More help   : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
5

The other way which I forgot to mention, but which can be useful, especially if you have shape files, is the use of the GML importer. There are numerous tools which you can use to convert the shape files to GML, which can then be loaded into the system with the “GML import” functionality.

Sounds like you managed though. :slight_smile:

Regards,

Jason

¡¡¡

On Wed, Aug 7, 2013 at 4:29 PM, Knut Staring knutst@gmail.com wrote:

Jason’s explanation is very comprehensive, I would just like to add that you would not necessarily need to employ a scripting language, as it is relatively straightforward to generate the required XML or SQL strings e.g. by just having a formula with an INSERT string in one column and filling in OU name etc from other columns.

We also have plans for an app to facilitate the loading of metadata such as orgunit hierarchies directly from Excel.

On Wed, Aug 7, 2013 at 4:20 PM, Judge Muzinda jmuzinda@gmail.com wrote:

Hi Jason,

Thank you so much for the detailed explanation, I have had some difficulties with DFXso I followed the SQL way and it has worked well. By the way I am using Mysql.



Regards,

  On 07/08/2013 15:39, Jason Pickering wrote:

Hi Judge,
I am sitting here behind the computer doing such a task right now, so thought I would with you my approach.

      There are two major ways to do this, either with the DXF importer, or by direct injection with SQL into the database. I personally prefer the second approach, but this is really a personal preference.

      For the first approach, you will need to transform your data into an appropriate XML structure. If you look at the WepAPI and look into the organisation units (for instance on the demo database) you will get a good idea of how the XML needs to be structured. You could choose numerous different ways to do this, but likely a scripting language would be appropriate. I prefer R personally for this, while others may use which every language they may know (Python, TCL, etc) . Once you create the XML, you can upload it to your DHIS following [            this procedure](http://www.dhis2.org/doc/snapshot/en/user/html/ch25s05.html) from the docs.

      The SQL route is basically the same. Just transform the CSV into INSERT statements to mimic the "organisationunit" table. There are some required fields (name, shortname and organisationunitid). If you are using Postgresql, you can use the "nextval('hibernate_sequence'::regclass)" structure to get an ID for your organisation unit. Some other fields like "active" and "openingdate" are not required on the database schema, but cause problems when they do not exist, so it would be best to put them in.  Again, the choice of how you create the SQL is really up to you, but again, a scripting language would probably be a good alternative. Once you have the INSERT statements, you just execute them on your database, and then restart DHIS2. This route is a bit riskier, because you are working directly with the database, so the XML route (even though it is a bit more difficult to work up I think) is safer. Obviously, you will need access to the backend system.

      I have heard of others using Access somehow to work up the data, and then transfer this over somehow, but I have never seen this in action, but maybe others can describe this process.

      Lastly, if you have data in DHIS 1.4, you can just simply import the metadata as described [here.](http://here.)

Those are the ways I know of anyway. Hope this helps.

Regards,

Jason

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

–

      On Wed, Aug 7, 2013 at 3:28 PM, Judge Muzinda <jmuzinda@gmail.com>
      wrote:

Devs,

        Please assist, I have a list of organisation units in csv format which number about 5000 and I want to import them into DHIS2 how do I go about it, I have been trying the native import export and also the Web API, unfortunately I cannot get a head start, is the a native DHIS2 way to achieve this automatically or some other way using the Web API. Please find attached my csv file.



        Regards,



        _______________________________________________

        Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)

        Post to     : dhis2-devs@lists.launchpad.net

        Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)

        More help   : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)
6

Hi Jason,

Thank you so much, the import worked well, all the imported

organisation now have uids but unfortunately tomcat is now taking a
very long time to start and sometimes not starting at all, I have
checked tomcat logs but they are not indicating the problem, I had a
hunch that it was because of the number of organisation units, I
have 5690 units and my Java opts are -Xms512m -Xmx1024m
-XX:PermSize=512m -XX:MaxPermSize=1024m , can it be an issue to do
with memory, however tomcat is not throwing the OutOfMemory
exception, just the tocat failed to start message.

Regards,
¡¡¡

On 08/08/2013 08:12, Jason Pickering
wrote:

    The other way which I forgot to mention, but which

can be useful, especially if you have shape files, is the use of
the GML importer. There are numerous tools which you can use to
convert the shape files to GML, which can then be loaded into
the system with the “GML import” functionality.

Sounds like you managed though. :slight_smile:

Regards,

Jason

      On Wed, Aug 7, 2013 at 4:29 PM, Knut

Staring knutst@gmail.com
wrote:

          Jason's explanation is very comprehensive, I

would just like to add that you would not necessarily need
to employ a scripting language, as it is relatively
straightforward to generate the required XML or SQL
strings e.g. by just having a formula with an INSERT
string in one column and filling in OU name etc from other
columns.

            We also have plans for an app to facilitate the

loading of metadata such as orgunit hierarchies directly
from Excel.

On Wed, Aug 7, 2013 at 4:20 PM, Judge Muzinda jmuzinda@gmail.com
wrote:

Hi Jason,

                    Thank you so much for the detailed explanation,

I have had some difficulties with DFXso I
followed the SQL way and it has worked well. By
the way I am using Mysql.

                    Regards,

                          On 07/08/2013 15:39, Jason Pickering

wrote:

Hi Judge,
I am sitting here behind the
computer doing such a task right now,
so thought I would with you my
approach.

                              There are two major ways to do

this, either with the DXF importer, or
by direct injection with SQL into the
database. I personally prefer the
second approach, but this is really a
personal preference.

                              For the first approach, you will

need to transform your data into an
appropriate XML structure. If you look
at the WepAPI and look into the
organisation units (for instance on
the demo database) you will get a good
idea of how the XML needs to be
structured. You could choose numerous
different ways to do this, but likely
a scripting language would be
appropriate. I prefer R personally for
this, while others may use which every
language they may know (Python, TCL,
etc) . Once you create the XML, you
can upload it to your DHIS following this procedure
from the docs.

                              The SQL route is basically the

same. Just transform the CSV into
INSERT statements to mimic the
“organisationunit” table. There are
some required fields (name, shortname
and organisationunitid). If you are
using Postgresql, you can use the
“nextval(‘hibernate_sequence’::regclass)”
structure to get an ID for your
organisation unit. Some other fields
like “active” and “openingdate” are
not required on the database schema,
but cause problems when they do not
exist, so it would be best to put them
in. Again, the choice of how you
create the SQL is really up to you,
but again, a scripting language would
probably be a good alternative. Once
you have the INSERT statements, you
just execute them on your database,
and then restart DHIS2. This route is
a bit riskier, because you are working
directly with the database, so the XML
route (even though it is a bit more
difficult to work up I think) is
safer. Obviously, you will need access
to the backend system.

                              I have heard of others using Access

somehow to work up the data, and then
transfer this over somehow, but I have
never seen this in action, but maybe
others can describe this process.

                              Lastly, if you have data in DHIS

1.4, you can just simply import the
metadata as described here.

                              Those are the ways I know of

anyway. Hope this helps.

Regards,

Jason

                              On Wed, Aug 7,

2013 at 3:28 PM, Judge Muzinda jmuzinda@gmail.com
wrote:

Devs,

                                Please assist, I have a list of

organisation units in csv format
which number about 5000 and I want
to import them into DHIS2 how do I
go about it, I have been trying the
native import export and also the
Web API, unfortunately I cannot get
a head start, is the a native DHIS2
way to achieve this automatically or
some other way using the Web API.
Please find attached my csv file.

                                Regards,

                                Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)

                                Post to     : dhis2-devs@lists.launchpad.net

                                Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)

                                More help   : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)

                  _______________________________________________

                  Mailing list: [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)

                  Post to     : dhis2-devs@lists.launchpad.net

                  Unsubscribe : [https://launchpad.net/~dhis2-devs](https://launchpad.net/%7Edhis2-devs)

                  More help   : [https://help.launchpad.net/ListHelp](https://help.launchpad.net/ListHelp)

Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

–

7

Hello Jason:
Your text about importing organisation units has been very useful to third parties like myself. In line with what you've just said here re GML conversions: we just finished setting up our GIS, but we found the GML conversion technique that's stated in the manual a challenge. We are proposing to re-write the manual and would be very greatful if you could tell the other ways GML files are converted so we can include them in the new manual in order to give new users a wider range of options. Thank you for your help!

¡¡¡

Sent from my BlackBerry wireless device from MTN

-----Original Message-----
From: Jason Pickering <jason.p.pickering@gmail.com>
Sender: "Dhis2-devs"
  <dhis2-devs-bounces+jeromeshaguy=gmail.com@lists.launchpad.net>Date: Thu, 8 Aug 2013 08:12:12
To: Knut Staring<knutst@gmail.com>
Cc: DHIS 2 developers<dhis2-devs@lists.launchpad.net>
Subject: Re: [Dhis2-devs] Importing Organisation Units

_______________________________________________
Mailing list: DHIS 2 developers in Launchpad
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : DHIS 2 developers in Launchpad
More help : ListHelp - Launchpad Help

8

Hi Jerome,

Your contributions to the manual would be of course most welcome to
everyone. Let me know if you need more info about making changes, but
it is described in detail here
(http://www.dhis2.org/doc/snapshot/en/user/html/apa.html\)

The other ways which I know about to transform data into GML include:

1) ogr2ogr (which of course is the method described in the manual)
2) Other GIS packages such as ArcGIS (commercial), FWTools (free),
QGIS (free) and Manifold (commercial) all work quite well.
3) There are a whole host of Spatial ETL tools out there which can do
the job. My personal choice would be FME from Safe Software, which is
commercial. GeoKettle is an open source alternative which is very
capable as well.
4) R and various packages which go along with it (maptools, rgdal,
XML) is also another alternative.

I am sure there are other methods.

So, there is no set recipe. It really depends on which tools you are
most familiar with and have at your disposal. It would be great to
have a "Shape file loader" or "Excel loader" which would do this job
for you, but normally, there is a lot of work which needs to go into
being sure the source data is clean enough to load into DHIS2. Again,
there has been no set recipe I have seen, and will always depend on
the data source and how "clean" it is with respect to what needs to be
loaded into DHIS2.

Best regards,
Jason

¡¡¡

On 8/8/13, jeromeshaguy@gmail.com <jeromeshaguy@gmail.com> wrote:

Hello Jason:
Your text about importing organisation units has been very useful to third
parties like myself. In line with what you've just said here re GML
conversions: we just finished setting up our GIS, but we found the GML
conversion technique that's stated in the manual a challenge. We are
proposing to re-write the manual and would be very greatful if you could
tell the other ways GML files are converted so we can include them in the
new manual in order to give new users a wider range of options. Thank you
for your help!
Sent from my BlackBerry wireless device from MTN

-----Original Message-----
From: Jason Pickering <jason.p.pickering@gmail.com>
Sender: "Dhis2-devs"
  <dhis2-devs-bounces+jeromeshaguy=gmail.com@lists.launchpad.net>Date: Thu, 8
Aug 2013 08:12:12
To: Knut Staring<knutst@gmail.com>
Cc: DHIS 2 developers<dhis2-devs@lists.launchpad.net>
Subject: Re: [Dhis2-devs] Importing Organisation Units

_______________________________________________
Mailing list: DHIS 2 developers in Launchpad
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : DHIS 2 developers in Launchpad
More help : ListHelp - Launchpad Help

--
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260974901293

9

Thanks Jason,
I personally found ogr2ogr a challenge on my machine. Which is why I was suggesting a range of options. Will explore these. Thanks again.

¡¡¡

Sent from my BlackBerry wireless device from MTN

-----Original Message-----
From: Jason Pickering <jason.p.pickering@gmail.com>
Date: Thu, 8 Aug 2013 10:18:47
To: <jerome.shaguy@gmail.com>
Cc: Dhis2-devs<dhis2-devs-bounces+jeromeshaguy=gmail.com@lists.launchpad.net>; Knut Staring<knutst@gmail.com>; DHIS 2 developers<dhis2-devs@lists.launchpad.net>
Subject: Re: [Dhis2-devs] Importing Organisation Units

Hi Jerome,

Your contributions to the manual would be of course most welcome to
everyone. Let me know if you need more info about making changes, but
it is described in detail here
(http://www.dhis2.org/doc/snapshot/en/user/html/apa.html\)

The other ways which I know about to transform data into GML include:

1) ogr2ogr (which of course is the method described in the manual)
2) Other GIS packages such as ArcGIS (commercial), FWTools (free),
QGIS (free) and Manifold (commercial) all work quite well.
3) There are a whole host of Spatial ETL tools out there which can do
the job. My personal choice would be FME from Safe Software, which is
commercial. GeoKettle is an open source alternative which is very
capable as well.
4) R and various packages which go along with it (maptools, rgdal,
XML) is also another alternative.

I am sure there are other methods.

So, there is no set recipe. It really depends on which tools you are
most familiar with and have at your disposal. It would be great to
have a "Shape file loader" or "Excel loader" which would do this job
for you, but normally, there is a lot of work which needs to go into
being sure the source data is clean enough to load into DHIS2. Again,
there has been no set recipe I have seen, and will always depend on
the data source and how "clean" it is with respect to what needs to be
loaded into DHIS2.

Best regards,
Jason

On 8/8/13, jeromeshaguy@gmail.com <jeromeshaguy@gmail.com> wrote:

Hello Jason:
Your text about importing organisation units has been very useful to third
parties like myself. In line with what you've just said here re GML
conversions: we just finished setting up our GIS, but we found the GML
conversion technique that's stated in the manual a challenge. We are
proposing to re-write the manual and would be very greatful if you could
tell the other ways GML files are converted so we can include them in the
new manual in order to give new users a wider range of options. Thank you
for your help!
Sent from my BlackBerry wireless device from MTN

-----Original Message-----
From: Jason Pickering <jason.p.pickering@gmail.com>
Sender: "Dhis2-devs"
  <dhis2-devs-bounces+jeromeshaguy=gmail.com@lists.launchpad.net>Date: Thu, 8
Aug 2013 08:12:12
To: Knut Staring<knutst@gmail.com>
Cc: DHIS 2 developers<dhis2-devs@lists.launchpad.net>
Subject: Re: [Dhis2-devs] Importing Organisation Units

_______________________________________________
Mailing list: DHIS 2 developers in Launchpad
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : DHIS 2 developers in Launchpad
More help : ListHelp - Launchpad Help

--
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260974901293