Every now and then we import large data files from or partners or our scholarships into the network to increase our database. After you have done this, of course an activation campagin is still needed! But first things first, this is how you do it.
step 1: fill in the right excel formats
The information needs to be imported in 3 parts, so also in 3 different excel sheets. These can be found in the team folder on SharePoint.
- Official academic info
- Custom fields
- Professional info
You should also work in this order, only the official academic info is compulsory to import
1a. Make sure your original data is correct and complete
The first step is to make sure your data is correct and complete. Certain fields are obligatory to import, so always check whether these fields are filled in or correct (in the right format). Otherwise your import will definitely go wrong. The obligatory fields you have to check are:
- First name*"
- Last name*"
- Date of birth*
- Dutch education institution*
- Study start date*
- Study end date*
If any of these fields are missing, make sure you fill something in or when fields with*" are missing. delete that person from your database if you do not have that information. For the other fields you can just randomnly fill something in.
important: make sure that the different dates are all in the same format, so either M/D/Y or D/M/Y.
Now your data set is complete and you can start to copy and paste the data into the right excel format.
tip: Control Shift + arrow down to select all information in one column. Also make sure you do not use any filters!
1b. Complete the excel template
Some fields are not in your original database but they are obligatory* to fill this in for the HAn database and some fields need some extra attention. These are:
- User ID* : this is the same as email address, but all special signs replaced by _ (for example firstname.lastname@example.org becomes jlvanveldhuizen_nuffic_nl). Simple change this by using the CTRLF function
- Gender*: Either fill in Ms or Mr. (take notice of the . after Mr)
- Country of residence: these are 2 letters and can be found in the extra tab " country ISO"
- Mobile Phone number: use this format 31627008175
- User type* : fill in a number. 1=alumni, 2=teacher, 3=staff, 4=recruiter, 5=student
- Official Organisation*: this is Always NL
- Official Dutch Education Institution; not compulsory, but make sure the names are the same as the names in the database. See tab "DEI names &reference".
- Reference*: this is the reference code of the Dutch Education Institution. See tab "DEI names&reference".
- User ID*
- Degree level: see tab degree level what kind of degree levels there are
- Field of study: these should be the same as used in HAn
- Scholarship: find the right name of the Scholarship in the tab "Scholarships" or check it first on the website
- Programme type: see tab programme type
- Study Programme: This is the name of the study and is an open field
- Dutch education institution 2
- Study end date 2
- Capacity building programme: see the tab capacity building programme
- Current job title: open field
- Current company: open fields. Caution: If 2 organisations do not have the same spelling, they will be duplicated.
- Current sector-industry: see tab sector-industry
If the field is not compulsory and you do not have the information,then you just leave it empty. The systems will automatically change it into unspecified.
Step 3: save the files in the right format
While saving the file, use the following settings:
- Delete the empty columns
- Save the excel file in .CSV UTF-8 format
- Open Notepad and open the .CSV UTF-8 file (choose all formats)
- Make sure any """signs are deleted
- Save the file as .csv and choose UTF-8 (just enter .csv behind the name)
Go to users>import and choose user import. Upload the file here, choose a description and makse sure that the date format is correct and that the seperator is semicolon.
Step 4: map the fields
The next step is to make sure you map the fields correctly.
After you have mapped the field a screen pops up that tells you whether there are any errors in the file. Select Do not take into consideration empty
cells if this options shows. You can still change the file accordingly. Click on check file and then press import.
If you have imported wrong data, you can run an users update to correct the mistakes.
Step 5: importing in communities
For Scholarship communities the automatic populate function does not work because the scholarships are custom fields. So therefore we need to run another import: import of community members
For this, you need to make a CSV file with the persons ID (map as User School ID) and community ID (map as Internal group ID). The community ID you can find simply by checking the url of the community: In this case the community ID is 71. Afterwards follow the same steps as above.