Automatic CSV Import

Getting Started With Automatic CSV Import

User Management via automatic CSV import is one of the easiest and most scalable ways to manage your users on the Staffbase platform. The functions and capabilities that the automatic CSV import offer are the same as that of the manual CSV import performed in the Staffbase Studio. The only difference is that the script runs automatically on a schedule eliminating or reducing manual efforts. Similar to manual CSV import, for automatic CSV import, you need to actively push or upload the CSV file from your local environment to the Staffbase platform. You can do this with upload and update requests to the Staffbase endpoints. These calls are always performed in succession for the automatic CSV import.

Staffbase supports both complete and partial CSV automatic imports. The implementation for both processes is similar. If you use the partial CSV import, you need to define the partialImportTag parameter for it in the code.

Contact support@staffbase.com to enable partial CSV import for your organization before implementing it.

Use this guide to get started with the automatic CSV import. The examples provided in the instruction use the cURL command-line.

If you are using PowerShell for automating your CSV import, contact support@staffbase.com to receive template scripts for using PowerShell v5 or higher.

Prerequisites

If you already have existing users in the platform, ensure the identifiers are set and match the identifiers in the CSV file.

  • You have generated an API token with administrative access via the Staffbase Studio.
  • You have a good understanding of the manual CSV import via the Staffbase Studio.

Import Process Overview

The process of performing an automated CSV import in a nutshell.

  1. Upload: HTTP POST request to /users/import/csv/upload with the CSV file specified.
  2. Optional Preview: HTTP POST request to /users/import/csv/update with dry=true.
  3. Update: HTTP POST request to /users/import/csv/update with dry=false.

In this guide, the following CSV file is used:

External ID;First Name;Last Name;Email;Position;Department;Hire Date
A00123;John;Smith;john.smith@company.com;IT Manager;IT;01/05
A00124;Rosie;Jones;rosie.jones@company.com;Sales Representative;Sales;01/05

You can find more CSV file examples on our Support Portal.

1. Step: Upload the CSV File

Make an HTTP POST request to the endpoint: /users/import/csv/upload with the following details:

ParameterDescription
HostThe base URI for the hosting infrastructure on which your app is hosted.
AuthorizationThe API token to authorize the upload and update requests.
CSVThe path of the CSV file in your local environment.
EncodingThe character encoding used in the CSV file.
    Allowed values:
  • windows-1252
  • utf-8
Field separatorThe field separator used in the file to separate the values.
    Allowed values:
  • comma (,)
  • semicolon (;)
Partial import tagThe parameter for the partial CSV import. This is required only if you want to implement a partial CSV import.

Example Request

The following cURL-command uploads a new CSV file named users_example.csv from the current directory, prettyprints the response (using json_pp) and stores it in upload.json.

1curl 'https://exampleapp.staffbase.com/api/users/import/csv/upload' \
2 -X POST \
3 -H 'Authorization: Basic TOKEN' \
4 -H 'Content-Type: multipart/form-data' \
5 -F 'csv=@./users_example.csv' \
6 -F 'encoding=utf-8' \
7 -F 'fieldSeparator=";"' \
8 # optional parameter for partial import:
9 -F 'partialImportTag="csv_import:importTag"' \
10 --compressed | json_pp > upload.json

For debugging purposes, inspect the upload.json.
For example, if you are using Linux environment, inspect the dry_run.json with cat dry_run.json.

Example Response

1{
2 "rowCount" : 2,
3 "total" : 7,
4 "columns" : [
5 "External ID",
6 "First Name",
7 "Last Name",
8 "Email",
9 "Position",
10 "Department",
11 "Hire Date"
12 ],
13 "data" : [
14 {
15 "title" : "External ID",
16 "rows" : [
17 "A00123",
18 "A00124"
19 ]
20 },
21 {
22 "title" : "First Name",
23 "rows" : [
24 "John",
25 "Rosie"
26 ]
27 },
28 {
29 "title" : "Last Name",
30 "rows" : [
31 "Smith",
32 "Jones"
33 ]
34 },
35 {
36 "title" : "Email",
37 "rows" : [
38 "john.smith@company.com",
39 "rosie.jones@company.com"
40 ]
41 },
42 {
43 "title" : "Position",
44 "rows" : [
45 "IT Manager",
46 "Sales Representative"
47 ]
48 },
49 {
50 "title" : "Department",
51 "rows" : [
52 "IT",
53 "Sales"
54 ]
55 },
56 {
57 "title" : "Hire Date",
58 "rows" : [
59 "01/05",
60 "01/05"
61 ]
62 }
63 ]
64}

