Introduction to excel based import

Supported entities: Individual (aka Subject Registration) | Encounter (aka Subject Encounter) | ProgramEnrolment | ProgramEncounter | Checklist (aka Vaccinations) | Relationship (aka IndividualRelationship, SubjectRelationship)

We can Import transactional data from excel files. Data can be Subject Registration, Enrolment, Encounters, Relationship between Subjects, or Vaccinations, etc. The data file, ideally, should have columns like, RegistrationDate, FirstName, LastName, DOB, .. in case of Registration, and SubjectUUID, DateOfEnrolment, Program, .. in case of Enrolment, and SubjectUUID, EnrolmentUUID, EncounterType, Name, .. for Encounters. Along with these default fields, all the observation specific to the implementation should be present in the data file.

Definition of those forms cannot be imported this way. Only the data recorded against those forms can be imported this way.

We need a metaData.xlsx file which would work as an adapter between the data.xlsx file and avni system.
The data.xlsx file will be provided by the org-admin which should have consistent and tabular data. The metaData.xlsx file defines the relationship between each column and its corresponding field in avni system/implementation.

Structure of metaData.xlsx file:

The following are the various spreadsheets within a metaData.xlsx file.

Sheets

Sheets represent a logical sheet of data. A physical sheet of data can be mapped to multiple logical sheets of data.

Column

Description

File Name

The data migration service is used by supplying the metadata excel file, a data excel file, and a fileName (since the server reads the data excel file via a stream it doesn't know the name of the file originally uploaded hence it needs to be explicitly provided).

Only the sheets which have the file name matching the fileName via the API would be imported.

User File Type

This is the unique name given to the file of specific types. There can be more than one physical file of the same type, in which case the user file type will be the same but file names will be different.

Sheet Name

This is the name of the actual sheet in the data file uploaded where from the data should be read.

Entity Type, Program Name and Visit Type, Address

Core but optional data to be provided depending on type of data being imported

Active

During data migration, it is possible that there are a lot of files and mapping-metadata definition for those files may not be complete. Active flag (Yes or No) can be used to disable sheets that need not be considered for migration when uploaded.

Name of fields

One can add multiple columns after this such that it matches the name of a System Field and provides the default value for the entire virtual sheet.

Sample

File NameUser File TypeSheet NameEntity TypeProgram NameVisit TypeActiveDate of Birth VerifiedSubjectTypeUUIDRegistration DateEnrolment Date
master_data_district_wise.xlsxRegistrationAhmedNagarIndividualNo8a9b0ef8-325b-4f75-8453-daeaf59df29d2019-01-01
master_data_district_wise.xlsxEnrolmentAhmedNagarProgramEnrolmentGDGS 2019No2019-01-01

Fields

The mapping for non-calculated fields

Column

Description

User File Type

This is same as in Sheets.

Form Type

[IndividualProfile, Encounter, ProgramEncounter, ProgramEnrolment, ProgramExit, ProgramEncounterCancellation, ChecklistItem, IndividualRelationship]

System Field

Concept name specified in the form.
Or Default field (this can be seen in different importers, See below ).

User Field

Name of the column from data.xlsx file

Default fields for each entity as of Dec 2019

Subject RegistrationEncounterEnrolmentProgramEncounterChecklistRelationship
First NameIndividual UUIDEnrolment UUIDEnrolment UUIDEnrolment UUIDEnterDateTime
Last NameUUIDIndividual UUIDUUIDBase DateExitDateTime
AgeVisit TypeEnrolment DateVisit TypeChecklist NameIndividualAUUID
Date of BirthEncounter DateTimeAddressVisit NameItem NameIndividualBUUID
Date of Birth VerifiedUserExit DateEarliest DateCompletion DateRelationshipType
GenderVoidedUserActual DateUserUser
Registration DateVoidedMax DateVoidedVoided
Address LevelAddress
AddressUUIDCancel Date
Individual UUIDUser
Catchment UUIDVoided
SubjectTypeUUID
User
Voided

Along with these, the implementation specific observations also to be mapped.

Sample

User File TypeForm TypeSystem FieldUser Field
RegistrationIndividualProfileIndividual UUIDSiteUUID
RegistrationIndividualProfileFirst NameSite
RegistrationIndividualProfileAddressUUIDVillageUUID
RegistrationIndividualProfileType of waterbodyType
RegistrationIndividualProfileConcerned Govt. Dept.Concerned Govt. Dept.
EnrolmentProgramEnrolmentSilt Estimation as per work planEstimated quantitiy of Silt Cu m
EnrolmentProgramEnrolmentIndividual UUIDSiteUUID
EnrolmentProgramEnrolmentEnrolment UUIDEnrolmentUUID

An example of Metadata.xlsx file
An example of Data.xlsx file

🚧

UUIDs in Data.xlsx file

Note that

  • Individual UUID (aka Subject UUID, in this example called SiteUUID), EnrolmentUUID, or any will have to be manually assigned by the developer before import.
    • Use tools like uuid: npm i -g uuid.
    • for n in {1..100}; do uuidgen -r; done #to get 100 uuids from CLI
  • AddressUUID (or Village UUID) will not be available when the data file is provided by the Implmentation. And has to be determined from the Full Address details (see example Data.xlsx).
    • For this get all locations and it's uuid into a Ref Sheet in data.xlsx file
    • do vlookup for uuid by full address details