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)

❗️

Avni does not support Excel based import any longer, please refer to Admin App based approach to upload data Bulk Data Upload page

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.

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 the data should be read.
Entity Type, Program Name and Visit Type, Address	Core but optional data to be provided depending on the 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 Name	User File Type	Sheet Name	Entity Type	Program Name	Visit Type	Active	Date of Birth Verified	SubjectTypeUUID	Registration Date	Enrolment Date
master_data_district_wise.xlsx	Registration	AhmedNagar	Individual			No		8a9b0ef8-325b-4f75-8453-daeaf59df29d	YYYY-MM-DD
master_data_district_wise.xlsx	Enrolment	AhmedNagar	ProgramEnrolment	GDGS 2019		No				YYYY-MM-DD

Fields

The mapping for non-calculated fields

Column	Description
User File Type	This is the same as in Sheets.
Form Type	[IndividualProfile, Encounter, ProgramEncounter, ProgramEnrolment, ProgramExit, ProgramEncounterCancellation, ChecklistItem, IndividualRelationship]
System Field	The concept name is 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 Registration	Encounter	Enrolment	ProgramEncounter	Checklist	Relationship
First Name	Individual UUID	Enrolment UUID	Enrolment UUID	Enrolment UUID	EnterDateTime
Last Name	UUID	Individual UUID	UUID	Base Date	ExitDateTime
Age	Visit Type	Enrolment Date	Visit Type	Checklist Name	IndividualAUUID
Date of Birth	Encounter DateTime	Address	Visit Name	Item Name	IndividualBUUID
Date of Birth Verified	User	Exit Date	Earliest Date	Completion Date	RelationshipType
Gender	Voided	User	Actual Date	User	User
Registration Date		Voided	Max Date	Voided	Voided
Address Level			Address
AddressUUID			Cancel Date
Individual UUID			User
Catchment UUID			Voided
SubjectTypeUUID
User
Voided

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

Sample

User File Type	Form Type	System Field	User Field
Registration	IndividualProfile	Individual UUID	SiteUUID
Registration	IndividualProfile	First Name	Site
Registration	IndividualProfile	AddressUUID	VillageUUID
Registration	IndividualProfile	Type of waterbody	Type
Registration	IndividualProfile	Concerned Govt. Dept.	Concerned Govt. Dept.
Enrolment	ProgramEnrolment	Silt Estimation as per the work plan	Estimated quantity of Silt cum
Enrolment	ProgramEnrolment	Individual UUID	SiteUUID
Enrolment	ProgramEnrolment	Enrolment UUID	EnrolmentUUID

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