CODEWITHSUNDEEP
powershell
Yamez
Yamez

Reputation: 25

How can I concatenate csv colums and rename their header with Powershell?

I am attempting to merge two csv files together and select only two of their columns for use in a new csv. I don't understand why I cannot use the code I have already: 

$Temp1 = (Import-csv "C:\path\APPcsv.csv" -header "APP") | 
    select-object APP
$Temp2 = (Import-csv "C:\path\ALLdb42APPs.csv"-header "NA1", "NA2", "Applications", "NA3", "Project") | 
    select-object Project

$CSV= @($temp1, $temp2) | 
    export-csv -path "C:\path\Why isn't this working.csv" -noTypeInformation

Here is an example line from each CSV:

CSV1 (ALLdb42APPs.csv)

"Current Application","Calculation","AdobeReaderDC-18.011.20036 V1 - Add  Instalation Status: SUCCESSFUL","2018-05-16 08:54:17","DK ATM error main"

CSV2 (APPcsv.csv)

"DameWareService-10.0.0.0-x64 V2 - Add"

Upvotes: 0

Views: 71

Answers (1)

BenH
BenH

Reputation: 10044

So your issue is because @($temp1,$temp2) doesn't combine the first element of $temp1 with the first element of $temp2, but instead makes a new collection which is all of $temp1's objects followed by all of $temp2.

Since $temp1 is objects with an APP property and $temp2 is objects with a Project, combining these into a collection doesn't make sense to export to a csv.

If $temp1 is a bag of apples and $temp2 is a bag of oranges, @($temp1,$temp2) isn't holding the bags together, it's dumping both into one bag on top of each other.

You could either join the two objects into one. Warren Frame has a well respected module Join-Object that could be used as James C pointed out, but your two csvs would need to share a column.

The other alternative is to use a for loop, then in each iteration take the value from each collection and create a new object with both values.

$Temp1 = (Import-csv "C:\path\APPcsv.csv" -header "APP") | 
    Select-Object -ExpandProperty APP
$Temp2 = (Import-csv "C:\path\ALLdb42APPs.csv"-header "NA1", "NA2", "Applications", "NA3", "Project") | 
    Select-Object -ExpandProperty Project

$LargestIndex = [math]::Max($temp1.count,$temp2.count)

$CombinedArray = For ($i=0; $i -le $LargestIndex; $i++) {
    [pscustomobject]@{
        APP = $temp1[$i]
        Project = $temp2[$i]
    }
}

$CombinedArray | 
    Export-Csv -Path "C:\path\Example.csv" -NoTypeInformation

Note: requires PowerShell 3+ for the pscustomobject way of creating objects.

Upvotes: 1

Related Questions