How can I automate the Importing csv to azure board using API in powershell

Question

`I have done automation of backing up azure board work item in csv form to my local using API now I'm trying to restore it back into the azure boards. I'm not getting the proper approach to do that, can any one help me in this

POST https://dev.azure.com/fabrikam/{project}/_apis/wit/workitems/${type}?api-version=7.1-preview.3

[
  {
    "op": "add",
    "path": "/fields/System.Title",
    "from": null,
    "value": "Sample task"
  }
]

Answer 1

You need to get the data from csv file, and update the work item with rest api Work Items - Update.

Here is a doc which mention with c# sample, if you would like to use powershell script, please check below.

Typically the csv file could have below columns(if it's different, please modify the script accordingly).

Please note:

1. Get the data from 2nd row of the csv file as 1st row is the field name.
2. For Area Path, it could contain \ in the path, which need be changed to \\, otherwise udpate will fail.

Powershell script:

# Define parameters
$token = "PAT"
$organization = ""
$project = ""


#get the csv file data and start on 2nd row.
$workItems = Get-Content -Path "C:\Temp\data.csv" | Select-Object -Skip 1   


$token =   [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($token)"))
$header = @{authorization = "Basic $token"}

#fix for area path.
foreach ($row in $workItems) {
    $columns = $row.Split(',')
    $id = $columns[0]
    $type = $columns[1]
    $title = $columns[2]
    $assignedto = $columns[3]
    $state = $columns[4]
    $areapath = $columns[5] -replace '\\', '\\\\'   
    $tags = $columns[6]
    $commentcount = $columns[7]

    # check each value in the csv
    Write-Output "$id"
    Write-Output "$type"
    Write-Output "$title"
    Write-Output "$assignedto"
    Write-Output "$state"
    Write-Output "$areapath"
    Write-Output "Tag is $tags"
    Write-Output "$commentcount"
    Write-Output "================="

 $url = "https://dev.azure.com/$organization/$project/_apis/wit/workitems/$id" + "?bypassRules=true&api-version=7.1-preview.3"
 echo $url


 # Define the JSON body
$json = @"
[
    {"op": "add", "path": "/fields/System.Title","value": "$title"},
    {"op": "add", "path": "/fields/System.AssignedTo","value": "$assignedto"},
    {"op": "add", "path": "/fields/System.State","value": "$state"},
    {"op": "add", "path": "/fields/System.AreaPath","value": "$areapath"},
    {"op": "replace", "path": "/fields/System.Tags","value": "$tag"},
    {"op": "add", "path": "/fields/System.CommentCount","value": "$commentcount"}
]
"@

    # Call the API
    Invoke-RestMethod -Uri $url -Method Patch -Body $json -ContentType "application/json-patch+json" -Headers $header

}

My original work item:

Work item updated.

Edit: to restore the work items to a new project, you need:

1. Change to use rest api Work Items - Create.

2. Remove areapath as it's differnet between different projects. If there's other field conflict, please remove as well.

Code below:

# Define parameters
$token = "PAT"
$organization = ""
$project = ""


#get the csv file data and start on 2nd row.
$workItems = Get-Content -Path "C:\Temp\datanew.csv" | Select-Object -Skip 1   


$token =   [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($token)"))
$header = @{authorization = "Basic $token"}

foreach ($row in $workItems) {
    $columns = $row.Split(',')
    $id = $columns[0]
    $type = $columns[1]
    $title = $columns[2]
    $assignedto = $columns[3]
    $state = $columns[4]
    $areapath = $columns[5] -replace '\\', '\\\\'
    $tags = $columns[6]
    $commentcount = $columns[7]

    # check each value in the csv
    Write-Output "$id"
    Write-Output "$type"
    Write-Output "$title"
    Write-Output "$assignedto"
    Write-Output "$state"
    Write-Output "$areapath"
    Write-Output "Tag is $tags"
    Write-Output "$commentcount"
    Write-Output "================="

 $url = "https://dev.azure.com/$organization/$project/_apis/wit/workitems/$" + "$type" + "?api-version=7.1-preview.3"


 echo $url


 # Define the JSON body
$json = @"
[
    {"op": "add", "path": "/fields/System.Title","value": "$title"},
    {"op": "add", "path": "/fields/System.AssignedTo","value": "$assignedto"},
    {"op": "add", "path": "/fields/System.State","value": "$state"},
    {"op": "add", "path": "/fields/System.Tags","value": "$tag"},
    {"op": "add", "path": "/fields/System.CommentCount","value": "$commentcount"}
]
"@

    # Call the API
    Invoke-RestMethod -Uri $url -Method Post -Body $json -ContentType "application/json-patch+json" -Headers $header

}

Work items created.