Importing BPS user from an Excel file

Applies to version 2020.1.3; author: Franciszek Sakławski

Introduction

Apart from standard options for adding users to the system, such as synchronization with AD or/and AAD, WEBCON BPS allows you to manually add a user. All users added in this way can log in to the system via BPS Auth., be granted privileges, and can be assigned tasks in the system.

To save time, you can use the ability of adding more users from an Excel file.

Adding users from an Excel file

To add users, prepare an Excel file based on the defined template. This template can be downloaded from WEBCON BPS Designer Studio – go to the System settings tab -> BPS users list and click the Download template button. The system will download the Excel file with the defined columns in user data that should be entered.

Fig. 1. WEBCON BPS Designer Studio

The downloaded Excel file template:

Fig. 2. The Excel file template

1. Email – user’s e-mail address (required)
2. Name – user’s name and surname (required)
3. BpsId – user’s e-mail address (required). In WEBCON BPS, the e-mail address is the unique user ID
4. JobTitle – user’s position (optional)
5. Phone – user’s phone number (optional)
6. Manager – manager’s BPS_ID (optional)
7. Picture – the base64 code of the image that would appear next to the user’s account – for more information about adding pictures see The "Picture" form field (optional)

After filling out data, the file looks like this:

Fig. 3. The template with the filled out data about a user

Save the file and go to Designer Studio. In the place where the file has been downloaded, you can add the filled-out file.

Fig. 4. WEBCON BPS Designer Studio – the template import

Business case

If you already have some way of storing information about employees in the internal database, and you want to export them to an Excel file, see how to easily convert the data to match the template.

The #Users tab has been created that stores user’s data:

Fig. 5. The SQL query

Data returned by the query does not match the formula specified in the Excel file template, so you have to transform this query to get the columns used in the import.

Fig. 6. The SQL query

This means that from the #Users table the system get:

1. Email as email
2. Name and surname as Name
3. Email as BpsId
4. Job Title as JobTitle
5. Phone as Phone
6. Manager’s email as Manager

Then copy data that returned you by the query and paste it to the spreadsheet. To do this, select data that you want to copy, right-click on the table and select the “Copy” option. Next, go to the spreadsheet and paste the copied data in the first column under the title.

Fig. 7. Copying data

Remember to use the “Copy” option instead of “Copy with headers” option - otherwise, the first row in the Excel file will be duplicated. Save the file as a template in Designer Studio.