时间戳

首页 > 解决方案 > 为什么读取 .CSV 文件时 ForEach-object 循环停止工作

powershell - 为什么读取 .CSV 文件时 ForEach-object 循环停止工作

问题描述

更新：

我做了建议的更改。我已经切换到 switch 语句。

$csv = import-csv C:\TEST\Test_Data.csv 

$rowcount = 1

ForEach ($line in $CSV) {
   
   $rowcount++
   
   $Store = $line.Store
   $sheet.Cells.Item($rowcount,"A") = $Store

   $StoreNUM = $line.StoreNumber
   $sheet.Cells.Item($rowcount,"B") = $StoreNUM
  
   $COLUMNA = $line.A
   # use that switch statement!
    switch ($COLUMNA) {
        # Find "Prepared With:"
        {$_ -like "*Prepared With:*"}{  
            $sheet.Cells.Item($rowcount,"E") = $COLUMNA
        }
        # Find "Contains"
        {$_ -like "*Contains:*"}{ 
            $sheet.Cells.Item($rowcount,"F") = $COLUMNA
        }
        # You can do -match, it can be a little faster
        {$_ -like "Net Wt."}{  
            $sheet.Cells.Item($rowcount,"G") = $COLUMNA
        }

    } #end Switch

   $COLUMNB = $line.B
   # use that switch statement!
    switch ($COLUMNB) {
        # Find "Prepared With:"
        {$_ -like "*Prepared With:*"}{  
            $sheet.Cells.Item($rowcount,"E") = $COLUMNB
        }
        # Find "Contains"
        {$_ -like "*Contains:*"}{ 
            $sheet.Cells.Item($rowcount,"F") = $COLUMNB
        }
        # You can do -match, it can be a little faster
        {$_ -like "Net Wt."}{  
            $sheet.Cells.Item($rowcount,"G") = $COLUMNB
        }

    } #end Switch

   $COLUMNC = $line.C
   # use that switch statement!
    switch ($COLUMNC) {
        # Find "Prepared With:"
        {$_ -like "*Prepared With:*"}{  
            $sheet.Cells.Item($rowcount,"E") = $COLUMNC
        }
        # Find "Contains"
        {$_ -like "*Contains:*"}{ 
            $sheet.Cells.Item($rowcount,"F") = $COLUMNC
        }
        # You can do -match, it can be a little faster
        {$_ -like "Net Wt."}{  
            $sheet.Cells.Item($rowcount,"G") = $COLUMNC
        }

    } #end Switch

   $COLUMND = $line.D
   # use that switch statement!
    switch ($COLUMND) {
        # Find "Prepared With:"
        {$_ -like "*Prepared With:*"}{  
            $sheet.Cells.Item($rowcount,"E") = $COLUMND
        }
        # Find "Contains"
        {$_ -like "*Contains:*"}{ 
            $sheet.Cells.Item($rowcount,"F") = $COLUMND
        }
        # You can do -match, it can be a little faster
        {$_ -like "Net Wt."}{  
            $sheet.Cells.Item($rowcount,"G") = $COLUMND
        }

    } #end Switch

   $COLUMNE = $line.E
   # use that switch statement!
    switch ($COLUMNE) {
        # Find "Prepared With:"
        {$_ -like "*Prepared With:*"}{  
            $sheet.Cells.Item($rowcount,"E") = $COLUMNE
        }
        # Find "Contains"
        {$_ -like "*Contains:*"}{ 
            $sheet.Cells.Item($rowcount,"F") = $COLUMNE
        }
        # You can do -match, it can be a little faster
        {$_ -like "Net Wt."}{  
            $sheet.Cells.Item($rowcount,"G") = $COLUMNE
        }

    } #end Switch

   $COLUMNF = $line.F
   # use that switch statement!
    switch ($COLUMNF) {
        # Find "Prepared With:"
        {$_ -like "*Prepared With:*"}{  
            $sheet.Cells.Item($rowcount,"E") = $COLUMNF
        }
        # Find "Contains"
        {$_ -like "*Contains:*"}{ 
            $sheet.Cells.Item($rowcount,"F") = $COLUMNF
        }
        # You can do -match, it can be a little faster
        {$_ -like "Net Wt"}{  
            $sheet.Cells.Item($rowcount,"G") = $COLUMNF
        }

    } #end Switch

} #end Foreach

