Introduction to excel based import [Deprecated]

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, relationships between Subjects, 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 observations specific to the implementation should be present in the data file.

The 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 that would work as an adapter between the data.xlsx file and the 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 the 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.

ColumnDescription
File NameThe 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 TypeThis 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 NameThis is the name of the actual sheet in the data file uploaded where the data should be read.
Entity Type, Program Name and Visit Type, AddressCore but optional data to be provided depending on the type of data being imported
ActiveDuring 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 fieldsOne 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

ColumnDescription
User File TypeThis is the same as in Sheets.
Form Type[IndividualProfile, Encounter, ProgramEncounter, ProgramEnrolment, ProgramExit, ProgramEncounterCancellation, ChecklistItem, IndividualRelationship]
System FieldThe concept name is specified in the form.
Or Default field (this can be seen in different importers, See below ).
User FieldName 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 are 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 the work planEstimated quantity of Silt cum
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

Google Drive Files

For uploading files (images/documents) you can put the URL of the file. Please follow the following steps:

  • Ensure the drive file is shared without any restrictions
  • Copy the file link and use this website to get the link that can be put into the excel file to be uploaded - https://sites.google.com/site/gdocs2direct/?pli=1
  • Copy the link generated by the above website for your file and put it in the excel/CSV cell.

Technical link for Avni Team

The above website uses the following http request behind the scenes

curl '[https://www.google-analytics.com/g/collect?v=2&tid=G-KV5S9LK4WB>m=2oe1a1>\_p=437198370&gdid=dZWRiYj&cid=1650660276.1673947139&ul=en-gb&sr=1440x900&uaa=x86&uab=64&uafvl=Not%253FA_Brand%3B8.0.0.0%7CChromium%3B108.0.5359.124%7CGoogle%2520Chrome%3B108.0.5359.124&uamb=0&uam=&uap=macOS&uapv=10.14.6&uaw=0&\_s=1&sid=1673947138&sct=1&seg=1&dl=https%3A%2F%2Fsites.google.com%2Fsite%2Fgdocs2direct%2F%3Fpli%3D1&dr=https%3A%2F%2Fwww.google.com%2F&dt=Google%20Drive%20Direct%20Link%20Generator&en=page_view&\_ee=1](https://www.google-analytics.com/g/collect?v=2&tid=G-KV5S9LK4WB&gtm=2oe1a1&_p=437198370&gdid=dZWRiYj&cid=1650660276.1673947139&ul=en-gb&sr=1440x900&uaa=x86&uab=64&uafvl=Not%253FA_Brand%3B8.0.0.0%7CChromium%3B108.0.5359.124%7CGoogle%2520Chrome%3B108.0.5359.124&uamb=0&uam=&uap=macOS&uapv=10.14.6&uaw=0&_s=1&sid=1673947138&sct=1&seg=1&dl=https%3A%2F%2Fsites.google.com%2Fsite%2Fgdocs2direct%2F%3Fpli%3D1&dr=https%3A%2F%2Fwww.google.com%2F&dt=Google%20Drive%20Direct%20Link%20Generator&en=page_view&_ee=1)' -X 'POST' -H 'authority: www.google-analytics.com' -H 'accept: _/_' -H 'accept-language: en-GB,en;q=0.9,hi-IN;q=0.8,hi;q=0.7,en-US;q=0.6,de;q=0.5' -H 'content-length: 0' -H 'dnt: 1' -H 'origin: <https://sites.google.com>' -H 'referer: <https://sites.google.com/>' -H 'sec-ch-ua: "Not?A_Brand";v="8", "Chromium";v="108", "Google Chrome";v="108"' -H 'sec-ch-ua-mobile: ?0' -H 'sec-ch-ua-platform: "macOS"' -H 'sec-fetch-dest: empty' -H 'sec-fetch-mode: no-cors' -H 'sec-fetch-site: cross-site' -H 'user-agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36' --compressed


What’s Next