Hello,
First post here,
I’m writing a script that reads a CSV file, it does everything I want it to do, but how would get the value of CoreID, which is FMD354800203 from the array below?
@{Branch Number=8002; CoreID=FMD354800203; Available=Y; Workstation=}
Thanks,
Tony
Am I correct in assuming that your CSV file has the example data you posted as a value in a column?
If so, let’s assume that the column name is ‘Details’. You can use a regular expression to extract the value from CoreID. The example below does assume that the CoreID is always 3 letters followed by 9 digits, if that’s not the case the regex will need to be defined differently.
$csv = Import-Csv .\test.csv
foreach ($row in $csv) {
$row.Details -match '[A-Z]{3}\d{9}' | Out-Null
$matches.Values
}
That’s a hashtable not an array.
$hashtable = @{‘Branch Number’=8002; CoreID=‘FMD354800203’; Available=‘Y’; Workstation=‘’}
$hashtable.get_item(‘CoreID’)
I have a variable $FirstAvailable, which returns @{Branch Number=8002; CoreID=FMD354800201; Available=Y; Workstation=}
Now I have
$hashtable = $FirstAvailable
$hashtable.get_item(‘CoreID’)
But, it’s giving an error message
Method invocation failed because [Microsoft.PowerShell.Commands.MatchInfo] does not contain a method named ‘get_item’.
How would I paste the code into the forum like you did before?
Are you saying that your csv actually contains that line? What does $firstavailable.gettype() return?
PS C:\Windows\system32> $FirstAvailable.GetType()
IsPublic IsSerial Name BaseType
True False MatchInfo System.Object
This is what my CSV looks like
Branch Number, CoreID, Available, Workstation
8000, FMD354800000, N,
8000, FMD354800001, N, 2UA51427CH
8000, FMD354800002, Y,
8000, FMD354800003, N,
8000, FMD354800004, Y,
8001, FMD354800100, N,
8001, FMD354800101, N,
8001, FMD354800102, Y,
8001, FMD354800103, N,
8001, FMD354800104, N,
8002, FMD354800200, N,
8002, FMD354800201, Y,
8002, FMD354800202, N,
8002, FMD354800203, Y,
8002, FMD354800204, N,
8003, FMD3548000300, N,
8003, FMD3548000301, Y,
8003, FMD3548000302, Y,
8003, FMD3548000303, Y,
8003, FMD3548000304, Y,
5002, FMD354500200, Y,
7402, FMD3547402200, N,
7402, FMD3547402201, Y,
8989, FMD3548989200, Y,
8990, FMD3548989200, N,
8991, FMD3548989200, N,
$firstavailable = import-csv something.csv
$firstavailable.coreid
Other than that you’re going to have to share the entire script. I don’t know where you’re coming up with that type from a csv.
Dan,
I’ll try that, and I’ll share the entire script if it still doesn’t work. I appreciate your help, I’m new to powershell.
Below is my entire code
# creating variables
$Workstation = HOSTNAME.exe
$CoreIP = "Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\CORe\IP"
$CSV = '\\hqfs1\users\tantony\PowerShell\GetCoreID\CoreID.csv'
$ImportCSV = Import-Csv $CSV
$GetBranchNum = Read-Host "Enter branch number and press ENTER: "
# Checking the CSV to make sure the Branch Number is on the file
if($ImportCSV | Where-Object {$_.'Branch Number' -match $GetBranchNum})
{
Write-Host "Branch number found in CSV file!" -ForegroundColor Green
# checking if the Available is Y
if($ImportCSV | Where-Object {$_.'Branch Number' -eq $GetBranchNum -and $_.'Available' -eq "Y"})
{
# Searches for the branch number
$Find = $ImportCSV | Select-String -Pattern $GetBranchNum
# Searches for the available Y values in the branch number and selects the first index
$FirstAvailable = $Find -match "Y"| select -First 1
# Counts how many CoreIDs left
$HowManyLeft = ($Find -match "Y").Count
# Displaying message found an available CoreID for the branch
Write-Host "Found $HowManyLeft availalbe CoreId for this branch!" -ForegroundColor Green
# Changing the registry value
New-ItemProperty -path $CoreIP -name "TTable ID" -PropertyType String -Value $FirstAvailable -Force
Write-Host "First available CoreID is " $FirstAvailable
}
# if Available is N, display message below
else
{
Write-Host "Could not find availalbe CoreId for this branch!" -ForegroundColor Red
}
}
# Checking the CSV to make sure the Branch Number is on the file, if not, display message below
else
{
Write-Warning "Branch number not found in CSV file!"
}
This is what the result is when I run
Branch number found in CSV file!
Found 2 availalbe CoreId for this branch!
TTable ID : @{Branch Number=8002; CoreID=FMD354800201; Available=Y; Workstation=}
PSPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\CORe\IP
PSParentPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\CORe
PSChildName : IP
PSProvider : Microsoft.PowerShell.Core\Registry
First available CoreID is @{Branch Number=8002; CoreID=FMD354800201; Available=Y; Workstation=}
$workstation = $env:computername
I don’t have enough time to get through all of it. You had all you needed right here.
$items = $ImportCSV | Where-Object {$.‘Branch Number’ -eq $GetBranchNum -and $.Available -eq ‘Y’}
If this outputs all the Yes’s then you don’t need to find Y. Typically, we wouldn’t use select-string to find items in a csv.
Now practice how you can get what you want out of times.
$items.count
$items | select -first 1
($items | select -first 1).coreid
Thank you
I made a few modification to my script, and now I’m able to get the value that I want, so that’s now working.
How would I change the value of Y to a N, and save the CSV so that it wont’ grab the same value again?
# Change the Y to N - update CSV $ChangeAvailable = $Find.available[0]
# creating variables
$Workstation = $env:COMPUTERNAME
$CoreIP = "Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\CORe\IP"
$CSV = '\\hqfs1\users\tantony\PowerShell\GetCoreID\CoreID.csv'
$ImportCSV = Import-Csv $CSV
$GetBranchNum = Read-Host "Enter branch number and press ENTER: "
$Find = $ImportCSV | where {$_.'Branch Number'-eq $GetBranchNum -and $_.'Available' -eq "Y"}
# Checking the CSV to make sure the Branch Number is on the file
if($ImportCSV | Where-Object {$_.'Branch Number' -match $GetBranchNum})
{
Write-Host "Branch number found in CSV file!" -ForegroundColor Green
# checking if the branch number is on the CSV and available is Y
if($Find)
{
# Gets the first CoreID
$GrabCoreID = $Find.CoreID[0]
# Adds workstation s/n - update CSB
$AddWorkstation = $Find.Workstation[0]
# Change the Y to N - update CSV
$ChangeAvailable = $Find.available[0]
# Counts how many CoreIDs left
$HowManyLeft = ($Find -match "Y").Count
# Displaying message found an available CoreID for the branch
Write-Host "Found $HowManyLeft availalbe CoreId for this branch!" -ForegroundColor Green
# Changing the registry value
New-ItemProperty -path $CoreIP -name "TTable ID" -PropertyType String -Value $GrabCoreID -Force
}
# if Available is N, display message below
else
{
Write-Host "Could not find availalbe CoreId for this branch!" -ForegroundColor Red
}
}
# Checking the CSV to make sure the Branch Number is on the file, if not, display message below
else
{
Write-Warning "Branch number not found in CSV file!"
}
Take a look at this example. There are some problems you should be aware of. Read-Host is just getting whatever someone types in, so you need to do extra work to check things before you use it. In the example, if the branch doesn’t exist, it will just say the branch doesn’t exist and give you valid branches so the user isn’t guessing at what you’re looking for. Next is using a CSV as a flat file database. You have to be careful because if you (or someone else) opens the CSV with Excel, your script won’t work because the file is locked. Just some things to keep in mind. Play with the below and let us know if there are any other questions. I took the liberty of checking some other things that I assumed that would be relevant.
$csvPath = "C:\Users\Rob\Desktop\Archive\test.csv"
$csv = Import-CSV -Path $csvPath
$assignedCoreID = $csv | Where{$_.Workstation -eq $env:COMPUTERNAME}
if ( !$assignedCoreID ) {
$GetBranchNum = Read-Host "Enter branch number and press ENTER"
$branchNum = $csv | where {$_.'Branch Number'-eq $GetBranchNum}
if ($branchNum) {
$availableCoreID = $csv | where {$_.'Branch Number'-eq $GetBranchNum -and $_.'Available' -eq "Y"}
if ($availableCoreID) {
foreach ($record in $csv) {
if ($record."Branch Number" -eq $GetBranchNum -and $record.Available -eq "Y") {
"Setting CoreID {0} for workstation {1}" -f $record.CoreID, $env:COMPUTERNAME
#Update the Row with new values in the $csv object
$record.Available = "N"
$record.Workstation = $env:COMPUTERNAME
# Changing the registry value
#New-ItemProperty -path $CoreIP -name "TTable ID" -PropertyType String -Value $record.CoreID -Force
break
}
}
#Overwrite the existing CSV with the updated CSV object
$csv | Export-CSV -Path $csvPath -NoTypeInformation
"There are {0} CoreID(s) available for branch {1}" -f @($csv | where {$_.'Branch Number'-eq $GetBranchNum -and $_.'Available' -eq "Y"}).Count,$GetBranchNum
}
else {
"No CoreID available for {0}" -f $GetBranchNum
}
}
else {
"Branch Number {0} does not exist. Please enter a valid branch ID: `r`n`r`n{1}" -f $GetBranchNum, (($csv | Select -ExpandProperty "Branch Number" -Unique) -join "`r`n").ToString()
}
}
else {
"Workstation {0} already has an assigned CORE ID of {1} for Branch Number {2}" -f $env:COMPUTERNAME, ($assignedCoreID | Select -ExpandProperty CoreID), ($assignedCoreID | Select -ExpandProperty "Branch Number")
}