Hi All,
I am looking to import a csv file in this format:
"DS","LunUuid","VM"
"L111_3_VM231","020005000060060e80102ae0300511a18b000000e3444636303046","VI-TEST-VM321"
pull out the LunUuid column and carve it like this:
Remove this >02 Keep this>0005 Remove this>000060060e80102ae0300511a18b00 Keep this>0000e3 Remove this>444636303046
then output to a new csv with two columns like this:
"data1","data2"
"0005","0000e3"
I think it's much easier to do this with a RegEx expression.
The following will extract the strings for $data1 and $data2
$text= "020005000060060e80102ae0300511a18b000000e3444636303046"
$text -match "^\w{2}(?<data1>\w{4})\w{30}(?<data2>\w{6})\w{12}" | Out-Null
$data1 = $matches["data1"] $data2 = $matches["data2"]
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
So far I have this:
$text.Count
$text= "020029000060060e80102ae0300511a18b000000fa444636303046"
$text.Remove(1,2 5,34 10,22)
#How do I group the sections I want to delete using the .Remove command?
I think it's much easier to do this with a RegEx expression.
The following will extract the strings for $data1 and $data2
$text= "020005000060060e80102ae0300511a18b000000e3444636303046"
$text -match "^\w{2}(?<data1>\w{4})\w{30}(?<data2>\w{6})\w{12}" | Out-Null
$data1 = $matches["data1"] $data2 = $matches["data2"]
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Try something like this
&{Import-Csv C:\input.csv -UseCulture | %{ $_.LunUuid -match "^\w{2}(?<data1>\w{4})\w{30}(?<data2>\w{6})\w{12}" | Out-Null
New-Object PSObject -Property @{ data1 = $matches["data1"] data2 = $matches["data2"] } }} | Export-Csv C:\output.csv -NoTypeInformation -UseCulture
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
That did it. Thank you. The person requesting the input into a Perl Script, wants to remove the leading two zeros "00" add "0x" of the SCSILUN column only. So:
The current output is:
"SCSILUN","LDEV"
"0000","0000de"
"0001","0000df"
"0002","0000e0"
I need the "SCSILUN" column to be edited only. The output should be this:
"SCSILUN","LDEV"
"0x00","0000de"
"0x01","0000df"
"0x02","0000e0"
I'm not sure if I need to use the .Replace function or .Trim. Any ideas?
You can do that with the RegEx expression and string concatenation.
Something like this
&{Import-Csv C:\input.csv -UseCulture | %{ $_.LunUuid -match "^\w{4}(?<data1>\w{2})\w{30}(?<data2>\w{6})\w{12}" | Out-Null
New-Object PSObject -Property @{ data1 = "0x" + $matches["data1"] data2 = $matches["data2"] } }} | Export-Csv C:\output.csv -NoTypeInformation -UseCulture
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Hi Luc,
Thanks again. My next step is add data to the column "VM" from another csv file:
This file is called "output.csv'
"LDEV","SCSILUN","VM"
"0000de","0x00",""
"0000de","0x00",""
"0000df","0x01",""
It seems like it should be a simple task. If logic serves me correctly, I should be able to import in this fashion (or something like it) and add to "VM" column:
import-csv VMfile.csv | select-object VM >>output.csv | select-object VM
After trying the above method, it only outputs the VMfile in the newly created csv file.
How do you link the entries in the VM CSV file with the other info ?
Is there a common field ?
Or is that VMfile.csv already in the correct order ?
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
File1 is:
"SCSILUN","LDEV","VM"
"0x00","0000de",
"0x01","0000df",
"0x02","0000e0",
"0x03","0000e1",
"0x04","0000e2",
"0x05","0000e3",
"0x06","0000e4",
"0x07","0000e5",
File2 is:
"VM"
""
"CentOS with Prelert"
"VI-SVCS-VM001"
"VI-SVCS-VM002"
"VI-SVCS-VM009"
"VI-SVCS-VM010"
"VI-SVCS-VM011"
"VI-SVCS-VM012"
"vi-svcs-vc3"
The VMfile.csv is already in the correct order. This is simply addition from File2.csv to File1.csv.
Best,
Rob.
Try something like this
$vms = Import-Csv file2.csv -UseCulture
$i = 0
Import-Csv file1.csv -UseCulture | %{ Select -InputObject $_ ScsiLun,LDev,@{N="VM";E={$vms[$i].VM}} $i++
} | Export-Csv result.csv -NoTypeInformation -UseCulture
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Almost there. This is my output:
"LDEV","SCSILUN","VM"
"0000de","0x00","@{VM=}"
"0000df","0x01","@{VM=}"
Looks lik the @{VM=} is not referencing the VM data.
Yes, that code was not correct.
I updated the code above, please try again
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
HI Luc,
works like a charm - I'm learning a whole lot here, I really appreciate your help and guidance. I may have to buy your book 🙂