Powershell loop csv file export csv line one at a time

Question

I'm trying to loop a pipe delimited file, check if the line has 28 columns and just export that line to the file. This is the primary question and answer I'm looking for (I have researched a lot, but I'm new to PS and so many different ways I need some help). The following works but there are two issues. The export file will not let me choose pipe delimited, AND the output goes in alphabetical format by field name, not by ordinal.

Also, is there a way I can make the output not "text" qualified?

$path = Get-ChildItem c:\temp\*.txt

$staticPath = '0859'
$year = Get-Date -format yy
$month = Get-Date -format MM
$day = Get-Date -format dd
$output =  'c:\temp\' + $year + $month + $day + $staticPath + '.TXT'
$outputbad = 'c:\temp\BAD' + $year + $month + $day + $staticPath + '.TXT'



$input = 'c:\temp\' + $path.Name
$input

$csv = Import-Csv -path $input -Delimiter "|"

foreach($line in $csv)
{ 

$properties = $line | Get-Member -MemberType Properties
$row = ''
$properties.Count
$obj = new-object PSObject

for($i=0; $i -lt $properties.Count;$i++)
{

    $column = $properties[$i]
    $columnvalue = $line | Select -ExpandProperty $column.Name
    #$row += $columnvalue
    $obj | add-member -membertype NoteProperty -name $column.Name -value     $columnvalue
    }


    if($properties.Count -eq 28)
    {
        $obj | export-csv -Path $output -Append  -NoTypeInformation
        #$row | Export-Csv -Path $output -Append -Delimiter "|" -NoTypeInformation

    }
    else
    {
        $obj | Export-Csv  -Path $outputbad -Append -Delimiter "|" -NoTypeInformation

    }
} 

Answer 1

If you want to avoid any chance of changing the formatting of these lines files, perhaps you don't want to use the -CSV commands. Export-csv can add quotation marks etc. Here's different way that might do what you want:

$path | ForEach-Object { 
    $good = @()
    $bad = @()
    Get-Content $_ | ForEach-Object { 
        if (($value = $_ -split '\|').length -eq 28) { 
            $good += $_ 
        } else { 
            $bad += $_ 
        }
    }
    if ($good) { Out-File -Append -InputObject $good $output }
    if ($bad) { Out-File -Append -InputObject $bad $outputbad }
}

Note however that this will count quoted values containing a pipe differently than import-csv. Pipe separated values are sometimes generated without any quoting logic.

The $values variable will be an array of individual columns, so if you want to write some code to fix them up inside the if, you can use that then join them back up with $good += $values -join '|', or perhaps use another regex to fix errors.

Answer 2

silly me, here is the answer. But if somebody could answer why the PSObject or the properties loop is alphabetical it would be helpful. Soon i would like to add intelligence to this to check ordinal (not alphabetic field name order) a field if its a integer or not, then i know how to fix the BAD record.

$path = Get-ChildItem c:\temp\*.txt

$staticPath = '0859'
$year = Get-Date -format yy
$month = Get-Date -format MM
$day = Get-Date -format dd
$output =  'c:\temp\' + $year + $month + $day + $staticPath + '.TXT'
$outputbad = 'c:\temp\BAD' + $year + $month + $day + $staticPath + '.TXT'

$input = 'c:\temp\' + $path.Name

$csv = Import-Csv -path $input -Delimiter "|"

foreach($line in $csv)
{ 
    $properties = $line | Get-Member -MemberType Properties

    if($properties.Count -eq 28)
    {
        $line | export-csv -Path $output -Append  -NoTypeInformation -Delimiter "|"
    }
    else
    {
        $line | Export-Csv  -Path $outputbad -Append -Delimiter "|"
    }
}