Powershell: Search csv file

by leitsu at 2013-03-25 16:22:34

Hi

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?
by ps_gregg at 2013-03-25 20:25:22
Assuming your CSV file is named “C:\vmhosts.csv”, the following will provide you the value contained in the “VLAN ID” of the object.

Import-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
by leitsu at 2013-03-25 21:47:08
Hi That doesnt work
This line returns nothing and no errors.
I have attached the CSV file.
by Jules at 2013-03-26 00:33:25
Your csv file is tab-delimited so the Import-Csv cmdlet should include the Delimiter parameter:
Import-Csv C:\vmhosts.csv -Delimiter "t&quot;</code><br>Though another problem is that in your csv file you have two columns which are both named &quot;VLAN ID&quot;. 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 { $_.&quot;Subnet Name&quot; -eq &quot;Dedicated ESX vmotion&quot; -and $_.Hostname -eq &quot;clexncihyp001&quot; } | Select-Object -ExpandProperty &quot;VLAN ID&quot;</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 &quot;VLAN ID&quot; 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 &quot;t"
You get the following error:
ipcsv : The member “VLAN ID” is already present.
At line:1 char:1
+ ipcsv .\NetConfig.csv -Delimiter “t&quot;<br>+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br> + CategoryInfo &#58; NotSpecified&#58; (&#58;) &#91;Import-Csv&#93;, ExtendedTypeSystemException<br> + FullyQualifiedErrorId &#58; AlreadyPresentPSMemberInfoInternalCollectionAdd,Microsoft&#46;PowerShell&#46;Commands&#46;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 &quot;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.
by leitsu at 2013-03-26 19:53:58
Thanks
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"
by ps_gregg at 2013-03-26 20:02:44
If you use a wildcard () you need to use the “-like” comparison operator.

$_.“SubnetName” -like "
ESX Management”