Mark Minasi's Tech Forum
Sign up Calendar Latest Topics
 
 
 


Reply
  Author   Comment  
meloao

New Friend (or an Old Friend who Built a New Account)
Registered:
Posts: 95
Reply with quote  #1 
I have a question about searching the an array for specific values.  

The array ($arrayUsers) has the following values:
EMPLOYEE_ID         : 00000 
EMPLOYEE_FNAME  : John
EMPLOYEE_LNAME  : Smith
EMPLOYEE_PHONE   : 123-456-789

EMPLOYEE_ID:         11111
EMPLOYEE_FNAME:   Tom
EMPLOYEE_LNAME:   Jones
EMPLOYEE_PHONE   : 987-654-3210

EMPLOYEE_ID:          22222
EMPLOYEE_FNAME:   Sam
EMPLOYEE_LNAME:   Jackson
EMPLOYEE_PHONE   : 483-000-1110

I created code to prompt the user for EMPLOYEE_ID, EMPLOYEE_FNAME, and EMPLOYEE_LNAME using the following variables $employeeID, $inputFirstName, $inputLastName, $inputPhone.

I would like to be able to search the array to look for the record that has the match for employee id, employee first name, and employee last name and then change the phoneNumber to something.  Any ideas?  
0
cj_berlin

Avatar / Picture

Senior Member
Registered:
Posts: 375
Reply with quote  #2 
Hi,

is each line in your listing a separate array element? Normally, for structured data you would either use an array of hashtables, like

$users = @(
@{'EMPLOYYE_ID'='00000';'EMPLOYEE_FNAME'='John';'EMPLOYEE_LNAME'='Smith';
'EMPLOYEE_PHONE'='123-456-789'}
@{'EMPLOYYE_ID'='00001';'EMPLOYEE_FNAME'='Jack';'EMPLOYEE_LNAME'='Doe';'EMPLOYEE_PHONE'='123-456-766'}
#etc.
)


or, even better, an array of custom objects which you can build from hashtables:


$users = @(
[PSCustomObject]@{'EMPLOYEE_ID'='00000';'EMPLOYEE_FNAME'='John';'EMPLOYEE_LNAME'='Smith';
'EMPLOYEE_PHONE'='123-456-789'}
[PSCustomObject]@{'EMPLOYEE_ID'='00001';'EMPLOYEE_FNAME'='Jack';'EMPLOYEE_LNAME'='Doe';'EMPLOYEE_PHONE'='123-456-766'}
#etc.
)


If you really have an unstructured array of text string as depicted in your post, it will be more complicated. Helpful keywords are 'regex pattern matching' and 'for-loop', the latter because you need to find the phone record that immediately follows the id and name.

__________________
Evgenij Smirnov

My personal blog (German): http://www.it-pro-berlin.de/
My stuff on PSGallery: https://www.powershellgallery.com/profiles/it-pro-berlin.de/
0
meloao

New Friend (or an Old Friend who Built a New Account)
Registered:
Posts: 95
Reply with quote  #3 
Let me provide my detail:

I have a csv file that contains columns for EMPLOYEE_ID, EMPLOYEE_FNAME, and EMPLOYEE_PHONE with a '|' delimiter.

The file looks like this:

EMPLOYEE_ID | EMLPLOYEE_FNAME | EMPLOYEE_LNAME | EMPLOYEE_PHONE
00000 | John | Smith | 123=456=789
11111 | Tom | Jones | 987-654-3210
22222 | Sam | Jackson | 483-000-1110

The code so far is :
$source = Import-Csv c:\downloads\users.csv -Delimiter '|'


$inputFirstName = Read-Host -Prompt "Enter First Name for new account" 
$inputLastName = Read-Host -Prompt "Enter First Name for new account" 
$inputEmployeeNumber = Read-Host -Prompt "Enter First Name for new account" 


When I run $source that the command line it displays the data as I wrote in my original post. 


What I am looking to do is to match $inputFirstName, $inputLastName, and $inputEmployeeNumber with the values in the spreadsheet.  I need to get that record and change the phone number.

An example would be if $inputFirstName is Tom, $inputLastName is Jones, and $inputEmployeeNumber is 11111, change phone number to '999-658-0465'

To select the record would be $source[1],  just need help with comparing the values.

0
meloao

New Friend (or an Old Friend who Built a New Account)
Registered:
Posts: 95
Reply with quote  #4 
Quote:
Originally Posted by meloao
Let me provide detail:

I have a csv file that contains columns for EMPLOYEE_ID, EMPLOYEE_FNAME, and EMPLOYEE_PHONE with a '|' delimiter.

The file looks like this:

EMPLOYEE_ID | EMLPLOYEE_FNAME | EMPLOYEE_LNAME | EMPLOYEE_PHONE
00000 | John | Smith | 123=456=789
11111 | Tom | Jones | 987-654-3210
22222 | Sam | Jackson | 483-000-1110

The code so far is :
$source = Import-Csv c:\downloads\users.csv -Delimiter '|'


$inputFirstName = Read-Host -Prompt "Enter First Name for new account" 
$inputLastName = Read-Host -Prompt "Enter First Name for new account" 
$inputEmployeeNumber = Read-Host -Prompt "Enter First Name for new account" 


