How to Automate the Process of Importing Data

Modified on Thu, 24 Feb 2022 at 09:19 AM

TABLE OF CONTENTS

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.

 

Graphical user interface, text, application 
Description automatically generated



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.


ColumnsDetails
Script NameA descriptive name 
EnabledTicked
CategoryCategorise it accordingly
DescriptionBrief description of what it does
Grant AccessApi Connectors
Schedule 1Specify when to run this script
TriggerNo Trigger
Sub TriggerNo Trigger
ModuleDevices. Note this needs to be set based on what module you are importing into.
Run AsApi Account


  • 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.