但是我在源 CSV 中仍然有一个问题，它没有超过第 8 列数据。我的数据跨越 17 列。我不明白为什么它停止在列中运行。我知道没有空列。到目前为止，您的所有帮助都非常感谢。

对为什么我的 ForEach 循环停止工作感到困惑。一切正常，直到####Evaluate Column E####它到达 E 停止搜索。无论我在 CSV 中放置什么数据，它都会被完全忽略。如果我将数据放在 A - D 列中，它就可以正常工作。

可能有一种更简单的方法来完成我想要完成的事情。

$csv = import-csv C:\TEST\Test_Data.csv 
$csv | foreach-object {
    
  $ItemName = $_.Filename
  $Store = $_.Store
  $StoreNUM =$_.StoreNumber
  $Department =$_.Department 
  $COLUMNA = $_.A
  $COLUMNB = $_.B
  $COLUMNC = $_.C
  $COLUMND = $_.D
  $COLUMNE = $_.E
  $COLUMNF = $_.F
  $COLUMNG = $_.G
  $COLUMNH = $_.H

}
$csv | ForEach-Object {$rowcount = 2}{

   $Store = $_.Store
   $sheet.Cells.Item($rowcount,"A") = $Store

   $StoreNUM = $_.StoreNumber
   $sheet.Cells.Item($rowcount,"B") = $StoreNUM

   $Department = $_.Department
   $sheet.Cells.Item($rowcount,"C") = $Department

   $ItemName = $_.Filename
   $sheet.Cells.Item($rowcount,"D") = $ItemName


####Evaluate Column A####
   $COLUMNA = $_.A

   #Find Prepared With:
   if($COLUMNA -like "*Prepared*"){
        $sheet.Cells.Item($rowcount,"E") = $COLUMNA
   }

   #Find Contains:
   elseif($COLUMNA -like "*Contains:*"){
        $sheet.Cells.Item($rowcount,"F") = $COLUMNA
   }
   
   #Find Net Weight:
   elseif($COLUMNA -like "*Net Wt.:*"){
        $sheet.Cells.Item($rowcount,"G") = $COLUMNA
   }

   #Find OLD UPC:
   elseif($COLUMNA -like "*OLD*UPC*"){
        $sheet.Cells.Item($rowcount,"H") = $COLUMNA
   }

   #Find NEW UPC:
   elseif($COLUMNA -like "*NEW*UPC*"){
        $sheet.Cells.Item($rowcount,"I") = $COLUMNA
   }

   #Find UPC:
   elseif($COLUMNA -like "*UPC*"){
        $sheet.Cells.Item($rowcount,"J") = $COLUMNA
   }

   #Find Label Type
   elseif($COLUMNA -clike "*LABEL*"){
        $sheet.Cells.Item($rowcount,"L") = $COLUMNA
   }

   #Find Section
   elseif($COLUMNA -like "*Section:*"){
        $sheet.Cells.Item($rowcount,"L") = $COLUMNA
   }

   #Find Retail
   elseif($COLUMNA -like "*Retail:*"){
        $sheet.Cells.Item($rowcount,"M") = $COLUMNA
   }

   #Find Cost
   elseif($COLUMNA -like "*Cost:*"){
        $sheet.Cells.Item($rowcount,"N") = $COLUMNA
   }
   
   #Find Requested By
   elseif($COLUMNA -like "*Requested by:*"){
        $sheet.Cells.Item($rowcount,"O") = $COLUMNA
   }
   
   else{
        $sheet.Cells.Item($rowcount,"Q") = $COLUMNA
   }

####Evaluate Column B#####
   $COLUMNB = $_.B

   #Find Prepared With:
   if($COLUMNB -like "*Prepared*"){
        $sheet.Cells.Item($rowcount,"E") = $COLUMNB
   }

   #Find Contains:
   elseif($COLUMNB -like "*Contains:*"){
        $sheet.Cells.Item($rowcount,"F") = $COLUMNB
   }
   
   #Find Net Weight:
   elseif($COLUMNB -like "*Net Wt.:*"){
        $sheet.Cells.Item($rowcount,"G") = $COLUMNB
   }

   #Find OLD UPC:
   elseif($COLUMNB -like "*OLD*UPC*"){
        $sheet.Cells.Item($rowcount,"H") = $COLUMNB
   }

   #Find NEW UPC:
   elseif($COLUMNB -like "*NEW*UPC*"){
        $sheet.Cells.Item($rowcount,"I") = $COLUMNB
   }

   #Find UPC:
   elseif($COLUMNB -like "*UPC*"){
        $sheet.Cells.Item($rowcount,"J") = $COLUMNB
   }

   #Find Label Type
   elseif($COLUMNB -clike "*LABEL*"){
        $sheet.Cells.Item($rowcount,"L") = $COLUMNB
   }

   #Find Section
   elseif($COLUMNB -like "*Section:*"){
        $sheet.Cells.Item($rowcount,"L") = $COLUMNB
   }

   #Find Retail
   elseif($COLUMNB -like "*Retail:*"){
        $sheet.Cells.Item($rowcount,"M") = $COLUMNB
   }

   #Find Cost
   elseif($COLUMNB -like "*Cost:*"){
        $sheet.Cells.Item($rowcount,"N") = $COLUMNB
   }
   
   #Find Requested By
   elseif($COLUMNB -like "*Requested by:*"){
        $sheet.Cells.Item($rowcount,"O") = $COLUMNB
   }
   
   else{
        $sheet.Cells.Item($rowcount,"R") = $COLUMNB
   }

####Evaluate Column C####
   $COLUMNC = $_.C

   #Find Prepared With:
   if($COLUMNC -like "*Prepared*"){
        $sheet.Cells.Item($rowcount,"E") = $COLUMNC
   }

   #Find Contains:
   elseif($COLUMNC -like "*Contains:*"){
        $sheet.Cells.Item($rowcount,"F") = $COLUMNC
   }
   
   #Find Net Weight:
   elseif($COLUMNC -like "*Net Wt.:*"){
        $sheet.Cells.Item($rowcount,"G") = $COLUMNC
   }

   #Find OLD UPC:
   elseif($COLUMNC -like "*OLD*UPC*"){
        $sheet.Cells.Item($rowcount,"H") = $COLUMNC
   }

   #Find NEW UPC:
   elseif($COLUMNC -like "*NEW*UPC*"){
        $sheet.Cells.Item($rowcount,"I") = $COLUMNC
   }

   #Find UPC:
   elseif($COLUMNC -like "*UPC*"){
        $sheet.Cells.Item($rowcount,"J") = $COLUMNC
   }

   #Find Label Type
   elseif($COLUMNC -clike "*LABEL*"){
        $sheet.Cells.Item($rowcount,"L") = $COLUMNC
   }

   #Find Section
   elseif($COLUMNC -like "*Section:*"){
        $sheet.Cells.Item($rowcount,"L") = $COLUMNC
   }

   #Find Retail
   elseif($COLUMNC -like "*Retail:*"){
        $sheet.Cells.Item($rowcount,"M") = $COLUMNC
   }

   #Find Cost
   elseif($COLUMNC -like "*Cost:*"){
        $sheet.Cells.Item($rowcount,"N") = $COLUMNC
   }
   
   #Find Requested By
   elseif($COLUMNC -like "*Requested by:*"){
        $sheet.Cells.Item($rowcount,"O") = $COLUMNC
   }
   
   else{
        $sheet.Cells.Item($rowcount,"S") = $COLUMNC
   }

####Evaluate Column D####
   $COLUMND = $_.D

   #Find Prepared With:
   if($COLUMND -like "*Prepared*"){
        $sheet.Cells.Item($rowcount,"E") = $COLUMND
   }

   #Find Contains:
   elseif($COLUMND -like "*Contains:*"){
        $sheet.Cells.Item($rowcount,"F") = $COLUMND
   }
   
   #Find Net Weight:
   elseif($COLUMND -like "*Net Wt.:*"){
        $sheet.Cells.Item($rowcount,"G") = $COLUMND
   }

   #Find OLD UPC:
   elseif($COLUMND -like "*OLD*UPC*"){
        $sheet.Cells.Item($rowcount,"H") = $COLUMND
   }

   #Find NEW UPC:
   elseif($COLUMND -like "*NEW*UPC*"){
        $sheet.Cells.Item($rowcount,"I") = $COLUMND
   }

   #Find UPC:
   elseif($COLUMND -like "*UPC*"){
        $sheet.Cells.Item($rowcount,"J") = $COLUMND
   }

   #Find Label Type
   elseif($COLUMND -clike "*LABEL*"){
        $sheet.Cells.Item($rowcount,"L") = $COLUMND
   }

   #Find Section
   elseif($COLUMND -like "*Section:*"){
        $sheet.Cells.Item($rowcount,"L") = $COLUMND
   }

   #Find Retail
   elseif($COLUMND -like "*Retail:*"){
        $sheet.Cells.Item($rowcount,"M") = $COLUMND
   }

   #Find Cost
   elseif($COLUMND -like "*Cost:*"){
        $sheet.Cells.Item($rowcount,"N") = $COLUMND
   }
   
   #Find Requested By
   elseif($COLUMND -like "*Requested by:*"){
        $sheet.Cells.Item($rowcount,"O") = $COLUMND
   }
   
   else{
        $sheet.Cells.Item($rowcount,"T") = $COLUMND
   }

####Evaluate Column E####
   $COLUMNE = $_.E

   #Find Prepared With:
   if($COLUMNE -like "*Prepared*"){
        $sheet.Cells.Item($rowcount,"E") = $COLUMNE
   }

   #Find Contains:
   if($COLUMNE -like "*Contains:*"){
        $sheet.Cells.Item($rowcount,"F") = $COLUMNE
   }
   
   #Find Net Weight:
   elseif($COLUMNE -like "*Net Wt.:*"){
        $sheet.Cells.Item($rowcount,"G") = $COLUMNE
   }

   #Find OLD UPC:
   elseif($COLUMNE -like "*OLD*UPC*"){
        $sheet.Cells.Item($rowcount,"H") = $COLUMNE
   }

   #Find NEW UPC:
   elseif($COLUMNE -like "*NEW*UPC*"){
        $sheet.Cells.Item($rowcount,"I") = $COLUMNE
   }

   #Find UPC:
   elseif($COLUMNE -like "*UPC*"){
        $sheet.Cells.Item($rowcount,"J") = $COLUMNE
   }

   #Find Label Type
   elseif($COLUMNE -clike "*LABEL*"){
        $sheet.Cells.Item($rowcount,"L") = $COLUMNE
   }


   #Find Section
   elseif($COLUMNE -like "*Section:*"){
        $sheet.Cells.Item($rowcount,"L") = $COLUMNE
   }

   #Find Retail
   elseif($COLUMNE -like "*Retail:*"){
        $sheet.Cells.Item($rowcount,"M") = $COLUMNE
   }

   #Find Cost
   elseif($COLUMNE -like "*Cost:*"){
        $sheet.Cells.Item($rowcount,"N") = $COLUMNE
   }
   
   #Find Requested By
   elseif($COLUMNE -like "*Requested by:*"){
        $sheet.Cells.Item($rowcount,"O") = $COLUMNE
   }
   
   else{
        $sheet.Cells.Item($rowcount,"U") = $COLUMNE
   } 

####Evaluate Column F####
   $COLUMNF = $_.F

   #Find Prepared With:
   if($COLUMNF -like "*Prepared*"){
        $sheet.Cells.Item($rowcount,"E") = $COLUMNF
   }

   #Find Contains:
   elseif($COLUMNF -like "*Contains:*"){
        $sheet.Cells.Item($rowcount,"F") = $COLUMNF
   }
   
   #Find Net Weight:
   elseif($COLUMNF -like "*Net Wt.:*"){
        $sheet.Cells.Item($rowcount,"G") = $COLUMNF
   }

   #Find OLD UPC:
   elseif($COLUMNF -like "*OLD*UPC*"){
        $sheet.Cells.Item($rowcount,"H") = $COLUMNF
   }

   #Find NEW UPC:
   elseif($COLUMNF -like "*NEW*UPC*"){
        $sheet.Cells.Item($rowcount,"I") = $COLUMNF
   }

   #Find UPC:
   elseif($COLUMNF -like "*UPC*"){
        $sheet.Cells.Item($rowcount,"J") = $COLUMNF
   }

   #Find Label Type
   elseif($COLUMNF -clike "*LABEL*"){
        $sheet.Cells.Item($rowcount,"L") = $COLUMNF
   }


   #Find Section
   elseif($COLUMNF -like "*Section:*"){
        $sheet.Cells.Item($rowcount,"L") = $COLUMNF
   }

   #Find Retail
   elseif($COLUMNF -like "*Retail:*"){
        $sheet.Cells.Item($rowcount,"M") = $COLUMNF
   }

   #Find Cost
   elseif($COLUMNF -like "*Cost:*"){
        $sheet.Cells.Item($rowcount,"N") = $COLUMNF
   }
   
   #Find Requested By
   elseif($COLUMNF -like "*Requested by:*"){
        $sheet.Cells.Item($rowcount,"O") = $COLUMNF
   }
   
   else{
        $sheet.Cells.Item($rowcount,"V") = $COLUMNF
   }


    #Increase Row Count
    $rowcount++

}

