CODEWITHSUNDEEP
datepowershellformat
john50
john50

Reputation: 467

Change date format in multiple text files in PowerShell

I have around 5000 text files (CSV) in one directory. Files are comma delimited. I'm trying to change the date format (it is stored in the first column of each file) from M/d/yyyy to MM/dd/yyyy.

There are around 1 milion lines in those 5000 files, so I think the StreamWriter command is a way to go. Some CSV files have a header, but not all. Please see the code below. I think I'm pretty close...

The code below do its job for one specific file (test.csv), but it doesn't work for all CSV files (*.csv) files in a single directory. Is there more space for speed improvement of this code?

$file = "C:\Test\Test.csv";

try
{
    $stringBuilder = New-Object System.Text.StringBuilder;

    try
    {
        $reader = New-Object System.IO.StreamReader($file)

        while($reader.Peek() -ge 0)
        {
            [datetime]$dirDate = New-Object DateTime;
            $line = $reader.ReadLine();
            $dateVal = $line.Split(",")[0];

            if ([DateTime]::TryParseExact($dateVal,
                                          "M/d/yyyy",
                                          [System.Globalization.CultureInfo]::InvariantCulture,
                                          [System.Globalization.DateTimeStyles]::None,
                                          [ref]$dirDate))
            {
                $result = $line -replace $line.Substring(0, $line.IndexOf(",")), $dirDate.ToString("MM/dd/yyyy");
                $stringBuilder.Append($result + "`r`n") | Out-Null;
            }
            else
            {
                $stringBuilder.Append($line + "`r`n") | Out-Null;
            }
        }
    }
    finally
    {
        $reader.Close();
    }

    try
    {
        $sw = New-Object System.IO.StreamWriter $file;
        $sw.Write($stringBuilder.ToString());
        Write-Host "File processed successfully.";
    }
    finally
    {
        $sw.Close();
    }
}
catch
{
    Write-Host "Caught an exception:" -ForegroundColor Red;
    Write-Host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Red;
    Write-Host "Exception Message: $($_.Exception.Message)" -ForegroundColor Red;
}

File sample without a header:

 8/1/2014,35,35.6,32.64,34.17,4217150
 8/4/2014,34.34,35.86,33.66,34.17,2231804
 8/5/2014,33.74,34.08,33.11,33.58,3456364
 8/6/2014,33.91,35.08,33.76,33.92,2805103
 8/7/2014,33.8,34.99,33.01,33.43,2474410
 8/8/2014,33.23,34.19,32.5,33.76,1929437

file sample with Header:

 Date,Header2,Header3,Header4,Header5,Header6
 8/1/2014,75.65,78,74.21,76.7,1376428
 8/4/2014,77.07,81.459,76.92,80.96,1871618
 8/5/2014,80.87,82.5,78.101,79.34,1894376
 8/6/2014,78.42,83.49,77.99,82.75,2792467
 8/7/2014,81.27,86,78.82,85.8,4496068

Upvotes: 0

Views: 895

Answers (1)

StephenP
StephenP

Reputation: 4081

I converted it to a function for you. You were really close, but had some issues with the excess try catch finally blocks. I also changed $reader.peek -ge 0 to -not $reader.endofstream

function ParseCSV ($file) {
    try {
        $stringBuilder = New-Object System.Text.StringBuilder;
        $reader = New-Object System.IO.StreamReader($file)
        while(-not $reader.EndOfStream) {
            [datetime]$dirDate = New-Object DateTime;
            $line = $reader.ReadLine();
            $dateVal = $line.Split(",")[0];
            if([DateTime]::TryParseExact($dateVal,
                                         "M/d/yyyy",
                                         [System.Globalization.CultureInfo]::InvariantCulture,
                                         [System.Globalization.DateTimeStyles]::None,
                                         [ref]$dirDate))
            {
                $result = $line -replace $line.Substring(0, $line.IndexOf(",")), $dirDate.ToString("MM/dd/yyyy");
                $stringBuilder.Append($result + "`r`n") | Out-Null;
            }
            else
            {
                $stringBuilder.Append($line + "`r`n") | Out-Null;
            }
        }
        $reader.Close()
        $sw = New-Object System.IO.StreamWriter $file;
        $sw.Write($stringBuilder.ToString());
        Write-Host "File processed successfully.";
    }
    catch {
        Write-Host "Caught an exception:" -ForegroundColor Red;
        Write-Host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Red;
        Write-Host "Exception Message: $($_.Exception.Message)" -ForegroundColor Red;
    }
    finally {
        if($reader) {
            $reader.Dispose()
        }
        if($sw) {
            $sw.Dispose()
        }
    }
}

I used it like this:

dir *.csv | %{ParseCSV $_.FullName}

It parsed 25 of the sample files you provided, converting the dates from m/d/yyyy to mm/dd/yyyy.

Upvotes: 2

Related Questions