แก้ไข

แชร์ผ่าน

Import, update, and export bulk work items with CSV files in Azure Boards

  • บทความ

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

Import and export work items in bulk using CSV formatted files in Azure Boards. While you can continue to use Excel for bulk import and updates, you can use the native import/export feature that doesn't require Excel. For more information, see Bulk add or modify work items with Excel.

You can export of work items in bulk using a CSV formatted file. While you continue to use Excel for bulk import and updates, you can use the native export feature from Queries that doesn't require Excel. For more information, see Bulk add or modify work items with Excel.

Import new work items

Do the following steps to import new work items.

Note

You can import up to 1,000 work items at a time. If you have more than 1,000 work items to import, break them into multiple files and import them separately.

  1. Create a local import.csv file and open it in Visual Studio Code or Excel.

  2. The file must contain the Work Item Type and the Title fields. You can include other fields as needed. For a list of default fields, see Work item field index.

    The following example includes the Priority field.

    Work Item Type,Title,Priority
Issue,Fix issues with code,1
Issue,Merge testing modules,3
Issue,Open private preview for select customers,2
Issue,Enable feature for customer champs,2
Issue,Remove old test code,2

  3. From the web portal for your project, open Boards > Queries, and then select Import work items.

    Screenshot showing the Boards page with Queries, then Import work items selected.

  4. Select Choose file, choose your file, and then select Import.

    Screenshot showing Import work items dialog with the Import button highlighted.

    The import process loads the imported work items into the queries view in an unsaved state. No IDs get assigned.

  5. Verify the results and then select Save items to save the work items.

    Screenshot showing Save items for imported work items.

    Tip

    Don't assign IDs to new work items that you add. Assigning IDs results in an error message.

  6. The system highlights those work items with data issues. Resolve the data issues before you save the work items. In this example, an invalid value is in the Priority field. Fix the data by opening the work item directly. Instead, use bulk edit to fix several work items with the same issue.

    Screenshot showing work items with data issues to fix.

The following tips can be useful.

  • Parent-child links: You can add parent-child links between work items you import by indenting the title columns, as shown in Can I import a CSV file that have parent-child links?. However, you can't specify any other link types when you import or update work items.

  • Default State field: When you import new work items, the State field is set to New by default. You can't specify a different state during the import process. If you need to change the state of imported work items, use the following workaround:

    1. Import the work items with the default New state.
    2. Export the imported work items to a CSV file.
    3. Update the State field values in the exported CSV file.
    4. Reimport the updated CSV file to set the desired states.

  • Default Area and Iteration fields: The Area and Iteration fields default to the top-level node. This behavior occurs because the import process doesn't have the context for these fields unless explicitly specified in the CSV file. To set specific Area and Iteration paths during import, ensure these fields are included in your CSV file with the correct values. For example:

    Title,Description,Area Path,Iteration Path
"Sample Work Item","This is a sample description.","Project\Team","Project\Sprint 1"

Update existing work items

  1. To update work items, create a query that contains all the columns you want to export and possibly edit. Save your query and select Export to CSV to save the data.csv file to your local machine.

    Screenshot showing work items in a query with the option to Export to CSV selected.

    The exported file should look similar to the following example:

    ID,Work Item Type,Title,Assigned To,State,Tags
"272","Issue","Fix issues with code","Active","",
"273","Issue","Merge testing modules","Active","",
"274","Issue","Open private preview for select customers","Active","",
"275","Issue","Enable feature for customer champs","Active","",
"276","Issue","Remove old test code","Active","",

  2. Make the edits to your work items. Your CSV file must contain the ID, Work Item Type, and Title fields. Any other fields you want to include are optional.

    Note

    When you import identity fields, enter the name and email in the following format "Display Name <email>". For example, to assign work to Jamal Hartnett, specify "Jamal Hartnett <fabrikamfiber4@hotmail.com>". If you specify a value that isn't recognized as a valid user to the system, you may encounter problems with the import.

    In the following example, we change values on existing working items.

    ID,Work Item Type,Title,Assigned To,State,Tags
"272","Issue","Fix issues with code","Jamal Hartnett <fabrikamfiber4@hotmail.com>","Active",
"273","Issue","Merge testing modules","Jamal Hartnett <fabrikamfiber4@hotmail.com>","Active",
"274","Issue","Open private preview for select customers","Raisa Pokrovskaya <fabrikamfiber5@hotmail.com>","Active",
"275","Issue","Enable feature for customer champs","Raisa Pokrovskaya <fabrikamfiber5@hotmail.com>","Active",
"276","Issue","Remove old test code","Christie Church <fabrikamfiber1@hotmail.com>","Active",

  3. Save the file and import, as described in the previous section.

  4. The results list with work items that contain value changes appear highlighted in bold. Select Save Items to apply the changes.

    Screenshot showing Import Work Items with the option to Save items.

  5. Work items with data issues get highlighted in red and must be resolved before you can save them. In this example, an invalid value appears in the Assigned To field. Fix the data by opening the work item directly. You can use bulk edit if you have many work items with the same issue.

    Screenshot showing an invalid value appearing in the Assigned To field.

