TABLE OF CONTENTS

• Introduction
  • Prerequisite
  • Establish the Location of the Files
  • Create the PowerShell Script
  • Script syntax
  • Examining the Import Contract text file
• Further Support

Introduction

A Project admin might have extra information about users, workstations, applications, defects, deployment units, tasks, contacts, mailboxes, & bespoke items, that would be useful to the project. The data importer allows a project admin to import data from an excel or csv file into ManagementStudio.  In most cases, these imports could be done on a daily or weekly which could be time consuming. Using the Scheduled Task manager, this process can be automated and set to run at a specific time and frequency provided the source data is available. The example that follows will use the Device module to illustrate the concept although this approach applies to all modules in ManagementStudio.

Prerequisite

• Import data which could be an Excel or CSV file.
• Import Contract created when manually importing data.
• The Service account that ManagementStudio runs under requires access to the folder/location where the import data and contract are saved.

Establish the Location of the Files

• Browse to the location where the import data and import contract are located.
• Make a note of the file path.

Create the PowerShell Script

• Switch to Administration ->[Target Module] -> PowerShell Scripts (1). In this example, Devices is selected.
• Click here to add new item (2).
• Enter a descriptive name for the script. (3).
• Click on the Edit (4) symbol and enter the following script.

##Call the import module 

##specify location of file and import contract

Import-MsModuleData -Module Devices -ImportFilePath 'C:\ManagementStudioFiles\ScheduleReport\Import\Device_data.xlsx' -ContractFile 'C:\ManagementStudioFiles\ScheduleReport\Import\DeviceImportContract.txt'

##Move imported file into completed/ folder

Move-Item "C:\ManagementStudioFiles\ScheduleReport\Import\Device_data.xlsx" ("C:\ManagementStudioFiles\ScheduleReport\Import\CompletedImports\")

##Rename file located in the completed location to include timestamp

Rename-Item -path "C:\ManagementStudioFiles\ScheduleReport\Import\CompletedImports\Device_data.xlsx" "Device_data_$((get-date).ToString('dd-MM-yyyy')).xlsx"

• Click OK (5).

• Enter the following with the specified columns.

• Click Save Changes (6).

• The script has been scheduled to run at 12pm everyday. Please refer to this article for more information on the Scheduled Task Manager.

Script syntax

##Call the import module 

##specify location of file and import contract

Import-MsModuleData -Module TargetModule -ImportFilePath 'C:\Folderpath\filename.xlsx' -ContractFile 'C:\Folderpath\ImportContract.txt'

##Move imported file into completed/destinated folder

Move-Item "C:\Folderpath\filename.xlsx" ("C:\DestinationFolderpath\")

##Rename file located in the completed location to include timestamp

Rename-Item -path "C:\DestinationFolderpath\filename.xlsx" " "filename_$((get-date).ToString('dd-MM-yyyy')).xlsx"

Examining the Import Contract text file

The import contract file needs to be amended accordingly based on the file type. Open the contract file from its location and amend line 3. 

• Excel (.xlsx)  = 1
• CSV = 2

Further Support

If you require further support, please visit ManagementStudio's Service Desk to search the knowledge base or create a new support ticket.