CODEWITHSUNDEEP
powershellcsv
ace23
ace23

Reputation: 27

Parse line of text and match with parse of CSV

As a continuation of a script I'm running, working on the following.

I have a CSV file that has formatted information, example as follows:

File named Import.csv:

Name,email,x,y,z
\I\RS\T\Name1\c\x,email@jksjks,d,f
\I\RS\T\Name2\d\f,email@jsshjs,d,f
...

This file is large.

I also have another file called Note.txt.

Name1
Name2
Name3
...

I'm trying to get the content of Import.csv and for each line in Note.txt if the line in Note.txt matches any line in Import.csv, then copy that line into a CSV with append. Continue adding every other line that is matched. Then this loops on each line of the CSV.

I need to find the best way to do it without having it import the CSV multiple times, since it is large.

What I got does the opposite though, I think:

$Dir = PathToFile
$import = Import-Csv $Dir\import.csv
$NoteFile = "$Dir\Note.txt"
$Note = GC $NoteFile
$Name = (($Import.Name).Split("\"))[4]

foreach ($j in $import) {
  foreach ($i in $Note) {
    $j | where {$Name -eq "$i"} | Export-Csv "$Dir\Result.csv" -NoTypeInfo -Append
  }
}

This takes too long and I'm not getting the extraction I need.

Upvotes: 1

Views: 128

Answers (2)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174435

This takes too long and I'm not getting the extraction I need.

That's because you only assign $name once, outside of the outer foreach loop, so you're basically performing the same X comparisons for each line in the CSV.

I would rewrite the nested loops as a single Where-Object filter, using the -contains operator:

$Import |Where-Object {$Note -contains $_.Name.Split('\')[4]} |Export-Csv "$Dir\Result.csv" -NoTypeInformation -Append

Upvotes: 1

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200193

Group the imported data by your distinguishing feature, filter the groups by name, then expand the remaining groups and write the data to the output file:

Import-Csv "$Dir\import.csv" |
  Group-Object { $_.Name.Split('\')[4] } |
  Where-Object { $Note -contains $_.Name } |
  Select-Object -Expand Group |
  Export-Csv "$Dir\Result.csv" -NoType

Upvotes: 1

Related Questions