POWERSHELL - Add columns + update a few rows

Question

I am new to PowerShell.

I am currently trying to merge two different csvs:

One has 7 columns and 31 rows
The other has 3 columns and about 10 rows
They only have the PC column in common, the rest I have to add the columns

I have tried several manipulations, but without success. Here is the begining of 1st csv on the powershell console:

PS Z:\Partage> $CSVInitiale | Format-Table

PC              Processeur                               Taille_de_la_RAM Capacite_carte_reseau Taille_du_dique_dur Systeme_d_exploitation
--              ----------                               ---------------- --------------------- ------------------- ----------------------
ADSERVER        Intel(R) Core(TM) i7-9700K CPU @ 3.60GHz 2 GB             1 GB                  60,00 GB            Microsoft Windows Server 2016 Datacenter
PCW10           Intel(R) Core(TM) i7-9700K CPU @ 3.60GHz 2 GB             1 GB                  60,00 GB            Microsoft Windows 10 ?ducation
PCW7            Intel(R) Core(TM) i7-9700K CPU @ 3.60GHz 1 GB             1 GB                  80,00 GB            Microsoft Windows 7 Professionnel
User1_PCW10     Intel(R) Core(TM) i5-5900K CPU @ 1.60GHz 12 GB            420 MB                900 GB              Windows 10 ?ducation
User2_PCW7      Intel(R) Core(TM) i3-4600K CPU @ 1.60GHz 8 GB             200 MB                250 GB              Microsoft Windows 7 Professionnel
User3_PCW10     Intel(R) Core(TM) i7-8900K CPU @ 3.60GHz 16 GB            2 GB                  1500 GB             Windows 10 ?ducation
User4_PCW95     Intel(R) Core(TM) i3-2300K CPU @ 1.60GHz 2 GB             100 MB                120 GB              Windows 95
User5_PCW7      Intel(R) Core(TM) i5-9600K CPU @ 3.60GHz 24 GB            1 GB                  900 GB              Windows 7 Professionnel
User6_PCW10     Intel(R) Core(TM) i5-5900K CPU @ 1.60GHz 12 GB            420 MB                900 GB              Windows 10 ?ducation
User7_PCWVista  Intel(R) Core(TM) i5-1900K CPU @ 1.60GHz 1 GB             50 MB                 120 GB              Windows Vista Professionel
User8_PCW10     Intel(R) Core(TM) i5-5900K CPU @ 1.60GHz 12 GB            420 MB                900 GB              Windows 10 ?ducation
User9_PCW7      Intel(R) Core(TM) i5-8900K CPU @ 3.60GHz 16 GB            1 GB                  900 GB              Windows 7 Professionnel
User10_PCW7     Intel(R) Core(TM) i3-5900K CPU @ 2.60GHz 6 GB             500 MB                200 GB              Windows 7 Professionnel
User11_PCW10    Intel(R) Core(TM) i5-5900K CPU @ 1.60GHz 12 GB            420 MB                900 GB              Windows 10 ?ducation
User12_PCW7     Intel(R) Core(TM) i3-4600K CPU @ 1.60GHz 8 GB             200 MB                250 GB              Microsoft Windows 7 Professionnel
User13_PCW10    Intel(R) Core(TM) i7-8900K CPU @ 3.60GHz 16 GB            2 GB                  1500 GB             Windows 10 ?ducation
User14_PCW95    Intel(R) Core(TM) i3-2300K CPU @ 1.60GHz 2 GB             100 MB                120 GB              Windows 95
User15_PCW7     Intel(R) Core(TM) i5-9600K CPU @ 3.60GHz 24 GB            1 GB                  900 GB              Windows 7 Professionnel
User16_PCW10    Intel(R) Core(TM) i5-5900K CPU @ 1.60GHz 12 GB            420 MB                900 GB              Windows 10 ?ducation
User17_PCWVista Intel(R) Core(TM) i5-1900K CPU @ 1.60GHz 1 GB             50 MB                 120 GB              Windows Vista Professionel
User18_PCW10    Intel(R) Core(TM) i5-5900K CPU @ 1.60GHz 12 GB            420 MB                900 GB              Windows 10 ?ducation
User19_PCW7     Intel(R) Core(TM) i5-8900K CPU @ 3.60GHz 16 GB            1 GB                  900 GB              Windows 7 Professionnel
User20_PCW7     Intel(R) Core(TM) i3-5900K CPU @ 2.60GHz 6 GB             500 MB                200 GB              Windows 7 Professionnel
User21_PCW10    Intel(R) Core(TM) i5-5900K CPU @ 1.60GHz 12 GB            420 MB                900 GB              Windows 10 ?ducation
User22_PCW7     Intel(R) Core(TM) i3-4600K CPU @ 1.60GHz 8 GB             200 MB                250 GB              Microsoft Windows 7 Professionnel
User23_PCW10    Intel(R) Core(TM) i7-8900K CPU @ 3.60GHz 16 GB            2 GB                  1500 GB             Windows 10 ?ducation
User24_PCW95    Intel(R) Core(TM) i3-2300K CPU @ 1.60GHz 2 GB             100 MB                120 GB              Windows 95
User25_PCW7     Intel(R) Core(TM) i5-9600K CPU @ 3.60GHz 24 GB            1 GB                  900 GB              Windows 7 Professionnel
User26_PCW10    Intel(R) Core(TM) i5-5900K CPU @ 1.60GHz 12 GB            420 MB                900 GB              Windows 10 ?ducation
User27_PCWVista Intel(R) Core(TM) i5-1900K CPU @ 1.60GHz 1 GB             50 MB                 120 GB              Windows Vista Professionel
User28_PCW10    Intel(R) Core(TM) i5-5900K CPU @ 1.60GHz 12 GB            420 MB                900 GB              Windows 10 ?ducation
User29_PCW7     Intel(R) Core(TM) i5-8900K CPU @ 3.60GHz 16 GB            1 GB                  900 GB              Windows 7 Professionnel
User30_PCW7     Intel(R) Core(TM) i3-5900K CPU @ 2.60GHz 6 GB             500 MB                200 GB              Windows 7 Professionnel

