如何使用PowerShell根据一个CSV文件中同一行中包含多个值的单个单元格将一行分为多行 [英] How to separate one row into multiple rows based on a single cell containing multiple values in the same row in a CSV file using PowerShell

问题描述

我正在尝试使用PowerShell在CSV文件的每一行中循环，以基于定界符（;）将行中的特定单元格分成多行。我的数据将如下所示：

I am attempting to loop through each row of a CSV file using PowerShell to separate out a specific cell in the row into multiple rows based on a delimiter (;). My data will look something like this:

ID Name Invoices       Member Since 
1  John A123;A234;C316 1999 
2  Dave B219;C216      2010 
3  Jim  D213;E117;G214 2003

我需要输出看起来像这样：

And I would need the output to look like this:

ID Name Invoices Member Since 
1  John A123     1999 
1  John A234     1999 
1  John C316     1999 
2  Dave B219     2010 
2  Dave C216     2010 
3  Jim  D213     2003 
3  Jim  E117     2003
3  Jim  G214     2003

最后，最后，我需要将此文件导出到新的CSV文件中文件。任何帮助将不胜感激。我已经研究了几个小时，并且在寻找有关如何执行此操作的资源方面遇到很多困难。预先感谢您的帮助。

Finally, at the end, I would need to export this file to a new CSV file. Any help would be greatly appreciated. I have been researching this for a few hours and am having a lot of difficulties in finding resources on how to do this. Thanks in advance for your help.

推荐答案

只需对每一行进行迭代，即可检索发票，将其除以; 并使用当前值为每个创建一个新的 PSCustomObject ：

Just iterate over each row, retrieve the Invoices, split it by ; and create a new PSCustomObject for each using the current values:

$x = Get-Content 'Path_to_your_csv'
$y = $x | ConvertFrom-Csv -Delimiter ' '
$y | Foreach {
    $current = $_
    $current.Invoices -split ';' | foreach  {
        [PSCUstomObject]@{
            ID = $current.ID
            Name  = $current.Name 
            Invoices  = $_
            Member  = $current.Member
            Since  = $current.Since
        }
    }
} | ConvertTo-Csv -NoTypeInformation -Delimiter ' ' | % {$_ -replace '"',''}

输出：

ID Name Invoices Member Since
1 John A123 1999 
1 John A234 1999 
1 John C316 1999 
2 Dave B219 2010 
2 Dave C216 2010 
3 Jim D213 2003 
3 Jim E117 2003 
3 Jim G214 2003 

注意：您可能想采用定界符（例如，使用Tabs）...

Note: You probably want to adopt the delimiter (e. g. using Tabs)...

这篇关于如何使用PowerShell根据一个CSV文件中同一行中包含多个值的单个单元格将一行分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！