根据单个单元格的内容将一个CSV行分为多行 [英] separate one CSV row into multiple rows based on contents of a single cell

问题描述

我基于

I based my own solution on 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

尝试遍历一行，检索zip，将其拆分为''，然后使用zipcode为每一个创建一个新的PSCustomObject，然后将zipcode放入同一csv中带有新行的列中。

Trying to iterate over one row, retrieve the zips, split it by ' ' and create a new PSCustomObject for each using the zipcodes and place the zipcode in the column with new rows in the same csv.

样本（是，敞篷=无价值）

Sample (yes, hood = no value)

City,State,hood,zip,lat,lng
Carmel,IN,,"46290 46032 46033 46280 46082",39.9783711,-86.1180435

我的非工作解决方案是一个修改链接中的解决方案

My non working solution is a modification of the solution in the link sited

$x = Get-Content 'D:\Carmel-IN.csv'
$y = $x | ConvertFrom-Csv -Delimiter ' '
$y | Foreach {
    $current = $_
    $current.zip -split ' ' | foreach  {
        [PSCUstomObject]@{ 
            zip  = $_
        }
    }
} | ConvertTo-Csv -NoTypeInformation -Delimiter ' ' | % {
    $_ -replace '"',''
}

在ISE和控制台上的输出是相同的...一个单词 zip 却没有任何内容写入文件。

The output is the same on ISE and console ... the one word zip and nothing is written to file.

我对PS非常陌生，使用5.1。我感觉校正非常简单，但我看不到。寻找结果文件为...

I am very new to PS, using 5.1. I have a feeling the correction is very simple but I cant see it. Looking for a result file as ...

City,State,hood,zip,lat,lng
Carmel,IN,,"46290 46032 46033 46280 46082",39.9783711,-86.1180435
,,,46290,,
,,,46032,,
,,,46033,,
,,,46280,,
,,,46082,,

更新：

我设法重新编写脚本以获得可以使用的结果

I managed to rework the script to get result I can use

$x = Get-Content 'D:\temp\Carmel-IN.csv'
$exportlocation = 'D:\temp\Carmel-IN.csv'
$y = $x | ConvertFrom-Csv -Delimiter ','
$y | Foreach {
    $current = $_
    $current.zip -split ' ' | foreach  {
        [PSCUstomObject]@{ 
            City  = $null
            State = $null
            hood  = $null
            zip   = $_
            lat   = $null
            lng   = $null
        }
    } 
} | Export-CSV -Append -NoTypeInformation -Delimiter ',' -Force $exportlocation  | % { $_ -replace '"',''}

，结果为

City,State,hood,zip,lat,lng
Carmel,IN,,"46290 46032 46033 46280 46082",39.9783711,-86.1180435
,,,"46290",,
,,,"46032",,
,,,"46033",,
,,,"46280",,
,,,"46082",,

我要关心另一个脚本中的多余

I am going to care of the extra "'s in another script

| ％{$ _ -replace''，''} 似乎并没有将它们删除

The | % { $_ -replace '"',''} doesn't seem to be removing them

感谢所有建议

推荐答案

使用选择对象：

$x = Get-Content 'D:\Carmel-IN.csv'
$y = $x | ConvertFrom-Csv -Delimiter ' '
foreach($row in $y){
  foreach($zip in -split $row.zip){
    $row |select *,@{Name='zip';Expression={$zip}} -ExcludeProperty zip
  }
}

如果需要的话要保留列的顺序，请设置要在循环之前选择的属性的属性列表：

Alternatively, if you want to preserve the order of columns, set up the property list of properties to select prior to the loop:

$columns = $y[0].psobject.Properties.Name |ForEach-Object {
    if($_ -eq 'zip'){
        $_ = @{Name = 'zip';Expression = { -split $zip }}
    }
    $_
}
foreach($row in $y){
  foreach($zip in -split $row.Zip){
    $row |select $columns
  }
}

这篇关于根据单个单元格的内容将一个CSV行分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！