Importing data in Excel files

Submissions that were not created directly to CONNECT can be imported via an Excel file.

There are a few things that needs to be considered before importing data:

• The import feature of the system only considers the first worksheet of an Excel, so if you have more than one worksheet you will need to seperate them into different files for the upload.

• For the import to be successful it is necessary that the colums in the Excel sheet match the question of the data entry form regarding its content. For example, if you want to import a selection question, the values of rows in the Excel sheet need to be indentical to the answers to the selection question.

• As the columns will be mapped to the questions in the data entry form in a proceeding step, they do not need to hold the same title.

• Coordinates need to be in comma seperated decimal degree format with longitude and latitude (9.436841, -5.657279).

• Always double check if you have mapped your fields to columns

Excel lists vs. Upload Template

You have the option of either using your own Excel list or use an Upload Template.

To get to the import feature open the relevant data collection and open the import tab.

The image below shows you the import tab. If you click on the green icon in the “Download Upload Template” window.

You can then copy and paste your data into their respective columns for an easy upload.

Open

Import via Excel Lists

When importing lists to the system there four different types of import you will come across:

1. Importing a lists where everything is valid

2. Importing a list with a small number of errors (e.g. spelling mistakes)

3. Importing a list with a larger number of erros

4. Updating an existing list

1. Importing a list where everything is valid and 2. Importing a list with a small number of errors

Follow these steps to import data to your data collection:

1. Fill in your data to the excel template and upload it by dragging and dropping the file to the designated box on the left side of the same screen, or simply click on the box and select a file from your File explorer.

2. In a next step you need to map the data collection fields (questions in the data entry form) to the columns of the Excel sheet. If you are using the downloaded upload template, the data mapping is done automatically.

3. If all of your entries are new the “Code” field does not to be mapped as none of your submissions have a code yet. The code serves as a unique identifier for the submission that becomes useful when updating data (see “4. Updating an existing list” for an explanation).

   Open

   

4. Initially, the status of your uploaded data will be Unchecked. Click on Validate Data to verify the matching of the Excel sheet and the data entry form.

5. The validation will result in either a Valid or Invalid status for each of your data entiries. By default, the table shows you the invalid entries (you can use the toggle to display also the valid entries). To fix errors in an invalid entry, click on the Edit Button in the Actions column. The edit button is the little icon with the tool on the left below the “Action” column marked with the little red square. This is useful if the number of erros is rather small and quickly be fixed with in-line editing of the entry.

6. Please note that after editing an entry, its status will change back to Unchecked and you need to revalidate the data in order to proceed with the import. Click on the “Revalidate Data” button and the system will check the entries again for errors.

    

7. Click on “Import Valid Data” to import your data.

8. You should now be able to see your imported data in the data table.

Note:

• If the number of invalid entries is high, you also have the option to download them. This way you can work on them outside of the tool and import them at a later time.

• Whatever you choose to do with your invalid entries, you can in any case continue and as the last step, to complete importing all your valid entries, click Import Valid Data.

• More on invalid entries will be explained in the next section “3. Importing a list with a larger number of erros”

3. Importing a list with a larger number of errors”

1. Sometimes you will see a large number of errors in an import list. This may be due to a spelling mistake or other data entry errors during the creation of your list. Below is an example of a list with a larger number of errors. In this case 11 entries are invalid and 30 are valid. Depending on the size of your list there may be 100 or more invalid entries next to another 100 valid entries.

2. In this situation you a multiple options

3. For option a) simply fix the mistakes in your excel and start a new import, now every entry should show up as valid.

4. For option b) upload the valid entries via the button, the system will ask you to confirm that you wish to only import the valid entries, once confirmed the system will import all the valid entries and leave you with the invalid entries. Simply download the invalid entries via the button and fix them in your excel before starting a new import with only the fixed entries. The valid ones from before are already in the system due to you partial import (e.g. your previous step of importing only the valid entries). In the excel file with all the invalid entries you can bulk edit them. This is especially useful if it is a larger number of errors and using the inline editing function of the system such as in the example from “2. Importing a list with a small number of errors (e.g. spelling mistakes)” would take to long.

5. Once you have fixed the list of invalid entries save the file and start a new import of the now valid entries, all entries should now be valid and the import should function as planned. Should there still be errors in the entries you repeat the previous steps or edit them with the in-line editing function if it’s only a smaller number of errors.

4. Updating an existing list

Sometimes you may want to update an existing list to change a few attributes. Consider for example the list below. Some people may have, changed governorates, contract type, or their salary as an example.

Here you have 2 options:

Option a) makes more sense, if there are changes in a small number of entries. Here using the editing function makes more sense as it is faster.

Option b) makes more sense if there is a larger number of edits to make since you can bulk edit in excel and do not have to open each entry by itself like you would have to in the system.

For the case above consider for example that all salaries are increased by 10% and all contract types become permanent due to changes in the company.

Going through them 1 by 1 would take a lot more time than simply using excel to increase the salary values by 10% and replacing every temporary contract with a permanent one and then re-uploading the list while overwriting the exisiting one.

Follow these steps for Option b):

1. To download the list go to the data table, click on the three dots on the top right and select “Download Excel”. The image below shows you where to find the options.
   
   

2. Open the downloaded Excel file and bulk edit the relevant entries.

3. Behind the last column you will notice there are two hidden columns in the file. One is called “Code” the other one is called “Version”.

   1. Code: this is the unique identifier of the entry. The system automatically asigns each new entry that is generated either through manual entry or through an import a code that is used to identify the entry in the database

   2. Version: This shows you how often an entry has been edited. Version 1 refers to a new entry that has never been edited, Version 2 would an entry that has been edited once and so on.

4. Once you have completed your edits, save the file, close it and open the import tab in the relevant data collection and proceed with the import as you would usually, with one extra column mapping. The mapping for the “Code” field. You will notice this time the columns were mapped automatically for you by the system because the column names match the ones in the system exactly. We still suggest to double check the mapping as always before proceeding with the import.

5. In the previous three types of import the code field did not have to be mapped because the entries were all new and did not yet have a code asigned to them by the system. Since the entries that we downloaded were already in the system they have received a code and already exist in the database. Mapping the code now lets the system know to check for existing codes in the data collection and now there are two things that can happen:

This means that in this way you can update existing entries and also add new entries to a list at the same time without having to do two seperate uploads i.e. updating existing entries and then uploading a list of new entries.

Note: Keep in mind that you always have to map the codes if you wish to update entries. If you forget to map the codes of existing entries. If the system does not check for or find a code it will treat the entry as a new entry which could lead to a lot of duplicates if you forget to map them.