Export list as a CSV file

From any query, you can export a list of work items as a comma-delimited list. Open the query, select the actions icon, and then select Export to CSV.

Note

Requires Azure DevOps Server 2019 Update 1 or later version.

Screenshot showing Export a query as CSV.

Export and import work items to a different project

You can use this feature to export work items from one project and import them to another project. However, before you import them to another project, you must remove the work item ID. You get an error if you attempt to import new work items to a project with an ID specified.

Import HTML fields

HTML fields, such as descriptions and acceptance criteria, include rich text formatting. To ensure this formatting is preserved, do the following tasks:

  1. Ensure your CSV file includes HTML tags within the relevant fields.
  2. Import the CSV file into Excel.

Excel might handle multi-line text fields differently, so it's important to check the formatting after import. Replace lines ending in CRLF by surrounding sentences with <p>... </p>.

For example, you can import the following work item, which includes three lines of text in the Description field.

Work Item Type,Title,Description
"Product Backlog Item","Hello World Web Site - 8","<p><strong>&nbsp;You can include bold text</strong></p><p><em>&nbsp;And italic text</em></p><p><u>&nbsp;Underline text</u></p>"

Convert multi-line fields to plaintext

To disable HTML in multi-line fields to ensure that they're plaintext, you can use the witadmin changefield command. For more information, see Manage Work Item Fields.

Example command:

witadmin changefield /collection:CollectionURL /n:FieldName /type:PlainText

Handle formatting inconsistencies

When you work with HTML fields across different Microsoft products, you might encounter formatting inconsistencies. Here are some tips to handle these issues:

  • Review the formatting after importing to ensure it meets your requirements.
  • Use appropriate HTML tags and structures to minimize discrepancies. You can add multiple tags separated by a semicolon. For more information, see Tasks you can and can't do with Excel.

For more information on managing HTML content and ensuring compatibility, see Provide help text, hyperlinks, or web content on a work item form.

FAQs

Q: Can I import new items and update existing items in the same CSV file?

A: Absolutely! Leave the ID field empty for any new work items. In the following example, the last entry for an Epic doesn't specify an ID.

ID,Work Item Type,Title,Assigned To,State,Priority,Tags
"16504","Issue","Fix issues with code",,"To Do","1",
"16505","Issue","Merge testing modules",,"To Do","3",
"16506","Issue","Open private preview for select customers",,"To Do","2",
"16507","Issue","Enable feature for customer champs",,"To Do","2",
"16508","Issue","Remove old test code",,"To Do","2",
,"Epic","Track Telemetry for data imports",,"To Do","2",

A: Yes, you can add child work items by indenting title columns. The following example adds three child issues under the already defined Epic.

ID,Work Item Type,Title 1,Title 2,Assigned To,State,Priority,Tags
"165","Epic","Track Telemetry for data imports",,,"To Do","2",
,"Issue",,"Fix issues with code",,"To Do","1",
,"Issue",,"Open private preview for select customers",,"To Do","2",
,"Issue",,"Enable feature for customer champs",,"To Do","2",

Screenshot showing Excel view.

Q: How do I know if my imported file has errors?

A: You can test by adding tags with spaces and hyphens, for example, and include it in the export. The import should match the same format. Any problems with the formatting of your CSV file appear in the Results page of the import view. You can't import the work items until the formatting and syntax is correct.

Screenshot showing a CSV Error in the Import Work Items page.

The work item results always list the data errors found for individual work items. Fix each error either from the web portal, or in the CSV file and import again.

Q: Why am I getting errors for some identity values?

A: When you use the Web UI, the identity picker goes through extra steps to validate the user. First it checks to see if the person is a valid user in the org. If not, it searches on the identity in Microsoft Entra ID. If the user's in Microsoft Entra ID but not in the org, that user gets added to the valid identities.

When you import from CSV, for performance reasons, the identity picker doesn't go through these extra steps. It only checks to see if there's a matching User Principal Name (UPN) already in the org. If it doesn't find a matching UPN, it reports that the identity is unknown.

Q: Does CSV import support all work item types?

A: No, the CSV import doesn't support the following work item types:

  • Code Review Request
  • Code Review Response
  • Feedback Request
  • Feedback Response
  • Test Case
  • Test Plan
  • Test Suite
  • Shared Parameter

For more information, see Bulk import or export test cases.