Extracting Data from one CSV using another

This post has 6 Replies | 4 Followers

Not Ranked
Posts 5
ImpossibleSqui Posted: 03-06-2013 8:29 AM

Hello,

I am attempting to extract data out of a CSV using another CSV. I have a large amount of users in CSV1, with properties attached to these users. I have another csv2, which contains a list of only the users I want to look at. How can I use csv2 to extract the properties of only the users I want to look at?

Top 25 Contributor
Posts 174

This may not be the most efficient way, but it works.  I used two data files.

User Data contains the users with extra data:

 

UserName,Prop1,Prop2

Jason,abc,123

Matt,456,789

Debbie,def,123

Beth,456,thr

Tom,der,324

 

 

Username.csv contains only the user names that I am interested in.

 

UserName

Jason

Debbie

 

 

Here is the fun part:

$Path = "C:\PowerShell"

$UserList = Import-Csv -Path "$($path)\UserName.csv"
$UserData = Import-Csv -Path "$($path)\UserData.csv"

foreach ($User in $UserList)
{
ForEach ($Data in $UserData)
{
If($User.Username -eq $Data.UserName)
{
# Process the data
$Data
}
}

}

 

Top 25 Contributor
Posts 174

Here is one more example to if you want to keep it the data flowing through the PowerShell Pipeline.

 

$Path = "C:\PowerShell"

$UserList = Import-Csv -Path "$($path)\UserName.csv"
$UserData = Import-Csv -Path "$($path)\UserData.csv"

$UserList | ForEach-Object { $User = $_
$UserData | ForEach-Object {
If ($User.Username -eq $_.UserName)
{
Write-Output $_
}
}
} | FT -AutoSize # Keep it in the pipeline.

 

Top 10 Contributor
Posts 1,901
Microsoft MVP
Top Contributor

Hi, 

I would utilize the -contains operator that returns true if the collection (specified one the left side) contains the item (specified on the right side).

$users = Import-Csv -Path .\users.csv 
$userData = Import-Csv -Path .\Data.csv -Delimiter ';'

$userList = $users | select -ExpandProperty UserName 

$UserData | where {$userList -contains $_.username}

For the sake of simplicity let's assume my current location is set to the path where the files are. I load the data and load the users (filter) using Import-Csv cmdlet.

I decided to filter the items based on the userName property of the objects in the $userData collection. So I place it on the right side of the -contains operator. I investigate the property further and see it is a string and hence I need an array of stings on the left side of the operator. 

I look on the type of the $users collection by $users.GetType().FullName and see it is System.Object[] (array of Objects) but I need it to be array of strings -> I need to extract the values of the username properties on the objects. That can be done by the select -expandProperty so I do it. 

Now I have a string value on the right side and array of strings on the left side and can filter the input easily. 

Not Ranked
Posts 5

Gentlemen,

 

Thank you for your assistance with this! Just for my own learning, I tried both of these methods and had success with both. I only recently started my powershell learnings, and I have a long way to go to get to where I want.

In this case, being able to visually see the data was all I needed, and worked successfully, when using write-output, but if I attempted to export the manipulated data back to a csv, it would only export the last name from the list. Any suggestions for future CSV manipulations?

 

Top 10 Contributor
Posts 1,901
Microsoft MVP
Top Contributor

This exports the resulting set of data successfully for me:

$UserData | 
    where {$userList -contains $_.username} | 
        Export-Csv c:\temp\export.csv -NoTypeInformation -Delimiter ";"

You may need to change the delimiter or omit it completely based on you settings. 

Not Ranked
Posts 2

Hi Jason,

Thanks for this script. I used this for comparing two csv's  and got the result with username matches.Now, In this same script I also want to get the output for which the username does not match.

For Ex: If usernames.csv has

UserName

Jason

Debbie

Paul

and User Data contains the users with data:

 UserName,Prop1,Prop2

Jason,abc,123

Matt,456,789

Debbie,def,123

Beth,456,thr

Tom,der,324

Then answer file will look like 

UserName,Prop1,Prop2

Jason,abc,123

Debbie,def,123

Paul,NA,NA

I tired using else {$user.username} but it returns the usernames in multiple times. Help needed!. Thanks in advance.

Page 1 of 1 (7 items) | RSS
Copyright 2012 PowerShell.com. All rights reserved.