Change date format in multiple text files in PowerShell

Question

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

Can you post a sample of your csv please.

StephenP
– StephenP

2015-02-08 23:04:36 +00:00
Commented Feb 8, 2015 at 23:04 — StephenP
– StephenP, Commented Feb 8, 2015 at 23:04
Sample files updated.

john50
– john50

2015-02-08 23:20:33 +00:00
Commented Feb 8, 2015 at 23:20 — john50
– john50, Commented Feb 8, 2015 at 23:20

Peter Mortensen · Accepted Answer · 2019-03-16 17:42:37Z

2

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.

edited Mar 16, 2019 at 17:42

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Feb 9, 2015 at 0:16

StephenP

4,15122 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

john50 Over a year ago

Thank you very much.It works fine. What do you think about this code? Is this good way to change date format for large number of files or would you suggest different (faster) apporoach? In your previous comment you mentioned using get-date, import-csv, export-csv,...

StephenP Over a year ago

This works fairly quickly. I didn't measure the performance, but I would be rather surprised if import-csv and export-csv were much faster than this.

Collectives™ on Stack Overflow

Change date format in multiple text files in PowerShell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related