by leitsu at 2013-03-25 16:22:34
Hiby ps_gregg at 2013-03-25 20:25:22
If I have the hostname "clexncihyp001" and Subnet Name "Dedicated ESX vmotion". Is it possible to use powershell to return its "VLAN ID" which is 1782?
Assuming your CSV file is named "C:\vmhosts.csv", the following will provide you the value contained in the "VLAN ID" of the object.by leitsu at 2013-03-25 21:47:08Import-csv C:\vmhosts.csv | Where-Object { $."Subnet Name" -eq "Dedicated ESX vmotion" -and $.Hostname -eq "clexncihyp001" } | Select-Object -ExpandProperty "VLAN ID"
The spaces in your CSV column header names require you to put some of the property names in quotes, but it’s still doable. I usually try to avoid spaces in CSV columns and properties.
-Gregg
Hi That doesnt workby Jules at 2013-03-26 00:33:25
This line returns nothing and no errors.
I have attached the CSV file.
Your csv file is tab-delimited so the Import-Csv cmdlet should include the Delimiter parameter:by leitsu at 2013-03-26 19:53:58Import-Csv C:\vmhosts.csv -Delimiter "t"</code><br>Though another problem is that in your csv file you have two columns which are both named "VLAN ID". If you rename one of them the import will work.<br>Because of an extra tab character in your file the Import-Csv cmdlet adds an extra Property named H1. This should not be a problem.</blockquote>by leitsu at 2013-03-26 16:28:14<blockquote>Doesnt look like thats the issue. I am still getting nothing from the command.<br>Import-csv C:\vmhosts.csv | Where-Object { $_."Subnet Name" -eq "Dedicated ESX vmotion" -and $_.Hostname -eq "clexncihyp001" } | Select-Object -ExpandProperty "VLAN ID"</blockquote>by poshoholic at 2013-03-26 18:29:58<blockquote>You have a few anomalies in your CSV file. First, there is a column in the middle with no title or values. Second, you have two "VLAN ID" columns. When importing a csv file, you should always try the import without any filters, see what errors you get. For example, if you try to import the csv file with this command:<br><code>Import-Csv .\NetConfig.csv -Delimiter "t"
You get the following error:ipcsv : The member "VLAN ID" is already present.
At line:1 char:1
+ ipcsv .\NetConfig.csv -Delimiter "t"<br>+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br> + CategoryInfo : NotSpecified: (:) [Import-Csv], ExtendedTypeSystemException<br> + FullyQualifiedErrorId : AlreadyPresentPSMemberInfoInternalCollectionAdd,Microsoft.PowerShell.Commands.ImportCsvCommand<br></code><br>Now that you know what the error is, use other Import-Csv parameters to work around it by defining the headers you want, like this:<br><code>Import-Csv .\NetConfig.csv -Delimiter "t" -Header Subnet,NumberOfGuests,VlanId,SubnetIP,SubnetMask,GatewayIP,EmptyColumn,HostName,SubnetName,VlanId2,AllocatedIP
I chose VlanId2 for the second VlanId field, and VlanId for the first. You can pick whatever names are appropriate for you. Also, for the empty column I simply called it EmptyColumn. If you run that and get the content from your CSV file, then you can use Where-Object and Select-Object to filter and get the property you want, making sure to use the header names specified in the Header property of Import-Csv, not the headers in the csv file itself.
Thanksby ps_gregg at 2013-03-26 20:02:44
That works
Another thing is it doesnt allow me to use wild card "* ESX Management". Is there a workaround for this? Thanks
With wild card:$a = Import-Csv .\NetConfig.csv -Delimiter "`t" -Header Subnet,NumberOfGuests,VlanId,SubnetIP,SubnetMask,GatewayIP,EmptyColumn,HostName,SubnetName,VlanId2,AllocatedIP
$a | Where-Object { $."SubnetName" -eq "* ESX Management" -and $.Hostname -eq "clexncihyp001" } | Select-Object -ExpandProperty "VlanId2"
If you use a wildcard () you need to use the "-like" comparison operator.
$_."SubnetName" -like " ESX Management"