And the second csv on the powershell console :

2nd Csv

PS Z:\Partage> $PrestaCSV | Format-Table

PC           Nom_du_prestataire Date_de_Migration
--           ------------------ -----------------
PCW          esgi-src12\presta1 17_07_21_01:35
PWX          esgi-src12\presta1 17_07_21_01:36
User10_PCW7  esgi-src12\presta1 17_07_21_01:50
User6_PCW10  esgi-src12\presta1 17_07_21_01:55
User6_PCW10  esgi-src12\presta1 17_07_21_01:56
User6_PCW10  esgi-src12\presta1 17_07_21_02:01
User11_PCW10 esgi-src12\presta1 17_07_21_02:01
User25_PCW7  esgi-src12\presta2 17_07_21_02:07

What I would like to do is : 1 - Add the last two columns in 1st csv 2 - Update only the concerned rows in first csv

Like that :Example array

1st CSV :            2nd csv:                 Result:
| A | B | C |       | A | Y | X |            | A | B | C | Y | X |
|---|---|---|       |---|---|---|            |---|---|---|---|---|
| A1| B1| C1|       | A2| Y2| X2|----------\ | A1| B1| C1|   |   |   
| A2| B2| C2|   +   | A3| Y3| X3|----------/ | A2| B2| C2| Y2| X2| 
| A3| B3| C3|       |   |   |   |            | A3| B3| C3| Y3| X3|

Here is the script I tried to do:

# TEST 4 => Crée les deux colonnes supplémentaires mais ne met pas les bonnes données
$NomCsvInitiale = "\\ADSERVER\Inventaire\InventairePCAD.csv"
$CSVInitiale = Import-Csv -Path '\\ADSERVER\Inventaire\InventairePCAD.csv' -Delimiter ','
$Folder = "\\ADSERVER\Inventaire\"
$GetAllCsv = (Get-ChildItem -Path $Folder -Filter 'Presta*.csv').FullName
$PrestaCSV = Import-Csv -Path $GetAllCsv -Delimiter ','

foreach ( $PrestaCSV1 in $PrestaCSV) {
    if ($CSVInitiale.PC -eq $PrestaCSV1.PC) {
        $CSVInitiale | Select-Object *, @{n="Nom_du_prestataire";e={$PrestaCSV1.Nom_du_prestataire}}, @{n="Date_de_Migration";e={$PrestaCSV1.Date_de_Migration}} | Export-Csv -Path $NomCsvInitiale -NoTypeInformation;
        } elseif ($CSVInitiale.PC -ne $PrestaCSV1.PC) {
        $CSVInitiale | Select-Object *, @{n="Nom_du_prestataire";e={" "}}, @{n="Date_de_Migration";e={" "}} | Export-Csv -Path $NomCsvInitiale -NoTypeInformation;
}
}

It does add the two columns but it only puts the last row on all PCs.

Sample of result actually :

PS Z:\Partage> $PrestaCSV

PC           Nom_du_prestataire Date_de_Migration
--           ------------------ -----------------
PCW          esgi-src12\presta1 17_07_21_01:35
PWX          esgi-src12\presta1 17_07_21_01:36
User10_PCW7  esgi-src12\presta1 17_07_21_01:50
User6_PCW10  esgi-src12\presta1 17_07_21_01:55
User6_PCW10  esgi-src12\presta1 17_07_21_01:56
User6_PCW10  esgi-src12\presta1 17_07_21_02:01
User11_PCW10 esgi-src12\presta1 17_07_21_02:01
User25_PCW7  esgi-src12\presta2 17_07_21_02:07


PS Z:\Partage> $CSVInitiale | Format-Table

PC              Processeur                               Taille_de_la_RAM Capacite_carte_reseau Taille_du_dique_dur Systeme_d_exploitation                   Nom_du_prestataire Date_de_Migration
--              ----------                               ---------------- --------------------- ------------------- ----------------------                   ------------------ -----------------
ADSERVER        Intel(R) Core(TM) i7-9700K CPU @ 3.60GHz 2 GB             1 GB                  60,00 GB            Microsoft Windows Server 2016 Datacenter esgi-src12\presta2 17_07_21_02:07
PCW10           Intel(R) Core(TM) i7-9700K CPU @ 3.60GHz 2 GB             1 GB                  60,00 GB            Microsoft Windows 10 ?ducation           esgi-src12\presta2 17_07_21_02:07
PCW7            Intel(R) Core(TM) i7-9700K CPU @ 3.60GHz 1 GB             1 GB                  80,00 GB            Microsoft Windows 7 Professionnel        esgi-src12\presta2 17_07_21_02:07
User1_PCW10     Intel(R) Core(TM) i5-5900K CPU @ 1.60GHz 12 GB            420 MB                900 GB              Windows 10 ?ducation                     esgi-src12\presta2 17_07_21_02:07
User2_PCW7      Intel(R) Core(TM) i3-4600K CPU @ 1.60GHz 8 GB             200 MB                250 GB              Microsoft Windows 7 Professionnel        esgi-src12\presta2 17_07_21_02:07
User3_PCW10     Intel(R) Core(TM) i7-8900K CPU @ 3.60GHz 16 GB            2 GB                  1500 GB             Windows 10 ?ducation                     esgi-src12\presta2 17_07_21_02:07
User4_PCW95     Intel(R) Core(TM) i3-2300K CPU @ 1.60GHz 2 GB             100 MB                120 GB              Windows 95                               esgi-src12\presta2 17_07_21_02:07
User5_PCW7      Intel(R) Core(TM) i5-9600K CPU @ 3.60GHz 24 GB            1 GB                  900 GB              Windows 7 Professionnel                  esgi-src12\presta2 17_07_21_02:07
User6_PCW10     Intel(R) Core(TM) i5-5900K CPU @ 1.60GHz 12 GB            420 MB                900 GB              Windows 10 ?ducation                     esgi-src12\presta2 17_07_21_02:07

