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.
The most important thing while doing this, is checking, checking and double checking. Is the information complete? Is the information in the right format?
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
Note; For smaller lists, it is not neccessary to break them into these 3 parts. The seperation was made by AlumnForce to prevent big incidents from happening. After you have imported annd something went wrong, you cannot fix it yourself. AlumnForce needs to help you as we cannot delete large entries from the database.
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 randomly 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. When you start your import in the database it will show you what the right formats are for all entries.
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.
tip: if you have multiple excel sheets and you want to combine the information, use the "VERT.ZOEKEN" or in English "VLOOKUP" function in excel. This allows you to match information from one excel to another excel. Search on google on how to use this.
- User ID* : this is the same as email address, but all special signs replaced by _ (for example email@example.com 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, and should match "reference'. So do not use this column.
- 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)
Save the list on the restricted documents map on sharepoint.
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 if there are no errors press import. If there errors, and in many cases there are, you need to adjust your excel sheet. Usually this has to with the format of your data and sometimes the format of some data might change when you change the excel file to csv.
If you have imported wrong data, you can run an users update to correct the mistakes. For this you only need the User ID and the fields that you want to correct.
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.