This function provides an interface for existing beneficiary data from an external data source to be imported into the system as a CSV (comma-separated values) file. The external data source may be an Excel spreadsheet, a database management system, or any other application that allows data to be exported as CSV files. Even Google Sheets provide the functionality to download data to CSV files. Given its widespread support and ease of use, CSV is often the most logical choice of file format for data transfer.

However, the simplicity and popularity of CSV do come with some tradeoffs. Because of its tabular flat file nature, using it for data that is organized hierarchically can become unwieldy. Such is the case with beneficiary data as modelled by this system where a one-to-many relationship exists between a household and its members. To avoid too much repetition of the same household data in each member record as a result of flattening the data structure, the Import Household Data process only accepts a subset of the household-level data. To further reduce the complexities of the process, only the most basic member details are captured by the process. The Required Columns section and the Conditional Columns section provide details on what exactly is captured.

Steps to Follow

• Read through this documentation to obtain a thorough understanding of all aspects of the import process, especially regarding the required data file format.
• Prepare the data source for the export to a CSV file by writing any required queries and/or performing any required data conversion to ensure the data complies with the requirements of the import process.
• Create the CSV file with the required header row and data columns. If the file is too big, it may be advisable to break it up into smaller files and import them one at a time. (Just remember to add the same header row to each file and do not split any households across different files.)
• Go to the Import Household Data page to import the CSV file.

Note that in the rest of this help documentation, a distinction is made between required columns and columns with required values: Required columns must always exist structurally in the CSV file but may be empty in some rows; columns with required values are those that do not allow missing values in any rows.

CSV is a rather loose standard in that many variations exist and are supported by different computer systems. In the context of importing household data, the following rules should be observed:

• The CSV file must have a header row, and column names are case-sensitive. See Conditional Columns and Required Columns for details on the columns.
• Each data row represents an active household member. (Inactive members should be excluded from the CSV file.) Rows are delimited by newline characters.
• If complete households are being imported, then the rows must be grouped by household, but the row order within each household is immaterial. (The household head does not need to come first.) See Demarcation Methods for help on how to delimit households.
• Columns must be separated by commas. Tabs and other delimiters such as semicolons are not supported. Column values containing embedded commas need to be enclosed in double quotes.
• Columns can be arranged in any order as long as all the required columns are present. What columns are required depends on what demarcation method is used.
• If extra columns are included in the file, they will simply be ignored, although they may incur a slight performance degradation due to the overhead needed to handle them.
• Date columns should use the short date format defined in the system configuration. As an example, today's date will appear as 2025-04-26 on this server.
• UTF-8 encoding (without BOM or signature) should be used for the file content when the data contains non-ASCII characters such as accented letters or foreign scripts.

Excel and UTF-8

When saving an Excel spreadsheet as a CSV file, there is no option to specify UTF-8 as the encoding, hence any non-ASCII characters will not be saved correctly in the CSV file.

The safest and easiest way to work around this issue is to use Google Sheets if a Google Drive account is available. Simply upload the Excel file to Google Drive, open it as a Google Sheet, and download it as a CSV file. Unlike Excel, Google will handle the character encoding correctly and automatically. Before the download, just ensure the correct date format has been applied to the date columns. It is also prudent to delete the temporary Google Sheet when it is no longer needed.

If using Google Sheets is not an available option, another workaround is to first save the Excel file as a Unicode Text (*.txt) file which uses tab characters as field delimiters. The file will then need to be converted to a CSV file using a text editor such as Notepad++ by replacing the tabs with commas.

Two modes of household data import are supported:

• A simplified head-only import mode where only the household heads are imported, and
• A full import mode where all active members of each household are imported.

In the first mode, each CSV record represents both a member and a complete household, so there is no issue there. In the second mode, however, since a household can span several member records, a convention needs to be established whereby the system can tell where a household ends and where the next household starts. This is referred to as the demarcation method of which there are two that can be followed as explained below.

Method 1 - Using Household IDs

Under this method, every member record will have an associated Household ID that identifies the household to which they belong. This can be any string that can serve as a unique household identifier. Since members of the same household should appear together in the CSV file, any change in the Household ID would indicate the start of a new household. The supplied Household ID values are solely used for the purpose of demarcation; they are not saved in the database. If the external data source of the beneficiary data contains a data field that can serve as the Household ID, then this method will be the preferred way to go.

Method 2 - Using Demarcation Flags

When no suitable candidates for Household ID are available, a Demarcation column can be used to flag where each household starts in the CSV file. This is simply an artificial column where only the first member of each household will have a value. Any arbitrary string can be used as a flag, and the value can even be different from one household to another. Every time a nonempty string is read from this column, the system will treat the current row as the first member of a new household.

See Conditional Columns for more information on the requirements of the full import mode.

The following table describes the data columns that are always required to be present in the CSV file regardless of the modality of the import operation. The last table column Required is used to indicate whether a value is required in every row of CSV data. The column itself must always be included in the CSV file even though the column value is optional. (The Account Holder column is the only exception.)

See Conditional Columns for information on columns that are only needed in certain cases depending on usage.

When importing complete households where every active member of each household is included in the CSV file, additional columns are required in the file. The following table describes what these columns are and when they are needed. Again, the Required table column indicates whether a value is required in every CSV row, not whether the column itself needs to exist in the file.

See Required Columns for information on columns that are always needed.