2. Step: Map the Attributes and Preview the Import

Map the CSV attributes to the Staffbase Studio profile fields and upload the file to Staffbase using the HTTP POST method to the endpoint /users/import/csv/update.

On the CSV Import Mappings page you can find an overview of available attributes in the platform and how to map them with your CSV file.

You can map one column from your CSV file to multiple profile fields. For example [externalID,userName] maps the identifier and username based on the same column.

Use the following parameters to:

  1. Display preview: Set the parameter dry to true in the code to display a preview. It is important to preview and test your mapping before the actual import to avoid incorrect syncs.
  2. Send emails or generate recovery codes: The following parameters are optional. By default, all three parameters are set to false.
  • sendMailsNew: Sends invitation emails to new users
  • sendMailsPending: Resends invitation emails to existing pending users
  • generateRecoveryCodes: Generates recovery codes for all users in the CSV file

Example Request

1curl 'https://exampleapp.staffbase.com/api/users/import/csv/update' \
2 -X POST \
3 -H 'Authorization: Basic TOKEN' \
4 -H 'Content-Type: application/x-www-form-urlencoded' \
5 -d 'mappings=externalID,profile-field:firstName,profile-field:lastName,eMail,profile-field:position,profile-field:department,profile-field:hiredate' \
6 -d 'sendMailsNew=false' \
7 -d 'sendMailsPending=false' \
8 -d 'generateRecoveryCodes=false' \
9 -d 'dry=true' \
10 --compressed | json_pp > dry_run.json

Preview the CSV to check all the changes you're about to make are correct. The preview contains all the changes, such as created, updated, or deactivated users. In addition it may contain, warnings or errors, if any. For example, if you are using Linux environment, inspect the dry_run.json with cat dry_run.json.

Example Response

