PowerCLI Export and Import

[vpv_pji]

Dear Community,

i want to export and import (after editing in excel) a csv file back to vCenter with PowerCLI.

The following information should be exported all in one line so i can easy edit the information in excel:

VM-Name, 2 different tags, 2 different categorys

For example virtualmachine1,

example how the csv should look:

virtualmachine1,"tag1","category1","tag2","category2"

virtualmachine2,"tag1","category1","tag2","category2"

and so on...

Here is what i got already but its not perfekt yet.

If you run this you dont get all the information needed in one line in the csv file. I need "category1" and "category2" not just "category"

I hope you understand what i mean, if not just run the script and check the export.

Get-tag | ForEach-Object {$Tag = $_;get-vm -Tag $_ | ForEach-Object {[pscustomobject]@{name=$_.name;tag=$Tag.name;Category=$tag.category}}} | Export-Csv -NoTypeInformation C:\temp\export.csv

And how can i import the csv file back after editing?

Thanks in advance!

[LucD]

One way of doing this is to make each line the same length, in other words have the same number of Tag/Catagory entries

You could do

$tags = Get-VM | Get-TagAssignment| Group-Object -Property {$_.Entity.ExtensionData.Name}

$maxCat = $tags | %{$_.Group.Count} | Measure-Object -Maximum | select -ExpandProperty Maximum

$tags | %{

    if($_.Group.Count -gt 0){

        $obj = [ordered]@{

            VM = $_.Name

        }

        $i = 1

        $_.Group | %{

            $obj.Add("Tag$($i)",$_.Tag.Name)

            $obj.Add("Category$($i)",$_.Tag.Category)

            $i++

        }

        if($i -le $maxCat){

            $i..$maxCat | %{

                $obj.Add("Tag$($i)",$_.Tag.Name)

                $obj.Add("Category$($i)",$_.Tag.Category)

                $i++

            }

        }

        New-Object PSObject -Property $obj

    }

} | Export-Csv C:\report.csv -NoTypeInformation -UseCulture

To import the file, you could do something like this

$entries = Import-Csv C:\report.csv -UseCulture

$maxCat = (($entries[0] | Get-Member -MemberType NoteProperty).Count - 1)/2

foreach($row in $entries){

    $vm = get-VM -Name $row.VM

    1..$maxCat | %{

        $cat = $row."Category$($_)"

        $tag = $row."Tag$($_)"

        if($cat -ne '' -and $tag -ne ''){

            Try{

                $tCat = Get-TagCategory -Name $cat -ErrorAction Stop

            }

            Catch{

                $tCat = New-TagCategory -Name $cat

            }

            Try{

                $tTag = Get-Tag -Name $tag -Category $tCat

            }

            Catch{

                $tTag = New-Tag -Name $tag -Category $tCat

            }

            New-TagAssignment -Entity $vm -Tag $tTag

        }

    }

}

[vpv_pji]

Hello,

the export works like a charm! Exactly what i wanted.

THANKS!

The import is not complete. I changed at the csv file a value (for example owner from Dave to Steven), saved and started the import.

The value i changed was added additional to the Virtual Machine tag. i expected to overwrite the existing name Dave to Steven.

Got an error promt:

New-TagAssignment : "System.Object[]" kann nicht in den Typ "VMware.VimAutomation.Sdk.Types.V1.VIObjectCore"
konvertiert werden, der für den Parameter "Entity" erforderlich ist. Not supported
In C:\Users\pji\Documents\VMWareScripte\VMCLI\import.ps1:22 Zeichen:39
+             New-TagAssignment -Entity $vm -Tag $tTag
+                                       ~~~
    + CategoryInfo          : InvalidArgument: (:) [New-TagAssignment], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgument,VMware.VimAutomation.ViCore.Cmdlets.Commands.Tagging.NewTagAssignment

Any idea?

[LucD]

Yes, we would need to remove the tag assignment (in case the cardinality of the category is 1).
Do you know what cardinality those tagcategories have, or should have?

Or should the script find that out?

It will make the logic of the import script a bit more complex

[vpv_pji]

Hi,

LucD thanks for Response...:smileygrin:

I have 3 categorys with mixed cardinality and many tags

Two of the categorys are multiple cardinality and one is single.

maybe the best if the script finds out?

