Import/Upload Tickets into Ticketing As A Service from Excel with Power Automate

Consolidating support data or importing it into a new system is a frequent necessity. If your ticket information resides in Excel spreadsheets, getting it into your Ticketing As A Service instance in Microsoft Teams is likely on your mind.

Importing historical or external data is a practical need. Microsoft Power Automate is the key to simplifying this, effectively streamlining the transfer of your Excel ticket data into our platform.

This guide details how to prepare your Excel file and use our pre-configured Power Automate solution for an efficient ticket import into Ticketing As A Service.

We've prepared a solution package for this:

Migrate Ticket Solutions.zip

ZIP herunterladen • 32KB

Importing the Power Automate Solution

The first step is to get the Power Automate solution into your environment. Think of this solution as a ready-made toolkit designed specifically for this migration task.

1. Grab the Solution Package: You'll have a .zip file from us. This package contains the Power Automate flows needed for the migration.

2. Head to Power Automate: Open your web browser and go to https://make.powerautomate.com.

3. Navigate to Solutions: On the left-hand menu, you'll find 'Solutions'. If you have multiple environments, make sure you've selected the correct one.

   

4. Import the Solution file: Click 'Import solution' at the top. A dialog will pop up. Click 'Browse', find that .zip file you saved, and select it. Click 'Next', then 'Import'. It might take a few moments, but once done, you'll see the new solution in your list.

Once imported, you can click on the solution to see the flows inside, including one named something like "Main - API Ticket Migration" and "Main - Excel Ticket Import" along with some utility flows that help things run smoothly. Read our blog how to Migrate Tickets between 2 Instance using Power Automate.

Preparing Your Excel File for Import

The success of your import heavily relies on how well your Excel file is structured. Power Automate needs the data in a specific format to read it correctly.

A. Standard Fields: Ensure your Excel sheet has columns for essential ticket information.

• Requestor ID: The unique identifier for the person who submitted the ticket.

• Assignee ID: The unique identifier for the agent assigned to the ticket. Fill these columns with the appropriate ID for each person associated with a ticket.

  
  

B. Custom Fields: If your tickets include custom fields, you'll need to add specific columns to your Excel sheet to handle them:

• People Picker Fields: For custom fields that select users (e.g., "Followers," "Approvers"):

  • Create two additional columns for each People Picker field:

    • [YourFieldName] ID: This column will contain the user ID(s).

    • [YourFieldName] Email: This column will contain the user's email address(es).

  • Example: If your custom field is named "Followers," you'll add columns "Followers ID" and "Followers Email."

  • Important for Multiple Values: If a People Picker field can hold multiple users, separate each ID and each email with a comma (,). Crucially, the order of IDs in the [YourFieldName] ID column must exactly match the order of emails in the [YourFieldName] Email column for each ticket.

    

  
  

• Email Picker Fields: For custom fields designed to capture email addresses (e.g., "Reviewer Email"):

  • Create one additional column:

    • [YourFieldName] Address: This column will contain the actual email address(es).

  • Example: If your field is "Reviewer Email," add a column named "Reviewer Email Address."

    

    
    

C. Convert to Table Format: Power Automate needs your Excel data to be formatted as an official Excel Table to process it.

1. Select all the cells containing your ticket data (including headers). An easy way is to click any cell within your data and press Ctrl + A.

2. Go to the 'Insert' tab on the Excel ribbon.

3. Click 'Table'.

4. In the "Create Table" dialog box, ensure the option "My table has headers" is checked.

5. Click 'OK'. Your data range is now an official Excel Table, ready for Power Automate.

   

Configure the Power Automate Flow

With your Excel file prepped, you need to tell the Power Automate flow where to find it.

1. Navigate back to https://make.powerautomate.com and open the solution you imported earlier.

2. Find and open the "Main - Excel Ticket Import" flow for editing.

3. Look for a scope (a container for actions) typically named something like "Scope - Excel File and Parameters."

4. Inside this scope, locate the action called "List rows present in a table." This is the action that reads your Excel data.

5. Configure this action:

   • Select the Excel file you prepared as the data source.

   • Select the specific Table within that file (Excel usually names it "Table1" by default if you haven't renamed it).

6. Once configured, Save and then Publish the flow.

   

Important Considerations when Importing:

Keep these points in mind to avoid issues:

• Table Format is Essential: Power Automate only reads Excel data formatted as a Table. If you skip this, the flow won't find any rows to import.

• Required Supporting Columns: If your custom fields (like People Pickers) need extra ID or Email columns, ensure they are present and correctly filled in your Excel file.

• Field Compatibility: The custom fields, tag categories, and tags in your Excel data should match the setup in your destination Ticketing As A Service instance.

• No Commas in Values: Avoid using commas (,) within individual custom field values or tag names. Commas are used as separators for multi-value fields, so an internal comma will break the parsing.

• Comment Migration: This Excel-based import flow typically does not support migrating ticket comments. If comments are crucial, consider using the API-based migration flow ("Main - API Ticket Migration").

• Unique Custom Field Names: Ensure all your custom field names are unique and avoid special characters like [ ] or . in the names.

• Unique Tag Names: Tags must have unique names across all your tag categories. Reusing a tag name in different categories can cause incorrect assignments during import.

Run the Import Flow

Now you're ready to import! Run the "Main - Excel Ticket Import" flow. It will prompt you for a few parameters:

• baseURL: The base API endpoint URL for your Ticketing As A Service instance. See Ticketing API Reference.

• secretKey_destination: The API key for the destination Ticketing As A Service instance where tickets will be created.

  

• timezone: Specify the timezone (e.g., 7 for GMT+7) to ensure date/time fields are interpreted correctly.

• limit: The maximum number of tickets (rows from Excel) to process in a single run of the flow.

• offset: The number of tickets (rows) to skip before starting the import. Useful for batching or resuming.

Take Control of Your Ticketing Data

Importing tickets from Excel into Ticketing As A Service doesn't have to be a complex ordeal. By carefully preparing your Excel file and leveraging the provided Power Automate solution, you can efficiently bring your existing data into our powerful Microsoft Teams integrated ticketing system.

Need support or have questions? Feel free to contact us!

Contact us

---

TeamsWork is a Microsoft Partner Network member, and their expertise lies in developing Productivity Apps that harness the power of the Microsoft Teams platform and its dynamic ecosystem. Their SaaS products, including CRM As A Service, Ticketing As A Service and Checklist As A Service, are highly acclaimed by users. Users love the user-friendly interface, seamless integration with Microsoft Teams, and affordable pricing plans. They take pride in developing innovative software solutions that enhance company productivity while being affordable for any budget.