1. Site assessment
2. Baseline assessment
3. Flow monitoring
4. Survey

A second analysis was performed on a selection of datasets labelled as site assessment:

IOM DTM Ecuador - Site Assessment Data - Spontaneous Sites

Data Structure - Third Analysis

IOM suggested looking at baseline rather than site assessment data.

DTM Cameroon Round III - Baseline data

File: DTM_CMR_Baseline_Analysis_Round3_V3_ED_AA v4.xlsx

This is at an aggregated level and has 3 sheets with the main one "uploaded_form_izr7j1" containing the header:

There is a sheet with the list of respondents including their names. Should this be publicly available?

IOM DTM Somalia - Site Assessment Data

File: rd10_DTM_Master_List_Jun2016.xlsx

Haiti - IOM DTM Dataset (June 2016) - Site assessment data

File: rd25_DTM_Master_List_Mar2016.xlsx

has multiple sheets with data in a main sheet called "dashboard" that has a single line header:

IOM DTM - Mosul crisis Baseline data

File: EmergencyTracking_DTM_IOM_IDPs_Dataset.xls

This is at an aggregated level and has just one sheet named "sheet" with single line header:

Libya - IOM DTM Dataset (June 2016) - Baseline data

File: rd4_DTM_Master_List_Jun2016.xlsx

This is at an aggregated level and has multiple sheets with the main one "1-DTM Round 4 Dataset" containing the two line header:

Yemen - IOM TFPM DTM Dataset (April 2016) - Baseline data

File: r9_TFPM_Master_List_Apr2016.xlsx

This is at an aggregated level and has multiple sheets with data split across two main sheets:

"IDP Location lvl" containing the single line header:

"Returnees Location lvl" containing the single line header:

Common Fields

From the headers above, there are certain fields which are common to all. These are:

1. Location Name (ward, district, camp name site name,  ADM3_Baladiya_Name_EN etc.)
2. Families/Households
   
3. Individuals
4. 
   

Similar Fields

The below fields communicate similar information but are not consistent between the spreadsheets:

1. Shelter (either a number of a type) 
2. Toilet availability (either a number or yes/no)
3. Water provision (yes/no or various inconsistent values expressing onsite, offsite and/or distance from water)Waste management (yes/no or a type)
   

Conclusion

Given the lack of any consistency between the DTM Master List spreadsheets, writing a one size fits all automated data checker and cleaner for all of them is challenging. It would involve placing a great deal of "intelligence" into the cleaning program with the possibility that errors are introduced during cleaning for example, by accidentally matching the wrong column heading when making an algorithm to match a very diverse range of names. It may be possible to write cleaners per country but the effort involved would be large.
A better approach is to try to encourage the different offices to use a similar template for their spreadsheets, as from this starting point, writing a cleaner would not be too onerous. If such a template were introduced, it could be HXLated from day one. How easy it would be to invent a template that covers the range of needs is debatable but it is likely to be much easier than trying to process greatly varying spreadsheet formats.