Bulk Load app 3.5.1 now available

Bulk Load app 3.5.1

Bulk Load is a DHIS2 Web Application to generate templates (Excel sheets) for datasets and programs and import multiple data values (as aggregated or events) into DHIS2 instances.

Bulk Load can:

  • Generate on-the-fly an Excel template reading the metadata from a program/dataset
  • Populate the template with the data the server has
  • Load at once all data in filled in the templates detecting overrides and duplicates

We are announcing the new release version 3.5.1 of Bulk Load.

View on Github

New Features

This new version of Bulk Load app includes the following new features:

  • Add Attribute option combo for data sets
  • Mandatory fields are labelled with a * in the excel template
  • Add Portuguese as a language
  • Add translations for Portuguese, French and Spanish
  • Bugfixes: please find the detail of bugs in the detailed release notes

  • Release Information Links
    Installable zip file Download
    Source code on Github Download

    Funded by:
    Developed by:
    11 Likes

    Thanks, exactly what we need.

    2 Likes

    @ifoche this great, as others say this is what we all were looking for. can event id be blank when we populate the template? or is there a way to generate event id?
    thanks,
    Derebe

    @Derebe Thank you! It’s been a hard work from our team and WHO that funded and co-designed the tool

    Sure you can leave event id blank and the system will understand that it is a new event what you provide. To populate the template with event ids, you just have to check the checkbox “Populate template with data” when generating the template. Then, if any data exists for the given dates, the events will be populated in the template, so you can decide to modify them offline and later upload them back to the platform.

    You will see that there’s a setting defining the detection of duplicates. The app will detect:

    1. If there is data with event UID…which will only be updated instead of generating duplicates
    2. Will check whethere there is any existing event that can be consider as a duplicate of what you sent and propose you to continue loading them, import only what’s not a duplicate or cancel the operation

    The definition of duplicates is also a setting. You can decide which DEs are considered when analyzing what’s a duplicate, and you can also define the time frame to look for a duplicate around every event.

    I hope you’ll like it!

    3 Likes

    Thank you gain @ifoche I was trying to import and I encountered duplicate records imported. any advice on how to remove duplicates?

    Hi @Derebe. Unfortunately, the app can detect duplicates of what you’re about to import and avoid loading them into the system if you don’t want to do it, but it doesn’t have any feature to deal with what’s already in the system (removing previously existing duplicates). However, if you didn’t tell the app to go ahead with the import of duplicates, it shouldn’t do it.

    3 Likes

    @ifoche Thank you again I am using the app frequently. Today I got the below error. it seems it is related to option sets. can help what excatly is the issues and what should I do.
    ${programStage.executionDateLabel ?? “Date”} *`
    );

            if (programStage.programStageSections.length === 0) {
                programStage.programStageSections.push({
                    dataElements: programStage.programStageDataElements.map(e => e.dataElement),
                    id: programStageT.id,
                });
            }
    
            _.forEach(programStage.programStageSections, programStageSectionT => {
                const programStageSection = programStageSectionT.dataElements
                    ? programStageSectionT
                    : metadata.get(programStageSectionT.id);
                const firstColumnId = columnId;
    
                _.forEach(programStageSection.dataElements, dataElementT => {
                    const dataElement = metadata.get(dataElementT.id);
                    const { name, description } = this.translate(dataElement);
    
                    const validation = dataElement.optionSet
                        ? dataElement.optionSet.id
                        : dataElement.valueType;
                    this.createColumn(
                        dataEntrySheet,
                        itemRow,
                        columnId,
                        `_${dataElement.id}`,
                        groupId,
                        this.validations.get(validation)
                    );
                    dataEntrySheet.column(columnId).setWidth(name.length / 2.5 + 10);
    
                    if (description !== undefined) {
                        dataEntrySheet.cell(itemRow, columnId).comment(description, {
                            height: "100pt",
                            width: "160pt",
                        });
                    }
    
                    columnId++;
                });
    
                if (firstColumnId < columnId)
                    dataEntrySheet
                        .cell(sectionRow, firstColumnId, sectionRow, columnId - 1, true)
                        .formula(`_${programStageSection.id}`)
                        .style(this.groupStyle(groupId));
    
                groupId++;
            });
        });
    }
    

    };

    SheetBuilder.prototype.toBlob = async function () {
    try {
    const data = await this.workbook.writeToBuffer();
    return new Blob([data], {
    type: “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”,
    });
    } catch (error) {
    console.error(“Failed building/downloading template”);
    throw error;
    }
    };

    SheetBuilder.prototype.translate = function (item) {
    const { elementMetadata, language } = this.builder;
    const translations = item?.translations?.filter(({ locale }) => locale === language) ?? [];

    const { value: formName } = translations.find(({ property }) => property === "FORM_NAME") ?? {};
    const { value: regularName } = translations.find(({ property }) => property === "NAME") ?? {};
    const { value: shortName } =
        translations.find(({ property }) => property === "SHORT_NAME") ?? {};
    
    const defaultName = item?.displayName ?? item?.formName ?? item?.name;
    const name = formName ?? regularName ?? shortName ?? defaultName;
    
    const { value: description = item?.description } =
        translations.find(({ property }) => property === "DESCRIPTION") ?? {};
    
    if (item?.type === "categoryOptionCombos" && name === defaultName) {
        const options = item?.categoryOptions?.map(({ id }) => {
            const element = elementMetadata.get(id);
            const { name } = this.translate(element);
            return name;
        });
    
        return { name: options.join(", "), description };
    } else {
        return { name, description };
    }
    

    };

    SheetBuilder.prototype.createColumn = function (
    sheet,
    rowId,
    columnId,
    label,
    groupId = null,
    validation = null,
    validationMessage = “Invalid choice was chosen”,
    defaultLabel = false
    ) {
    sheet.column(columnId).setWidth(20);
    const cell = sheet.cell(rowId, columnId);

    if (!defaultLabel) cell.style(groupId !== null ? this.groupStyle(groupId) : baseStyle);
    else {
        cell.style(groupId !== null ? this.groupStyle(groupId) : baseStyle).style(
            this.transparentFontStyle(groupId)
        );
    }
    
    if (label.startsWith("_")) cell.formula(label);
    else cell.string(label);
    
    if (validation !== null) {
        const ref = `${Excel.getExcelAlpha(columnId)}${rowId + 1}:${Excel.getExcelAlpha(
            columnId
        )}1048576`;
        sheet.addDataValidation({
            type: "list",
            allowBlank: true,
            error: validationMessage,
            errorStyle: "warning",
            showDropDown: true,
            sqref: ref,
            formulas: [validation.toString()],
        });
    
        sheet.addConditionalFormattingRule(ref, {
            type: "expression", // the conditional formatting type
            priority: 1, // rule priority order (required)
            formula: `ISERROR(MATCH(${Excel.getExcelAlpha(columnId)}${
                rowId + 1
            },${validation.toString().substr(1)},0))`, // formula that returns nonzero or 0
            style: this.workbook.createStyle({
                font: {
                    bold: true,
                    color: "FF0000",
                },
            }), // a style object containing styles to apply
        });
    }
    

    };

    SheetBuilder.prototype.transparentFontStyle = function (groupId) {
    const { palette = defaultColorScale } = this.builder.theme ?? {};

    return {
        font: {
            color: palette[groupId % palette.length],
        },
    };
    

    };

    SheetBuilder.prototype.groupStyle = function (groupId) {
    const { palette = defaultColorScale } = this.builder.theme ?? {};
    return {
    …baseStyle,
    fill: {
    type: “pattern”,
    patternType: “solid”,
    fgColor: palette[groupId % palette.length],
    },
    };
    };

    /**

    • Common cell style definition
    • @type {{alignment: {horizontal: string, vertical: string, wrapText: boolean, shrinkToFit: boolean}}}
      */
      const baseStyle = {
      alignment: {
      horizontal: “center”,
      vertical: “center”,
      wrapText: true,
      shrinkToFit: true,
      },
      fill: {
      type: “pattern”,
      patternType: “solid”,
      fgColor: “ffffff”,
      },
      };

    const protectedSheet = {
    sheetProtection: {
    sheet: true,
    formatCells: false,
    formatColumns: false,
    formatRows: false,
    password: “Wiscentd2019!”,
    },
    };

    @ifoche I also get the following when I tried to upload data using the template downloaded. please support.

    1. {httpStatus: “Internal Server Error”, httpStatusCode: 500, status: “ERROR”,…}

    2. httpStatus: “Internal Server Error”

    3. httpStatusCode: 500

    4. message: “query did not return a unique result: 7”

    5. status: “ERROR”

    Will this work with dhis version 2.34 or 2.35? Has anyone tested this?

    @Junaid_Mufti Yes, the application officially supports version 2.34. And while version 2.35 is not officially supported yet, everything should work as expected. If you face any issue with newer versions please report them with the builtin feedback tool. Thanks!

    1 Like

    Hi @ifoche does this version complete the datasets that are imported?

    Hi @adam. Unfortunately this version still doesn’t implement that functionality. But we have received some requests to implement it, so we are pretty sure it will come in one of the next ones.

    1 Like

    Pivot Tables/graphs that were formed using 3.2 are still not updating in 2.3.1.
    Secondly in Tracker Capture list of patients registered is not showing on data entry page.
    Data approval is not functioning.
    Please resolve these issues as soon as possible.
    Regards
    Shafaqat

    Dear @Shafaqat,

    If you could please create those issues with as much detail as possible through the feedback tool embedded in the app, we would really appreciate and would give us some help in detecting and replicating those possible failures. However, unfortunately, we cannot guarrantee a delay in solving the issues, unless you have some funding to implement them. We’re a company and we’re working hard to include the solution to as many issues as possible anytime a client requests for a new version. So happy to discuss about any project proposal around Bulk Load that you may have.

    Best regards