Reading the forums, I thought I understood that it was not possible to collect them directly, it was necessary to recreate a csv file, is that right? That doesn't work anymore with me :(

Regards.

What you are trying to do has often been called Join-object. The word Join comes from SQL and the world of relational databases. There has been interest in adding a Join-Object to powershell for about ten years now. You can read up on this at [this web page](https://devblogs.microsoft.com/powershell/join-object/). — Walter Mitty, Jul 17 '21 at 10:38
PLEASE, do not post images of data/errors/code. why? lookee ... Why not upload images of code/errors when asking a question? - Meta Stack Overflow — https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question — Lee_Dailey, Jul 17 '21 at 10:41
Here is a sample of a Join-Object project: [Github Join-Object](https://github.com/iRon7/Join-Object). — Walter Mitty, Jul 17 '21 at 10:44
@WalterMitty thank you for your quickly reply, i'll try this and i answer what is the result ! — Adha, Jul 17 '21 at 12:11
@Adha - excellent! that made things easier ... and i see that Theo posted an Answer that does what you need. double excellent! [*grin*] — Lee_Dailey, Jul 18 '21 at 10:04
As pointed out by [@Walter](https://stackoverflow.com/users/19937/walter-mitty), using this [`Join-Object script`](https://www.powershellgallery.com/packages/Join)/[`Join-Object Module`](https://www.powershellgallery.com/packages/JoinModule) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): the command is presumably as simple as: `$inventory |Join $addendum -On PC`. The other advantage is probably the performance as is able to stream the LHS and it uses a hashtable (binary search) for the RHS. — iRon, Jul 19 '21 at 08:13

score 1 · Accepted Answer · answered Jul 17 '21 at 14:30

You can add columns from the second csv to the first csv file based on a matching value in the PC column like below:

$NomCsvInitiale = '\\ADSERVER\Inventaire\InventairePCAD.csv'
$NomCsvPresta   = '\\ADSERVER\Inventaire\Presta.csv'

# import both csv files
$inventory = Import-Csv -Path $NomCsvInitiale -Encoding UTF8
$addendum  = Import-Csv -Path $NomCsvPresta -Encoding UTF8

# loop over the items in the $inventory data
$result = foreach ($item in $inventory) {
    # try and find a matching item in the $addendum, based op the PC column
    $matchingItem = $addendum | Where-Object { $_.PC -eq $item.PC }
    # output a new object with the two columns added
    $item | Select-Object *, 
                          @{Name = 'Nom_du_prestataire'; Expression = {$matchingItem.Nom_du_prestataire}},
                          @{Name = 'Date_de_Migration'; Expression =  {$matchingItem.Date_de_Migration}}
}

# save the captured new data to file
$result | Export-Csv -Path $NomCsvInitiale -NoTypeInformation -Encoding UTF8

Import-Csv uses the comma as delimiter by default, so in this case you don't have to specify that
I've noticed that with your data, you need to add parameter -Encoding UTF8, in order to treat the data correctly: Microsoft Windows 10 ?ducation --> Microsoft Windows 10 Éducation

Oh yes that works fine ! I was afraid that it would overwrite the other rows, but since we use the csv as a base it's fine! I think I ended up getting confused in all my tests! Thanks to you! You saved my day ! — Adha, Jul 17 '21 at 14:50

POWERSHELL - Add columns + update a few rows

1 Answers1