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.
foreach ($file in gci *.csv){
(gc $file) |
? {[datetime]$_.split('|')[1] -lt '09/29/2011'
} | set-content $file
}
Assuming that's a pipe-delimited file.
>= specified date, and each CSV file needs to contain dates with a 1-week range. Thanks in advance!
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): $_"
}
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 $_
}
Bad argument to operator '-match': parsing "\|(\d{2}/\d{2}/\d{4}\)|" - Not enough )'s.. + $null = $_ -match <<<< '\|(\d{2}/\d{2}/\d{4}\)|' + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : BadOperatorArgument Cannot convert null to type "System.DateTime". + [DateTime]$Matches[ <<<< 1] -lt '09/29/2011' + CategoryInfo : NotSpecified: (:) [], RuntimeException + FullyQualifiedErrorId : RuntimeException
Set-Content will preserve the file's encoding.-Encoding parameter is dynamic and definitely present for the FileSystem provider. It even works as it should. But whereas Out-File defaults to UTF-16, Set-Content will in fact preserve the original file's encoding (which isn't documented, though).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
10/02/2009 in there and watch ;-)000329|10/01/2011|BLNDCOM|ItemsImport-Csv : Cannot process argument because the value of argument "name" is invalid. Change the value of the "name" argument and run the operation again.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;
000329|09/30/2011|BLNDCOM|Items||||||||||||||||||||||1||1||||||||3|1||2|||||||||||1||||1||2|1||2||1|1|2|3|1|1|1|4|1|1|1||1|3|||||2|||1||||||||2|||||||||||