asked on

Exporting in CSV is giving incorrect result

In the below code I am trying to merge two csv files on the basis of one common parameter - Employee id. I am getting the desired result in powershell , however, while exporting to a csv I am getting incorrect data.

$UserList=Import-Csv -Path "j:\testad.csv"
$UserData = Import-Csv "j:\testswn.csv"
Foreach ($User in $UserList)
{
Foreach($Data in $UserData)
{
If($User.EmployeeId -eq $Data.EmployeeId)
{

       $a=$Data.EmployeeID
       $b=$User.Givenname
$c=$User.sn
       $d=$User.EmailAddress
       $e=$User.StreetAddress
       $f=$User.City
       $g=$User.State
       $h=$User.Country
       $i=$User.PostalCode
       $j=$Data.Telephone
       $k=$Data.PersonalEmail

       $e=$a+" "+ $b+ " " + $c+" "+$d+" "+$e+" "+$f+" "+$g+" "+$h+" "+$i+" "+$j+" "+$k
$temp= $e
       $temp

}
}

}
$temp|Export-Csv "j:\testresult2.csv" -NoTypeInformation

oBdA

Tr this:

$UserList = Import-Csv -Path "j:\testad.csv"
$UserData = Import-Csv -Path "j:\testswn.csv"
$Lookup = @{}
$UserData | ForEach-Object {$Lookup[$_.EmployeeID] = $_}
$UserList | Select-Object -Property `
	EmployeeID, Givenname, sn, EmailAddress, StreetAddress, City, State, Country, PostalCode, `
	@{n='Telephone'; e={$Lookup[$_.EmployeeID].Telephone}}, @{n='PersonalEmail'; e={$Lookup[$_.EmployeeID].PersonalEmail}} |
	Export-Csv -NoTypeInformation -Path "j:\testresult2.csv"

Open in new window

Aakash Aggarwal

ASKER

Now I am getting the data in CSV, however the result is giving data for all emp id in csv file2, however, it should give only for those which have same emp ids in table 1.

ASKER CERTIFIED SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aakash Aggarwal

ASKER

Everything is fine but the thing is in one CSV file for some Employees id they are in 6 digits and others in 5 digits, so there the matching result we are getting is incorrect.

Ex- If in my first CSV there are 2 records of Employee id - 123456, 034567 where as in second CSV file it is like- 123456, 34567(0 is not there), so how can we change the equate equation for employee ids so that we can match 6 digits employee id with 5 digits in second csv?

$UserData | ForEach-Object {$Lookup[$_.EmployeeID] = $_}