Improved Activity CSV import.

[RajbanshiNeha]

The current Activity CSV import will be improved to mimic the current CSV converter (maintained by IATI secretariat), which will include number of major IATI elements. See the Description below.

Tasks

• [x] 1. Design Templates and download the templates.
• [x] 2. Validate the uploaded CSV file against their types.
• [x] 3. Validate each activity.
• [x] 4. Validate other elements.
• [x] 5. Map CSV data into a database compatible format and write into a temporary json file.
• [x] 6. Validate duplications.
• [x] 7. Importing CSV through a queue as a background process.
• [x] 8. Show lists of valid/invalid Results with options to select specific of them to import.
• [x] 9. Import chosen valid activities into the database.
• [ ] 10. Option to choose invalid rows, after which another CSV is generated through AidStream, with all chosen invalid rows.
• [x] 11. Add Default Field Values.

  Description

Current Activity CSV import feature is limited with limited number of fields, it doesn't include budget, transactions information. With the improved feature, we would like to add the provision for the importing of budget, transactions, and other fields depending upon the need of the users.

Template Structure

Template will be custom based on the user's need. There will be 4 blocks that the users will be able to select.

1. Basic fields (all mandatory fields like title, description, status, dates, sector, recipient country, recipient region, participating organisations)
2. Other fields (which includes other fields than basic info - e.g. policy marker)
3. Budget
4. Transactions

Template will also support multiple rows for the same project/activity.

Template supports only one language for all the fields for the given activity.

Basic Fields

• Title
• General Description
• Objectives
• Target Groups
• Participating Organisation Type - Multiple
• Participating Organisation Name - Multiple
• Participating Organisation Identifier - Multiple
• Participating Organisation Role- Multiple
• Activity Status
• Planned Start Date
• Planned End Date
• Actual Start Date
• Actual End Date
• Recipient Country Code - Multiple
• Recipient Country Percentage - Multiple
• Recipient Region Code - Multiple
• Recipient Region Percentage - Multiple
• Sector Vocabulary - Multiple
• Sector Code - Multiple
• Sector Percentage - Multiple

  Other fields

• Policy Marker Vocabulary - Multiple
• Policy Marker Code - Multiple
• Policy Marker Significance - Multiple
• Activity Scope

  Budget

• Budget Type
• Budget Status
• Budget Period Start
• Budget Period End
• Budget Value
• Budget Value Date

  Transaction

• Transaction Internal Reference
• Transaction Type
• Transaction Date
• Transaction Value
• Transaction Value Date
• Transaction Description
• Transaction Provider Organisation Identifier
• Transaction Provider Organisation Type
• Transaction Provider Organisation Activity Identifier
• Transaction Provider Organisation Description
• Transaction Receiver Organisation Identifier
• Transaction Receiver Organisation Type
• Transaction Receiver Organisation Activity Identifier
• Transaction Receiver Organisation Description
• Transaction Sector Vocabulary
• Transaction Sector Code
• Transaction Recipient Country Code
• Transaction Recipient Region Code

  Activity Defaults

• Default Currency
• Default Language
• Humanitarian

  Process

  1. Download template
  2. Fill the data as per template
  3. Upload excel file
  4. "Processing Interface" before importing into database - shows all the activities rows, show validation error for each activity row (e.g. if the used codes are not valid for the given element say country, wrong data type - using string instead of numbers, handling duplicated activities) and will have options to select the valid activity for importing.
  5. Select the rows to be imported
  6. Import
  7. Data imported into the system and redirect to activities page

     Excel Data Validation

Validation will check the followings

• at least minimum fields are filled (title, general description, activity-status, recipient-country or recipient-region)
• whether the valid codelist is used or not for the fields which takes codes

Validation will not check for other rules like if the % for recipient-country is used or not, whether all the % sum to 100 or not and other element level validation. These will be handled in the activity publishing workflow.

Handling duplicated activities

There might be activities in the excel which is already entered in the database (by comparing the iati-activity-identifier). In step 4, the uploader will see that the activity is already entered. There are 3 actions that could be taken by the uploader.

1. Ignore the duplicate activity (doesn't take action)
2. Delete the activity in the database and insert the excel activity row as a new activity
3. Update the existing activity in the database with the updated fields values

   Handling huge number of activities and huge sized files

Huge size files will also have huge number of activities. The importing task will be asynchronous and once the file is uploaded, it will be sent to the csv-processing-queue. The queue will process each row and shows the results in the step 4 (Processing Interface) one at a time. Once the processing is complete, the users will be able to select the valid rows to import and submit. There will be number of workers which will be able to handle multiple files at the same time.

Also the processing time might be long. The user can close the window and when the user goes to the file upload interface, s/he can see the results of last file processed. In case the user uploads new file, then the last uploaded file processed rows will be lost without getting imported into the database. Proper instructions will be shown accordingly.

[wendyrogers]

Where on the template will the new humanitarian elements/attributes be available for import? I think these are likely to become more important over time so would be good to consider them now? Possibly humanitarian-scope on 'Other Fields' although the activity @humanitarian attribute might need to be added to 'Basic Fields' so maybe humanitarian-scope should be added there too?

[SJ-bond]

Organisations that we work with have also requested that the activity template include elements such as document link, website and contact information, as these are required by their donors. Are these elements included in the update? I can't see it on the list.

[jjbuckle]

Our flow type, finance type etc are not always consistent and so the "Default" functionality means that when we import activities with transactions we will need to manually override the values after import. Is there any way to extend the CSV import to allow additional IATI elements to be imported that aren't in the template?