CODEWITHSUNDEEP
powershellcsv
pyTh0n-M4nning
pyTh0n-M4nning

Reputation: 77

How to insert empty rows in between in a CSV file with PowerShell?

CSV-File:

DateColumn;ValueColumn
<Date1>;<Value1>
<Date2>;<Value2>
<Date3>;<Value3>
...

What I want to do is to compare the day of dateX with the day of dateX+1 and if they are not equal I want to add a blank row in between these two dates. How do I do this add blank row thing?

Code:

$content = Import-Csv -LiteralPath 'C:\test.csv' -Delimiter ';'
$counter = 1
$dateOne = $null
$dateTwo = $null

foreach ($row in $content) {
    if ($counter -gt 1) {
        $dateTwo = $row.DateColumn -as [DateTime]
        if ($dateOne.Day -ne $dateTwo.Day) {
            "`n" + $row | Add-Content 'C:\test.csv' | Set-Content 'C:\test.csv'
            $dateOne = $dateTwo
        }
    } else {
        $dateOne = $row.DateColumn -as [DateTime]
    }
    $counter = $counter + 1
}

The output I get is something like this in the CSV file:

@{DateColumn=29.01.2004 12:29, ValueColumn=2,2}

@{DateColumn=30.01.2004 12:29, ValueColumn=2,5}

@{DateColumn=31.01.2004 12:29, ValueColumn=2,8}

And it appends it to the existing data, doesn't overwrite. Any suggestions?

Desired output:

29.01.2004 12:29       2,2

30.01.2004 12:29       2,5

31.01.2004 12:29       2,8

Upvotes: 1

Views: 1864

Answers (2)

Esperento57
Esperento57

Reputation: 17472

other solution, with select-string and his context option

select-string -Path "C:\temp\New.txt" -Pattern "." -Context 1, 0 | %{
if ($_.Context.PreContext[0] -like "DateColumn*")
{
$_.line
}

elseif ($_.line -notlike "DateColumn*" -and ($_.line -split ";")[0] -ne ($_.Context.PreContext[0] -split ";")[0])
{
  $_.Line ; ";"
}
else
{
$_.Line 
}
} | Set-Content "C:\temp\New2.txt"

Upvotes: 0

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

You're thinking too complicated. Simply do this:

$prev    = $null
$current = $null
Get-Content 'C:\path\to\input.csv' | Select-Object -Skip 1 | ForEach-Object {
    # extract date from current record
    $current = ($_ -split ';')[0] -as [DateTime]

    # print empty line if date changed from previous line
    if ($prev -and $prev.Date -ne $current.Date) { "`n" }

    # print current line
    $_ -replace ';', "`t"

    # remember date from this iteration in the next iteration
    $prev = $current
} | Set-Content 'C:\path\to\output.txt'

Upvotes: 2

Related Questions