标签： powershell

解决方案

之前我回答说我以为你会提前结束循环，但我错了。我忘记了ForEach-Object同时支持-Begin和-End脚本块，这是您使用的。

例如，这是一个类似的命令，但包含了参数名称。

1..3 | ForEach-Object `
    -Begin {"starting..."; $baseVar = "ham"} `
    -Process {"$basevar + $_"} `
    -End {"all done"}

starting...
ham + 1
ham + 2
ham + 3
all done

这在 PowerShell 中受支持，但实际上从未使用过。我已经在企业级编写 PowerShell 十年了，它让我陷入了困境。我会警告不要这样做。

最后一件事，从风格上讲，您进行了如此多的 If 比较，以至于很难跟踪您的代码。我会像这样简化你的一些代码。

#Find Prepared With:
   if($COLUMNA -like "*Prepared*"){
        $sheet.Cells.Item($rowcount,"E") = $COLUMNA
   }

   #Find Contains:
   elseif($COLUMNA -like "*Contains:*"){
        $sheet.Cells.Item($rowcount,"F") = $COLUMNA
   }
   
   #Find Net Weight:
   elseif($COLUMNA -like "*Net Wt.:*"){
        $sheet.Cells.Item($rowcount,"G") = $COLUMNA
   }

   #Find OLD UPC:
   elseif($COLUMNA -like "*OLD*UPC*"){
        $sheet.Cells.Item($rowcount,"H") = $COLUMNA
   }

此代码将使用switch语句重写，如下所示。

switch ($COLUMNA)
{
    {$_ -like "*Prepared*"}{   #Find Prepared With:
        $sheet.Cells.Item($rowcount,"E") = $COLUMNA
    }
    {$_ -like "*Contains:*"}{  #Find Contains
        $sheet.Cells.Item($rowcount,"F") = $COLUMNA
    }
    {$_ -like "*Net Wt.:*"}{   #Find Net Weight:
        $sheet.Cells.Item($rowcount,"G") = $COLUMNA
    }
    {$_ -like "*OLD*UPC*"}{    #Find OLD UPC:
        $sheet.Cells.Item($rowcount,"H") = $COLUMNA
    }
}

一个更好的方法是将该代码移动到一个函数中，例如，Set-ColumnValue


Function Set-ColumnValue{ 
    Param
        ($rowcount,
         $cellInfo
        )

    switch ($cellInfo)
    {
        {$_ -like "*Prepared*"}{   #Find Prepared With:
            $sheet.Cells.Item($rowcount,"E") = $cellInfo
        }
        {$_ -like "*Contains:*"}{  #Find Contains
            $sheet.Cells.Item($rowcount,"F") = $cellInfo
        }
        {$_ -like "*Net Wt.:*"}{   #Find Net Weight:
            $sheet.Cells.Item($rowcount,"G") = $cellInfo
        }
        {$_ -like "*OLD*UPC*"}{    #Find OLD UPC:
            $sheet.Cells.Item($rowcount,"H") = $cellInfo
        }
    }
}

然后你会用Set-ColumnValue -rowCount $rowCount -CellInfo $columnA

编辑：以下内容不正确

就像@notjustme 暗示的那样，你forEach-object很早就结束了。

$csv | ForEach-Object {$rowcount = 2}{

   $Store = $_.Store

所以，具体来说，你就在这里结束

$csv | ForEach-Object {$rowcount = 2} <==== Ends the forEach

推荐阅读