Updating data in .csv without overwriting the existing data / adding new data in powershell

Question

it is me again the Powershell-Newbie (i am working with powershell for 11 days by now).

So what is the problem i have so far: I am reading out csv files from different folders and merge the data into one big csv (bigCSV). The bigCSV is a living document, so i am adding data manually into this worksheet.

What I do / Example

The problem is, if I have to update the bigCSV (with new csv files) the existing bigCSV gets overwritten and my manually added data gets lost.

What I do / The Problem

I have tried to add -append to my code, but the result is not satisfying, because the manually added data still consits but now i have duplicates ...

New Problem

This is my Code so far ...

$Userpath_Access    = "$PSScriptRoot"
$txtFiles = Get-ChildItem -Path $Userpath_Access\txt-Import-Datenbank\ -Filter *.csv -Recurse -ErrorAction SilentlyContinue -Force

$csv = foreach ($file in $txtFiles) {
        Import-Csv -Path $file.FullName -Delimiter ";" | 
                      Select-Object 'Type (BAY)', 'Order Number (BAY)', 'PCP Number (PCP)', @{label='Project ID (Mosaic)';expression={$($_.'Project')}}, 'Priority (BAY)', @{label='Compound No (Mosaic)';expression={$($_.'Compound No')}},
                      @{label='Customer (Mosaic)';expression={$($_.'Requestor')}}, @{label='Date Order Created (Mosaic)';expression={$($_.'Order Date')}},  @{label='Comment (Mosaic)';expression={$($_.'Comment')}}, 'Status', 'Data Finalized', @{label='Compound Amount [mg] (Mosaic)';expression={$($_.'Amount')}},
                      'Mail Address', 'Duration', @{label='Name Of Customer (Mosaic)';expression={$($_.'Requestor')}}, @{label='Vial (Mosaic)';expression={$($_.'Vial')}}, @{label='Mosaic File (Mosaic)';expression={$($_.'Filename')}}, @{label='Date Received (Mosaic)';expression={$($_.'Data import')}},
                      @{label='Order ID (Mosaic)';expression={$($_.'Order ID')}}
}

$fileOut = Join-Path -Path $Userpath_Access -ChildPath ('Inh_Auftragsliste.csv')
$csv | Export-Csv -Path $fileOut -Delimiter ";" -NoTypeInformation -Append

Now I am searching for a solution, to just add the "new files" to my existing bigCSV.

I'm so sorry ... Corona got me, and i totally missed to give some feedback. :-( — TAL, Aug 24 '20 at 06:03
Yes I am healthy again and I hope, that im immune from now on. :) — TAL, Aug 25 '20 at 11:54

score 1 · Accepted Answer · answered Aug 04 '20 at 10:32

If I understand the question properly, you are not far off with your code. The main thing is that I think you need to add the converted data from Import-Csv and Select-Object to the big CSV file inside the loop.

Try:

$Userpath_Access = $PSScriptRoot
$fileOut = Join-Path -Path $Userpath_Access -ChildPath 'Inh_Auftragsliste.csv'  # the BigCSV

$txtFiles = Get-ChildItem -Path "$Userpath_Access\txt-Import-Datenbank" -Filter '*.csv' -File -Recurse -ErrorAction SilentlyContinue -Force

foreach ($file in $txtFiles) {
    Write-Host "Adding data from 'file $($file.FullName)'.."

    Import-Csv -Path $file.FullName -Delimiter ";" | 
        Select-Object 'Type (BAY)', 'Order Number (BAY)', 'PCP Number (PCP)', 
                      @{label='Project ID (Mosaic)';expression={$_.'Project'}}, 
                      'Priority (BAY)', 
                      @{label='Compound No (Mosaic)';expression={$_.'Compound No'}},
                      @{label='Customer (Mosaic)';expression={$_.'Requestor'}}, 
                      @{label='Date Order Created (Mosaic)';expression={$_.'Order Date'}},  
                      @{label='Comment (Mosaic)';expression={$_.'Comment'}}, 
                      'Status', 'Data Finalized',
                      @{label='Compound Amount [mg] (Mosaic)';expression={$_.'Amount'}},
                      'Mail Address', 'Duration', 
                      @{label='Name Of Customer (Mosaic)';expression={$_.'Requestor'}},
                      @{label='Vial (Mosaic)';expression={$_.'Vial'}}, 
                      @{label='Mosaic File (Mosaic)';expression={$_.'Filename'}}, 
                      @{label='Date Received (Mosaic)';expression={$_.'Data import'}},
                      @{label='Order ID (Mosaic)';expression={$_.'Order ID'}} |
    Export-Csv -Path $fileOut -Delimiter ";" -NoTypeInformation -Append
}

^{Minor details, but I have changed the quoting and unnecessary $() a bit}

score 0 · Answer 2 · answered Aug 04 '20 at 09:25

You could Import the CSV file to Excel sort it and use the -Unique flag then export it again.

Import-Csv C:\temp\UsersConsolidated.csv | sort lname,fname –Unique | Export-Csv C:\temp\UsersConsolidated.csv

I don't think there is a way to handle it while Exporting to the CSV.

score 0 · Answer 3 · answered Aug 04 '20 at 12:43

Looking to the previous question and if I correctly understand it, you have to object lists like:

$Old = Import-Csv .\Old.csv

Date       Filename  Type (BAY) ...
----       --------  ---------- ---
2020-08-01 File1.csv Type 1     Info 1
2020-08-02 File2.csv Type 2
2020-08-03 File3.csv Type 3

And:

$New = Import-Csv .\New.csv

Date       Filename  Type (BAY) ...
----       --------  ---------- ---
2020-08-04 File2.csv Type 2     Info 2
2020-08-04 File4.csv Type 4     Info 4

If you use this Join-Object (see also: In Powershell, what's the best way to join two tables into one?), you can merge the two lists like:

$Old | Merge-Object $New -on Filename

Date       Filename  Type (BAY) ...
----       --------  ---------- ---
2020-08-01 File1.csv Type 1     Info 1
2020-08-04 File2.csv Type 2     Info 2
2020-08-03 File3.csv Type 3
2020-08-04 File4.csv Type 4     Info 4

Updating data in .csv without overwriting the existing data / adding new data in powershell

3 Answers3