Link to home
Start Free TrialLog in
Avatar of Richard Gardner
Richard GardnerFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Copy data from one CSV into another line by line

I have two slightly different CSV's -

File1.csv = Contains columns ID, Date, Total (with correct Total data)
File2.csv = Contains columns ID, Date, Total (with incorrect Total data) and many other columns containing data

I want to end up with File3.csv with data from File2.csv but for each line in File2.csv I want to copy the corresponding data in 'Total' column from File2.csv.

Each line in File1.csv and File2.csv have the same data in ID and Date columns.

I have been desperately trying to use Loops within Loops to try and match records with no success. Is there an easy way of doing this line by line?
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Push eveything of importance from file one into a hashtable, then fill the Total value by linking on ID (between File1 and File2).
$Totals = @{}
Import-Csv File1.csv | ForEach-Object {
    $Totals.($_.ID) = $_.Total
}

Import-Csv File2.csv |
    Select-Object ID, Date, @{n='Total';e={ $Totals.($_.ID) }} |
    Export-csv File3.csv

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of oBdA
oBdA

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of oBdA
oBdA

That seems to be a one-off, where speed doesn't matter that much. The "Where" query is more about the learning curve for "I have been desperately trying to use Loops within Loops to try and match records".
Avatar of Richard Gardner

ASKER

Thanks - works perfectly. Unfortunately the goal posts have moved with what I am trying to achieve due to the data being provided.

I will start another post.