[LucD]

Indeed, let me check how best to tackle that

[LucD]

Try like this.
I made a few assumptions:

• if a Category doesn't exist, the script creates it with Cardinality Single
• if a Category has Cardinality Single, any existing tag in that category will be removed

Not sure if this is 100% what your require.
If the Category is for example Multiple, can an entity have multiple Tags assigned in that Category?

This is the current version (only removes Tags in the Categories with Cardinality Single).

$entries = Import-Csv C:\report.csv -UseCulture

$maxCat = (($entries[0] | Get-Member -MemberType NoteProperty).Count - 1)/2

foreach($row in $entries){

    $vm = get-VM -Name $row.VM

    1..$maxCat | %{

        if($row."Category$($_)" -ne '' -and $row."Tag$($_)" -ne ''){

            $catName = $row."Category$($_)"

            $tagName = $row."Tag$($_)"

            Try{

                $cat = Get-TagCategory -Name $catName -ErrorAction Stop

            }

            Catch{

                $cat = New-TagCategory -Name $catName -Cardinality Single

            }

            Try{

                $tag = Get-Tag -Name $tagName -Category $cat -ErrorAction Stop

            }

            Catch{

                $tag = New-Tag -Name $tagName -Category $cat

            }

            if($cat.Cardinality -eq [VMware.VimAutomation.ViCore.Types.V1.Cardinality]::Single){

                Get-TagAssignment -Entity $vm -Category $cat | Remove-TagAssignment -Confirm:$false

            }

            New-TagAssignment -Entity $vm -Tag $tag

        }

    }

}

[vpv_pji]

Great work! We´re getting closer.

Is it possible to export the tags and categorys like this: (preferred alphabetically from left to right) Because editing in excel would be more comfortable

VM	Systemresponsible	Shutdown	Owner
vm01	Dave	step5	PersonA,PersonB
vm02	Steve	step2	PersonC

If this solution not possible:

Category & Tags = alphabetically from left to right

Hint:

Bevore import remove the tag and category, then import from csv

Assuming the Category already exists in vCenter, there is no need to create if not exists.

I modified with a little bit help the script.

Thats how it looks at the moment...

#export
$tags = Get-VM | Get-TagAssignment | Group-Object -Property {$_.Entity.ExtensionData.Name}
$maxCat = $tags | %{$_.Group.Count} | Measure-Object -Maximum | select -ExpandProperty Maximum
$maxCat = $maxCat+6
$tags = $tags | sort name
$tags | %{
    if($_.Group.Count -gt 0){
        $obj = [ordered]@{
            VM = $_.Name
        }
        $i = 1
        $_.Group | %{
            $obj.Add("Tag$($i)",$_.Tag.Name)
            $obj.Add("Category$($i)",$_.Tag.Category)
            $i++
        }
        if($i -le $maxCat){
            $i..$maxCat | %{
                $obj.Add("Tag$($i)",$_.Tag.Name)
                $obj.Add("Category$($i)",$_.Tag.Category)
                $i++
            }
        }
        New-Object PSObject -Property $obj
    }
} | Export-Csv C:\Users\pji\Documents\VMWareScripte\VMCLI\export.csv -NoTypeInformation -UseCulture
#import
$entries = Import-Csv C:\Users\pji\Documents\VMWareScripte\VMCLI\export.csv -UseCulture
$maxCat = (($entries[0] | Get-Member -MemberType NoteProperty).Count - 1)/2
foreach($row in $entries){
    $vm = get-VM -Name $row.VM
  Get-TagAssignment -Entity $vm | Remove-TagAssignment -Confirm:$false
    1..$maxCat | %{
        if($row."Category$($_)" -ne '' -and $row."Tag$($_)" -ne ''){
            $catName = $row."Category$($_)"
            $tagName = $row."Tag$($_)"
            Try{
             $cat = Get-TagCategory -Name $catName -ErrorAction Stop
            }
            Catch{
                $cat = New-TagCategory -Name $catName -Cardinality Single
            }
            Try{
                $tag = Get-Tag -Name $tagName -Category $cat -ErrorAction Stop
            }
            Catch{
                $tag = New-Tag -Name $tagName -Category $cat
            }
#            if($cat.Cardinality -eq [VMware.VimAutomation.ViCore.Types.V1.Cardinality]::Single){
#                Get-TagAssignment -Entity $vm -Category $cat | Remove-TagAssignment -Confirm:$false
#
#            }
# Get-TagAssignment -Entity $vm -Category $cat | Remove-TagAssignment -Confirm:$false
            New-TagAssignment -Entity $vm -Tag $tag
        }
    }
}

