Merge two columns in CSV file from command line / PowerShell-CodePudding

I have a CSV file where either column "EAN" (col. 9) or column "UPC" (col. 10) is set (some lines with both). What I need is a CSV where both are combined into one column, something like if EAN <> "" use EAN else use UPC to be put into a batch/PowerShell file. Additionally I need to keep col. 11, everything else can be discarded. The output file only has to contain two columns, either EAN or UPC plus stock level. Any help would be appreciated. The original CSV is semicolon seperated with possible empty values, but no quoted values with the seperator or newline characters. Example input:

company;dept;sku;desc;secsku;unit;size;year;ean;upc;stock;exact(CRLF)
Stack;Overflow;000-000;Question0;00;pcs;XL;2021;1111111111111;;1;6(CRLF)
Overflow;Stack;000-001;Question1;01;pcs;L;2021;;222222222222;1;9(CRLF)

The output should look like:

ean;stock(CRLF)
1111111111111;1(CRLF)
222222222222;1(CRLF)

CodePudding user response：

One way is to use a calculated property:

# import the source csv file
Import-Csv -Path 'D:\Test\test.csv' -Delimiter ';' | 
# select only two of its properties where 'ean' is a calculated property
Select-Object @{Name = 'ean'; Expression = {if(![string]::IsNullOrWhiteSpace($_.ean)) {$_.ean} else {$_.upc}}}, stock |
# output to a new CSV file
Export-Csv -Path 'D:\Test\updated.csv' -Delimiter ';' -NoTypeInformation

Another way of doing that (less concise) is by using a PsCustomObject

# import the source csv file
Import-Csv -Path 'D:\Test\test.csv' -Delimiter ';' | 
# loop through the data
ForEach-Object {
    # output an object with two properties
    [PsCustomObject]@{
        ean   = if(![string]::IsNullOrWhiteSpace($_.ean)) { $_.ean } else { $_.upc }
        stock = $_.stock
    }
} |
# output to a new CSV file
Export-Csv -Path 'D:\Test\updated.csv' -Delimiter ';' -NoTypeInformation