When I run $source that the command line it displays the data as I wrote in my original post. 


What I am looking to do is to match $inputFirstName, $inputLastName, and $inputEmployeeNumber with the values in the spreadsheet.  I need to get that record and change the phone number.

An example would be if $inputFirstName is Tom, $inputLastName is Jones, and $inputEmployeeNumber is 11111, change phone number to '999-658-0465'

To select the record would be $source[1],  just need help with comparing the values.

0
cj_berlin

Avatar / Picture

Senior Member
Registered:
Posts: 375
Reply with quote  #5 
OK, so you're actually working with custom objects already. Then it's easy:


$record = $source | Where-Object {($_.EMPLOYEE_ID -eq $inputEmployeeNumber) -and ($_.EMPLOYEE_FNAME -eq $inputFirstName) -and ($_.EMPLOYEE_LNAME -eq $inputLastName)}
if ($record.Count -eq 0) {
    Write-Host "No employee record found"
} elseif ($record.Count -gt 1) {
    Write-Warning "Multiple employee records found! Check the spreadsheet data for duplicates!"
} else {
    $index = $source.IndexOf($record)
    $source[$index].EMPLOYEE_PHONE = $inputPhone
}

__________________
Evgenij Smirnov

My personal blog (German): http://www.it-pro-berlin.de/
My stuff on PSGallery: https://www.powershellgallery.com/profiles/it-pro-berlin.de/
0
meloao

New Friend (or an Old Friend who Built a New Account)
Registered:
Posts: 95
Reply with quote  #6 

That's great!  So I was added the code above and it ran no issue.

It found the record.  I verified by highlighting  the $record variable.


$source[$index].EMPLOYEE_PHONE correctly evaluates to the new phone number.  When I open $source, in this case a .dat file, the phone number has not been updated.  The file is closed when I run the script.   
0
cj_berlin

Avatar / Picture

Senior Member
Registered:
Posts: 375
Reply with quote  #7 
Well, the $source array is not tied to the original file. You need to use Export-CSV to write updated info to the file.
__________________
Evgenij Smirnov

My personal blog (German): http://www.it-pro-berlin.de/
My stuff on PSGallery: https://www.powershellgallery.com/profiles/it-pro-berlin.de/
0
meloao

New Friend (or an Old Friend who Built a New Account)
Registered:
Posts: 95
Reply with quote  #8 
$source | export-csv $filename

?
0
cj_berlin

Avatar / Picture

Senior Member
Registered:
Posts: 375
Reply with quote  #9 
That's the one.
__________________
Evgenij Smirnov

My personal blog (German): http://www.it-pro-berlin.de/
My stuff on PSGallery: https://www.powershellgallery.com/profiles/it-pro-berlin.de/
0
meloao

New Friend (or an Old Friend who Built a New Account)
Registered:
Posts: 95
Reply with quote  #10 
That command results in the following error:

export-csv: cannot append csv content to the following file $fileName.  The appended object does not have a property that corresponds to the following column......

I verified that the column names match the column names in the custom object.
0
cj_berlin

Avatar / Picture

Senior Member
Registered:
Posts: 375
Reply with quote  #11 
Why would you be trying to append? $source has all objects so in theory, you'd only need to replace the initial CSV with the contents of $source...
__________________
Evgenij Smirnov

My personal blog (German): http://www.it-pro-berlin.de/
My stuff on PSGallery: https://www.powershellgallery.com/profiles/it-pro-berlin.de/
0
meloao

New Friend (or an Old Friend who Built a New Account)
Registered:
Posts: 95
Reply with quote  #12 
My bad.  I was thinking of a different script  (I am working on a script for both append and update record)

I added 

<code>
$source | export-csv $filename -NoTypeInformation
</code>


Opening the file I see that the row was updated with the appropriate information. However, all the data and the headers are surrounded by double quotes ""

Anyway to remove those?








0
meloao

New Friend (or an Old Friend who Built a New Account)
Registered:
Posts: 95
Reply with quote  #13 

Don't know if this is the most elegant, but the code below does remove double quotes.

(Get-Content $fileName) | % {$_ -replace '"', ''} | Out-File -FilePath $fileName -Force -Encoding ascii

 

0
cj_berlin

Avatar / Picture

Senior Member
Registered:
Posts: 375
Reply with quote  #14 
Quote:
Originally Posted by meloao

Don't know if this is the most elegant, but the code below does remove double quotes.

(Get-Content $fileName) | % {$_ -replace '"', ''} | Out-File -FilePath $fileName -Force -Encoding ascii


only as long as there aren't any double quotes in the data

__________________
Evgenij Smirnov

My personal blog (German): http://www.it-pro-berlin.de/
My stuff on PSGallery: https://www.powershellgallery.com/profiles/it-pro-berlin.de/
0
meloao

New Friend (or an Old Friend who Built a New Account)
Registered:
Posts: 95
Reply with quote  #15 
Will not be a problem.  The data will contains letters of the alphabet and possibly digits --- no double quotes.  Thanks!
0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Easily create a Forum Website with Website Toolbox.