[LucD]

Try like this

$tags = Get-VM | Get-TagAssignment

$tagGroups = $tags | Group-Object -Property {$_.Entity.ExtensionData.Name}

$tagCategoryNames = $tags | %{$_.Tag.Category.Name} | Sort-Object -Unique

$tagGroups | %{

    if($_.Group.Count -gt 0){

        $obj = [ordered]@{

            VM = $_.Name

        }

        $tagCategoryNames | %{

            $obj.Add($_,'')

        }

        $i = 0

        $_.Group | Sort-Object -Property {$_.Tag.Category.Name} | %{

            $obj.Item($_.Tag.Category.Name) = ($obj.Item($_.Tag.Category.Name),$_.Tag.Name -join ',').TrimStart(',')

        }

        New-Object PSObject -Property $obj

    }

} | Export-Csv C:\Users\pji\Documents\VMWareScripte\VMCLI\export.csv -NoTypeInformation -UseCulture 

[vpv_pji · Accepted solution]

yeah looks very good. thanks so much.

I had to edit the import script to get the import working again.

at least a question. is it possible to export and import a multiple cardinality category with two or more tags separated with a , for example:

VM	Systemresponsible	Shutdown	Owner
vm01	Dave,Steve	shutdown5	GroupA
vm02	Jack	shutdown1	GroupB

For the community here the complete script:

export.ps1

$tags = Get-VM | Get-TagAssignment
$tagGroups = $tags | Group-Object -Property {$_.Entity.ExtensionData.Name}
$tagCategoryNames = $tags | %{$_.Tag.Category.Name} | Sort-Object -Unique
$tagGroups | %{
    if($_.Group.Count -gt 0){
        $obj = [ordered]@{
            VM = $_.Name
        }
        $tagCategoryNames | %{
            $obj.Add($_,'')
        }
        $i = 0
        $_.Group | Sort-Object -Property {$_.Tag.Category.Name} | %{
            $obj.Item($_.Tag.Category.Name) = ($obj.Item($_.Tag.Category.Name),$_.Tag.Name -join ',').TrimStart(',')
        }
        New-Object PSObject -Property $obj
    }
} | Export-Csv C:\Users\pji\Documents\VMWareScripte\VMCLI\export.csv -NoTypeInformation -UseCulture 

import.ps1

$entries = Import-Csv C:\Users\pji\Documents\VMWareScripte\VMCLI\export2.csv -UseCulture
$maxCat = (($entries[0] | Get-Member -MemberType NoteProperty).Count - 1)-1
$tagcategory = $entries[0] | Get-Member -MemberType Noteproperty | Where-Object {$_.name -ne 'VM'} | select name
foreach($row in $entries){
    $vm = get-VM -Name $row.VM
    Get-TagAssignment -Entity $vm | Remove-TagAssignment -Confirm:$false
    0..$maxCat | %{
    $catName = $tagcategory["$($_)"].name
    $tagName = $row."$catName"
        if($tagName -ne ''){
            Try{
                $cat = Get-TagCategory -Name $catName -ErrorAction Stop
            }
            Catch{
                $cat = New-TagCategory -Name $catName -Cardinality Single
            }
            Try{
                $tag = Get-Tag -Name $tagName -Category $cat -ErrorAction Stop
            }
            Catch{
                $tag = New-Tag -Name $tagName -Category $cat
            }
#            if($cat.Cardinality -eq [VMware.VimAutomation.ViCore.Types.V1.Cardinality]::Single){
#                Get-TagAssignment -Entity $vm -Category $cat | Remove-TagAssignment -Confirm:$false
#
#            }
# Get-TagAssignment -Entity $vm -Category $cat | Remove-TagAssignment -Confirm:$false
            New-TagAssignment -Entity $vm -Tag $tag
        }
    }
}

[LucD]

The latest export code does that.

Check the code, I updated it afterwards.

[vpv_pji]

Perfect! Great job thanks!