Query CSV file based on a column value using Power Shell

Question

I have this C:\test.csv file:-

and i am importing this file and read the data as follow:-

$SourceID = @()
$DestinationID = @()

Import-Csv C:\test.csv |`
    ForEach-Object {
        $SourceID += $_.SourceID
        $DestinationID += $_."DestinationID"
    }

but how i can query the .CSV file based on the source ID? for example to get the DestinationID for the SourceID = 1?

Thanks

Changing ForEach-Object to Where-Object { $_.SourceId -eq 1 } should be it — Santiago Squarzon
– Santiago Squarzon, Commented Sep 28, 2022 at 13:03
@SantiagoSquarzon so i will need to Import-Csv each time i need to do the query ? or i can do the Import-Csv once and do multiple queries ? — John John
– John John, Commented Sep 28, 2022 at 13:07
you can store the object produced from CSV parsing in a variable and then just query that variable -> $csv | Where-Object ... — Santiago Squarzon
– Santiago Squarzon, Commented Sep 28, 2022 at 13:07
@SantiagoSquarzon can you provide more code please? so i do not miss what you are referring to ? — John John
– John John, Commented Sep 28, 2022 at 13:11
You don't need the continuation mark after the pipe. I would make a hash table. Import-Csv test.csv | foreach-object { $hash = @{} } { $hash[$_.sourceid] = $_.destinationid } — js2010
– js2010, Commented Sep 28, 2022 at 13:17

js2010 · Accepted Answer · 2022-09-28 14:45:53Z

1

I would make a hash table. The first script block is a 'begin' clause. This can be a good time saver with a large file. $hash[1] doesn't work.

Import-Csv c:\test.csv |
  foreach-object { $hash = @{} } { $hash[$_.sourceid] = $_.destinationid }


$hash['1']
 
10


$hash['2']

20


$hash.gettype()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Hashtable                                System.Object

edited Sep 28, 2022 at 14:45

answered Sep 28, 2022 at 14:05

js2010

28.3k6 gold badges82 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John John Over a year ago

can you please check my other question based on your reply stackoverflow.com/questions/73884847/… ?

Collectives™ on Stack Overflow

Query CSV file based on a column value using Power Shell

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related