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

    }
}

Burt_Harris · Accepted Answer · 2016-09-27 00:59:21Z

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.

Michael Rudner Evanchik · Accepted Answer · 2016-09-26 21:25:04Z

1

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 "|"
    }
}

answered Sep 26, 2016 at 21:25

Michael Rudner Evanchik

8771 gold badge8 silver badges19 bronze badges

1 Comment

Lance U. Matthews Over a year ago

Once Import-Csv "hydrates" a line of CSV text into a PSObject instance, the order of the fields in the CSV is lost. Neither the PSObject instance ($line) nor the Get-Member cmdlet have any way of knowing the original order of the fields.

Collectives™ on Stack Overflow

Powershell loop csv file export csv line one at a time

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related