Avatar of SquigglyMonkey

asked on 

powershell script that reads from excel and updates annotations

I could not find an existing topic, or maybe I couldn't find the right wording to find it.
I have a spreadsheet with server names in one column and a value in a second column.
This is the one liner that will update the annotation. (It is using powershell with powercli imported, to update vm annotations in VSPHERE.)
Set-annotation -entity vm-name -customattribute "location" -value "data center"

Open in new window

I could just create a script from Excel and export it to text, and run it, which I have done before. (Basically a bunch of one-liners)
Is it fairly simple for a script to pull 2 things from a spreadsheet? In this case it would be the VM name, and the value of the attribute that will change row to row.

Powershell* PowerCLIVMwareScripting Languages

Avatar of undefined
Last Comment
Avatar of oBdA

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of SquigglyMonkey


Well, darn. not working for me as yet. Hopefully something simple.
I have a csv file with 2 columns, the first  cell a1 has "VMName"(no quotes) in the cell below it ,in a2 a3 and a4 are server names, lets say server1 server2, etc.
in b1, the cell has "value"(no quotes)  in it, since this is actually what is getting changed .
It is saved as anno.csv, in the same directory as the script.
When I open it in excel, I see the 2 columns and the data in them.
An administrative ps session is opened in that directory.
When I run this:
Import-Csv -path C:\scripts\anno.csv | ForEach-Object {
                Set-Annotation -Entity $_.VMName -CustomAttribute "location" -Value $_.value

Open in new window

I get this, which looks to me like the command is not getting the server names:

Set-Annotation : Cannot validate argument on parameter 'Entity'. The argument is null. Provide a valid value for the argument, and then try running the command again.
At C:\scripts\update-anno-from-csv.ps1:2 char:25
+     Set-Annotation -Entity $_VMName -CustomAttribute "location" ...
Avatar of oBdA

Open the csv file in Notepad or any other text editor and verify the format, and/or just run
Import-Csv -path C:\scripts\anno.csv
It could be that your standard delimiter is not a comma, and Import-Csv expects a comma by default.
If the delimiter in the csv file is, for example, a ";", just add the parameter
-Delimiter ";"
to the Import-Csv command.
Import-Csv -Path C:\scripts\anno.csv -Delimiter ";"
Avatar of SquigglyMonkey


So the issue is the csv file, which I am also seeing when I just import it, the top "headers" are run together,
I receive  a spreadsheet with two columns, which I save as anno.csv.
I guess that is not sufficient to create it in the correct format, if I open it in notepad there is no commas or any delimiter.

VMware, a software company founded in 1998, was one of the first commercially successful companies to offer x86 virtualization. The storage company EMC purchased VMware in 1994. Dell Technologies acquired EMC in 2016. VMware’s parent company is now Dell Technologies. VMware has many software products that run on desktops, Microsoft Windows, Linux, and macOS, which allows the virtualizing of the x86 architecture. Its enterprise software hypervisor for servers, VMware vSphere Hypervisor (ESXi), is a bare-metal hypervisor that runs directly on the server hardware and does not require an additional underlying operating system.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo