合并两个CSV，然后对输出中的列进行重新排序 [英] Merging two CSVs and then re-ordering columns on output

问题描述

我有第一个CSV：

Server,Info  
server1,item1
server1,item1

和第二个CSV：

Server,Info  
server2,item2
server2,item2

我正在尝试获得以下输出：

And I am trying to get this output:

Server,Server,Info,Info
server1,server2,item1,item2
server1,server2,item1,item2

如您所见，问题在于2个CSV的标头具有相同的名称，如果我将它们解析为对象并循环遍历，则会导致问题键。

As you see, the problem is that the headers of the 2 CSV have the same names, which cause a problem if I parse them into objects and loop over keys.

所以我试图合并它们，然后将它们重新排序为字符串，但是我不知道如何在最后一个中做到这一点。 for 循环：

So I am trying to merge them then reordering them as strings, but my mind can't figure how to do it in the last for loop:

$file1 = Get-Content ".\Powershell test\A.csv"
$file2 = Get-Content ".\Powershell test\B.csv"

$content = for ($i = 0; $i -lt $file1.Length; $i++) {
    '{0},{1}' -f $file1[$i].Trim(), $file2[$i].Trim()
}

$content | Out-File  ".\Powershell test\merged.csv"

$firstFileParsed = Import-Csv -Path ".\Powershell test\B.csv"
$secondFileParsed = Import-Csv -Path ".\Powershell test\B.csv"

$secondFilePath =  ".\Powershell test\B.csv"
$contentOf2ndFile = Get-Content $secondFilePath

$csvColumnNames = (Get-Content '.\Powershell test\B.csv' |
                  Select-Object -First 1).Split(",")

$newColumns = @()

foreach($header in $csvColumnNames) {
    $newColumns += $header
}

$newColumns = $newColumns -join ","
$contentOf2ndFile[0] = $newColumns
$contentOf2ndFile | Out-File ".\Powershell test\temp.csv"

$tempObject = Import-Csv -Path ".\Powershell test\temp.csv"
$tempFile = Get-Content ".\Powershell test\temp.csv"

$array = @()
$tempArr = @()

for ($i = 0; $i -lt $file1.Length; $i++) {
    $tempArr1 = $file1[$i] -split ","
    $tempArr2 = $tempFile[$i] -split ","

    for ($j = 0; $j -lt $tempArr1.Length; $j++) {
        $tempArr += $tempArr1[$j] +  "," + $tempArr2[$j]
        $tempArr
    }

    $array += $tempArr
}

$array | Out-File '.\Powershell test\merged.csv'

推荐答案

您建议的内容不是很有用，甚至不是有效的CSV。恕我直言，只有两个结果才有意义：

What you suggest is not very useful or even valid CSV. IMHO only two results would make sense:

此：

Server1,Info1,Server2,Info2
server1,item1,server2,item2
server1,item1,server2,item2

或者这个：

Server,Info
server1,item1
server1,item1
server2,item2
server2,item2

第一种方法：

$csv1 = Import-Csv ".\Powershell test\A.csv"
$csv2 = Import-Csv ".\Powershell test\B.csv"

$merged = for($i = 0; $i -lt $csv1.Count; $i++) {
    $new = new-object psobject
    $entry1 = $csv1[$i]
    $entry1 | Get-Member -Type NoteProperty | foreach {
        Add-Member -InputObject $new -MemberType NoteProperty -Name ($_.Name + "1") -Value $entry1.($_.Name)
    }
    $entry2 = $csv2[$i]
    $entry2 | Get-Member -Type NoteProperty | foreach {
        Add-Member -InputObject $new -MemberType NoteProperty -Name ($_.Name + "2") -Value $entry2.($_.Name)
    }
    $new
}

$merged | Export-Csv ".\Powershell test\merged.csv"

第二种方法：

$csv1 = Import-Csv ".\Powershell test\A.csv"
$csv2 = Import-Csv ".\Powershell test\B.csv"

$merged = $csv1 + $csv2

$merged | Export-Csv ".\Powershell test\merged.csv"

更新

如果您确实要输出（并且文件肯定具有相同的标题和行数），则可以先使用唯一的标题，然后简单地重命名他们以后：

If you want exactly your output (and the files are certain to have the same headers and line count), you could use unique headers first, and then simply rename them later:

$csv1 = Import-Csv ".\Powershell test\A.csv"
$csv2 = Import-Csv ".\Powershell test\B.csv"
$merged = for($i = 0; $i -lt $csv1.Count; $i++) {
    $new = New-Object PSObject
    ("Server", "Info") | foreach {
        Add-Member -InputObject $new -MemberType NoteProperty -Name ($_ + "1") -Value $csv1[$i].$_
        Add-Member -InputObject $new -MemberType NoteProperty -Name ($_ + "2") -Value $csv2[$i].$_
    }
    $new
}
$header = $true
$merged | ConvertTo-Csv -NoTypeInformation | foreach {
    if ($header) {
        $header = $false
        # remove the numbers from the headers
        $_ -replace "\d", ""
    }
    else { $_ }
} | Out-File ".\Powershell test\merged.csv"

说明：

Count 在Powershell中可用于所有集合，并且比 Length更安全仅是数组的属性。

Count is available in Powershell for all collections, and safer than Length which is a property of arrays only. But in this case, both should work.

在循环中，将创建一个新的空对象（使用 New-Object ），然后通过添加已解析的CSV对象的成员（带有 Add-Member ）进行填充。

In the loop, a new empty object is created (with New-Object) and then populated by adding the members of the parsed CSV objects (with Add-Member). A counter is added to the property names to make them unique.

这些对象的集合（ $ merged ）然后将其转换为CSV，删除标题行中的数字，并将所有内容保存到文件中。

The collection of these objects ($merged) is then converted to CSV, the numbers in the header line removed, and everything saved to file.

这篇关于合并两个CSV，然后对输出中的列进行重新排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！