1{
2 "allChanges" : {
3 "A00123" : {
4 "eMail" : {
5 "new" : "john.smith@company.com",
6 "old" : null
7 },
8 "importTags" : {
9 "new" : [
10 "csv_import:importTag"
11 ],
12 "old" : []
13 },
14 "locale" : {
15 "new" : "en_US",
16 "old" : null
17 },
18 "profile-department" : {
19 "new" : "IT",
20 "old" : null
21 },
22 "profile-firstName" : {
23 "new" : "John",
24 "old" : null
25 },
26 "profile-hiredate" : {
27 "new" : "01/05",
28 "old" : null
29 },
30 "profile-lastName" : {
31 "new" : "Smith",
32 "old" : null
33 },
34 "profile-position" : {
35 "new" : "IT Manager",
36 "old" : null
37 }
38 },
39 "A00124" : {
40 "eMail" : {
41 "new" : "rosie.jones@company.com",
42 "old" : null
43 },
44 "importTags" : {
45 "new" : [
46 "csv_import:importTag"
47 ],
48 "old" : []
49 },
50 "locale" : {
51 "new" : "en_US",
52 "old" : null
53 },
54 "profile-department" : {
55 "new" : "Sales",
56 "old" : null
57 },
58 "profile-firstName" : {
59 "new" : "Rosie",
60 "old" : null
61 },
62 "profile-hiredate" : {
63 "new" : "01/05",
64 "old" : null
65 },
66 "profile-lastName" : {
67 "new" : "Jones",
68 "old" : null
69 },
70 "profile-position" : {
71 "new" : "Sales Representative",
72 "old" : null
73 }
74 }
75 },
76 "created" : {
77 "data" : [
78 {
79 "config" : {
80 "locale" : "en_US"
81 },
82 "created" : "2021-06-21T10:00:00.000Z",
83 "emails" : [
84 {
85 "value" : "john.smith@company.com"
86 }
87 ],
88 "externalID" : "A00123",
89 "profile" : {
90 "avatar" : null,
91 "department" : "IT",
92 "firstName" : "John",
93 "hiredate" : "01/05",
94 "lastName" : "Smith",
95 "location" : null,
96 "phoneNumber" : null,
97 "position" : "IT Manager",
98 "publicEmailAddress" : null
99 },
100 "role" : {
101 "type" : "reader"
102 },
103 "status" : "pending",
104 "tags" : [
105 "csv_import:importTag"
106 ],
107 "updated" : "2021-06-21T10:00:00.000Z"
108 },
109 {
110 "config" : {
111 "locale" : "en_US"
112 },
113 "created" : "2021-06-21T10:00:00.000Z",
114 "emails" : [
115 {
116 "value" : "rosie.jones@company.com"
117 }
118 ],
119 "externalID" : "A00124",
120 "profile" : {
121 "avatar" : null,
122 "department" : "Sales",
123 "firstName" : "Rosie",
124 "hiredate" : "01/05",
125 "lastName" : "Jones",
126 "location" : null,
127 "phoneNumber" : null,
128 "position" : "Sales Representative",
129 "publicEmailAddress" : null
130 },
131 "role" : {
132 "type" : "reader"
133 },
134 "status" : "pending",
135 "tags" : [
136 "csv_import:importTag"
137 ],
138 "updated" : "2021-06-21T10:00:00.000Z"
139 }
140 ],
141 "total" : 2
142 },
143 "deactivated" : {
144 "total" : 0
145 },
146 "deleted" : {
147 "total" : 0
148 },
149 "updated" : {
150 "total" : 0
151 },
152 "userExternalIDToEmail" : {
153 "A00123" : "john.smith@company.com",
154 "A00124" : "rosie.jones@company.com"
155 },
156 "userExternalIDToNotificationEmailAddress" : {
157 "A00123" : "john.smith@company.com",
158 "A00124" : "rosie.jones@company.com"
159 },
160 "errors" : [],
161 "infos" : [],
162 "warnings" : [],
163 "newEmailInvitesCount" : 2,
164 "pendingEmailInvitesCount" : 0
165}

See also the CSV Import References page to help you analyze your CSV import and get more information on potential warning and error codes.

3. Step: Import the File

After testing the mapping, you can set the parameter dry to false and import the file.

Use the example request code snippet from step 2 and modify the dry parameter.

Example Request

1curl 'https://exampleapp.staffbase.com/api/users/import/csv/update' \
2 -X POST \
3 -H 'Authorization: Basic TOKEN' \
4 -H 'Content-Type: application/x-www-form-urlencoded' \
5 -d 'mappings=externalID,profile-field:firstName,profile-field:lastName,eMail,profile-field:position,profile-field:department,profile-field:hiredate' \
6 -d 'sendMailsNew=false' \
7 -d 'sendMailsPending=false' \
8 -d 'generateRecoveryCodes=false' \
9 -d 'dry=false' \
10 --compressed | json_pp > dry_run.json

Schedule the Imports

Schedule the imports and updates based on your business requirements. You can use any scheduling tool of your choice. For example, if you're using the cURL command-line for Unix or Linux, you can use the cron job command for scheduling.

Check the Status of the Latest Import

It is recommended to check the state of your CSV import periodically to ensure everything is working as expected. This can also help if you want to run multiple partial imports in succession.

A HTTP GET request to the /users/import/csv/latest endpoint will return information on the latest import. The response will contain the completed and running parameters that will describe the state of the import.

The combination of these parameter's values will provide you with the status of the import.

Parameter ValuesImport State
completed: true
running: false
Status: Completed
Import is complete and the system is ready for another import.
completed: false
running: true
Status: In Progress
The import is currently running on the system.
completed: false
running: false
Staus: In Preparation
The first step (/users/import/csv/upload) of the import was completed and the second step (/users/import/csv/update) has not yet been started. The current import must be either completed by running the second step of the process or removed.

The system will be ready for a new import when the status is Completed. If the parameters describe an In Progress or In Preparation status, a new import cannot be started.