How to merge two csv files into a third one, without adding rows and columns-CodePudding

I would like to merge two CSV files of identical structure into a third file, filling it cell by cell, without adding any row or column, each line being separated by a paragraph break. Only one column, so far, in each file.

File1.csv:

Header
(Row1)first string from File1
(Row2)second string from File1

File2.csv:

Header
(Row1)first string from File2
(Row2)second string from File2

(Expected output) File3.csv:

 Header
 (Row1)first string from File1
  first string from File2
 (Row2)second string from File1
  second string from File2

What I don't want (but always get):

Header
(Row1)first string from File1
(Row2)first string from File2
(Row3)second string from File1
(Row4)second string from File2

I really searched a lot on the internet, but without success. If anyone has a solution, that would be a great help!

My code (so far) :

$thirdFile = @()
$firstFile = @(Import-Csv "Path\File1.csv")
$secondFile = @(Import-Csv "Path\File2.csv")
$MaxLength = [Math]::Max($firstFile.Length, $secondFile.Length)
for ($i = 0; $i -lt $MaxLength; $i  )
{ 
    $thirdFile =$firstFile[$i]
    $thirdFile =$secondFile[$i]
}
$thirdFile | Export-Csv "Path\File3.csv" -NoTypeInformation

CodePudding user response：

It looks like you want to combine the values for each item separated with a newline in between.

In that case you can do as below:

$firstFile  = @(Import-Csv "Path\File1.csv")
$secondFile = @(Import-Csv "Path\File2.csv")
$headers    = $firstFile[0].PsObject.Properties.Name
$maxRows    = [Math]::Max($firstFile.Count, $secondFile.Count)

$thirdFile = for ($i = 0; $i -lt $maxRows; $i  ) {
    if ($i -ge $firstFile.Count) { $secondFile[$i] }
    elseif ($i -ge $secondFile.Count) { $firstFile[$i] }
    else {
        # use an ordered Hashtable to collect and merge the values in each field
        $row = [ordered]@{}
        foreach ($header in $headers) {
            $row[$header] = '{0}{1}{2}' -f $firstFile[$i].$header, [environment]::NewLine, $secondFile[$i].$header
        }
        # cast to PsCustomObject and output so it gets collected in variable $thirdFile
        [PsCustomObject]$row
    }
}

# show on screen
$thirdFile | Format-Table -AutoSize -Wrap

# export to file
$thirdFile | Export-Csv "Path\File3.csv" -NoTypeInformation