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.

Thanks!

ASKER CERTIFIED SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SquigglyMonkey

ASKER

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" ...

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 ";"

SquigglyMonkey

ASKER

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.