Exporting and Importing Users From One Database to Another

In some instances, data from one Workplace database may need to be merged into another new or existing Workplace database. This only transfers contact information to the new database. This also might be necessary if an organization is moving from on-premise to a cloud account. Using SQL Server Management Studio and Excel you can extract user profile data and save it to a spreadsheet. Then, import this data into a new database or cloud account.

Exporting Users from an Workplace Reporting (Recommended for Cloud Migrations)

1. Open Reports
2. Create New Report with the following parameters
   
3. Using this report, you can add and remove columns you wish to import into the your new cloud account
   1. Click Column Chooser to add/remove columns
   2. Drag Column names from the Column Chooser onto the report. To remove columns, do the opposite and drag column names from the existing report to the Column Chooser
      
4. Click ‘Export To’ then Excel once you’ve got the data you wish to import
5. Proceed to the end of this guide for importing the resulting exported file into your new cloud account

Please note this method will get disabled users as well as active users

Exporting Users from an Workplace Database (v9 and under)

Our basic process will be:

• Find the Users
• Pick out the transferable information
• Copy the results table displaying data
• Paste it into an Excel sheet
• Import Users with the Workplace Import Wizard:

1. Open Management Studio and connect to the SQL instance that hosts Workplace. Log in with Windows Authentication or SQL Authentication. The default database name is SolutionSavant. The database may be named something else i.e. Workplace.

2. Right-click on the database named ‘SolutionSavant.’ (Please note your dB might have a custom name if the default name was changed)

3a. Click on New Query to open a new query window. Copy and Paste the following red text below into the Query window:

SELECT Contacts.FirstName, Contacts.LastName, Contacts.EmailAddress, Contacts.Phone, Contacts.Ext, Contacts.Cell, Users.UserName, Users.Password, Users.UserPIN, Contacts.Position AS Title, Contacts.GroupList, Contacts.StaticComment, Contacts.Fax, Contacts.Pager, Contacts.HomePhone, Contacts.HomeAddress, Contacts.HomeCity, Contacts.HomeState, Contacts.HomeZip, Contacts.HomeFax, Contacts.HomeEmail FROM Contacts INNER JOIN Users ON Contacts.ContactSys = Users.ContactSys

3b. If your server hosts multiple organizations it will need to be:

SELECT Contacts.FirstName, Contacts.LastName, Contacts.EmailAddress, Contacts.Phone, Contacts.Ext, Contacts.Cell, Users.UserName, Users.Password, Users.UserPIN, Contacts.Position AS Title, Contacts.GroupList, OrganizationLocations.LocationName, Contacts.StaticComment, Contacts.Fax, Contacts.Pager, Contacts.HomePhone, Contacts.HomeAddress, Contacts.HomeCity, Contacts.HomeState, Contacts.HomeZip, Contacts.HomeFax, Contacts.HomeEmail FROM Contacts INNER JOIN Users ON Contacts.ContactSys = Users.ContactSys INNER JOIN OrganizationLocations ON Contacts.OrganizationLocationSys = OrganizationLocations.OrganizationLocationSys

This query result is the list of Users we will extract.

4. Click Execute or F5. The Results will populate the bottom of the Query window.

5. Left-click the top-left corner of the Results panel, where the columns and rows overlap. This highlights all the data in the results window.

6. Right-click and select: “Copy with Headers.”

7. Open a new Excel Spreadsheet.

8. Right-click in the empty Excel sheet and choose Paste.

Not all fields need to be populated. The import fields required to add new users are First Name, Last Name, Email Address, User Name, and Password.

The full list of available Excel import fields is: First Name, Last Name, Email Address, Phone, Ext, Cell, User Name, Password, User ID, User PIN, Title, Group List, Location Name, Static Comment, Fax, Pager, Home Phone, Home Address, Home City, Home State, Home Zip, Home Fax, and Home Email.

9. Save this Spreadsheet (updating any desired fields) and close the sheet.

10. Now you are ready to import your users!

Helpful Links:

Sample Import Sheet (Excel File)

Importing Users into Workplace

1. Now that you have your exported list, you can modify the export file to add any additional columns within the spreadsheet to include optional fields.  To see a sample import template to see all the columns that we import to, download the Sample Import Sheet.
2. The Import Users feature is found in the Web Portal and the Workplace Application Interface or Workplace Outlook Interface.
   1. Using the Web Portal, change the Sample Import Sheet from the older XLS format to the XLSX format. Then, follow the https://www.savanceworkplace.com/resources/pdf/savance-workplace-web-based-user-import-guide/
   2. Using the other interfaces continue to the next step with either the XLS or XLSX format generated by Excel.
3. With your import list perfected (Left aligned data in all columns, no completely blank or null columns, no weird data type formatting), login to either the Workplace Application Interface or Workplace Outlook Interface as an EB Admin.
4. Open the Administrator, which by default will load the “Users” tab.
5. Click the “Import” button to open the Workplace User Import wizard.
6. Go through the steps of the import wizard to select your import file, match up the columns from the source columns on the template to the destination columns in Workplace (as seen in the screenshot below), preview your import, and finish the import to add the users.