CODEWITHSUNDEEP
powershellcsv
Keith
Keith

Reputation: 2019

How to delete date-based lines from files using PowerShell

I have some CSV files where I need to delete all lines that contain a date that is greater than a specified date. How would I do this in PowerShell?

BTW: here is the date format: 09/29/2011

Example: I would want to delete all lines that contain the date greater than 09/29/2011.

Upvotes: 0

Views: 2209

Answers (5)

PersianGulf
PersianGulf

Reputation: 2935

I wrote a script for you morning that do delete every line has pattern you specify. You should run script such as:

myscruipt.sh YOURDATYE YOURCSVFILE

myscript.sh:

#!/bin/bash
    declare -a  num
    num=`egrep -n "$1" yahoo_ab.csv |awk 'BEGIN{FS=":";}{for (i=0 ; i<NF ; i++) print $1; } '`
    while true; do 
        for i in $num ; do 
            sed -i "$i d" $2 ;
        done;
        egrep $1 $2;
        if [ $? = 1 ]; then break; fi;
    done;

Upvotes: -1

Joey
Joey

Reputation: 354476

Ok, it seems like there is only one thing that looks like a date in that line, anyway, so we can just filter for that:

Get-ChildItem *.csv | # adapt if necessary
  ForEach-Object {
    (Get-Content $_) | # the parentheses are important so the entire file is read at once
      Where-Object { # now we process the file line by line
        # find the date                       ↓ suppress the boolean output
        $_ -match '\|(\d{2}/\d{2}/\d{4})\|' | Out-Null

        # this only works if every line contains a date. Hopefully it does.
        $date = [DateTime]($Matches[1])

        # Finally the comparison we wanted in the first place
        # This is the condition for all lines that are *retained* (hence less than)
        $date -lt '09/29/2011'
      } | Out-File $_ # use -Encoding ASCII/UTF8/Unicode depending on your needs.
                      # Maybe ASCII is enough
  }

or shorter:

gci *.csv | % {
  (gc $_) |
    ? {
      $null = $_ -match '\|(\d{2}/\d{2}/\d{4})\|'
      [DateTime]$Matches[1] -lt '09/29/2011'
    } |
    Out-File $_
}

Upvotes: 1

mjolinor
mjolinor

Reputation: 68263

 foreach ($file in gci *.csv){
   (gc $file) |
     ? {[datetime]$_.split('|')[1] -lt '09/29/2011'
     } | set-content $file

 }

Assuming that's a pipe-delimited file.

Upvotes: 2

David Brabant
David Brabant

Reputation: 43499

I favored clarity over conciseness:

param (
    [parameter(Mandatory = $true)] [string] $csvFileName,
    [parameter(Mandatory = $true)] [datetime] $date
)

try
{
    $Error.Clear()

    if (!(Test-Path $csvFileName))
        { throw "Could not find file $csvFileName" }

    $newContent = Get-Content $csvFileName |    ?{
        ([regex]::matches($_, "[0-9]{2}/[0-9]{2}/[0-9]{4}") | %{[DateTime] $_.value -lt $date})
    } 

    $newContent | Set-Content $csvFileName
}

catch
{
    Write-Host "$($MyInvocation.InvocationName): $_"
}

Upvotes: 1

CB.
CB.

Reputation: 60908

You need to create a new cleaned csv file:

supposing this is you csv:

col1,date,col3
aaaaa,05/05/2010,rwer
bdfdfg,06/29/2011,reewr
dsfsdf,08/05/2012,dsfsd

do like this:

import-csv .\myoriginal.csv -delimiter '|' | ? { [datetime]$_.date -ge [datetime]"09/29/2011"} | Export-Csv -NoTypeInformation -Path .\mycleaned.csv -delimiter '|'

then you can delete original csv with

remove-item .\myoriginal.csv

Upvotes: 0

Related Questions