How to replace the date format in a string field coming from a csv file in c#

Question

I have a csv file that contains multiple fields, one of which is a string field containing comments which include dates in the dd MMM yyyy format. I need to convert these dates into a MM/dd/yyyy hh:mm:ss format. I've never worked with files before so I broke everything down into steps and began researching one step at a time

1. Open, read, and parse the file into the different fields
2. Search each field for a dd MMM yyyy date
3. Replace the date with a MM/dd/yyyy 00:00 formatted date
4. Write the new data back to the field it came from
5. Close the file

I've went as far as number 2 but I'm having a heck of a time getting any further and I'm beginning to think that maybe the way I did steps 1 and 2 were not the best way. I've been attempting to use regex.Replace() but have been going no where for a number of reasons: What I'm attempting to change is part of the foreach statement so VS won't allow me to change it, but even if it did, the Replace() method takes strings not Regex objects. Below is one of the many unsuccessful ways I've attempted to replace the data.

static void Main(string[] args)
{   
    TextFieldParser MyReader = new TextFieldParser("C:\\\\Users\\hacknj\\Desktop\\mo_daily_activity_20160627_1412.txt");
    MyReader.TextFieldType = FieldType.Delimited;
    MyReader.SetDelimiters(",");
    string[] currentRow;           
    while (!MyReader.EndOfData)
    {
        try
        {
            //Processing row
            currentRow = MyReader.ReadFields();
            foreach (string currentField in currentRow)
            {                       
                Regex badDate = new Regex(@"(\s\d{2}\s[a-zA-Z]{3}\s\d{4})");
                Regex goodDate = new Regex(@"([0,1]?\d{1}\/(([0-2]?\d{1})|([3][0,1]{1}))\/(([1]{1}[9]{1}[9]{1}\d{1})|([2-9]{1}\d{3}))\s(([0-1]?[0-9])|([2][0-3])):([0-5]?[0-9]))");
                Match match = badDate.Match(currentField);
                string matchedDate = match.ToString();
                if (match.Success)
                {
                    currentField = Regex.Replace(currentField, badData, goodData);
                }                        
            }
        }
        catch (MalformedLineException ex)
        {
            MessageBox.Show("line " + ex.Message + "is not valid and will be skipped");
        }
    }            
    MyReader.Close();
}

So the question is, is it possible to do what I want to do in the way I have my code now or should I start over again using StreamReader/Writer (unfortunately those didn't show up in my research until I got to steps 3 and 4)?

Answer 1

Try this:

foreach (string currentField in currentRow)
{
    // Define bad date
    Regex badDate = new Regex(@"(\s\d{2}\s[a-zA-Z]{3}\s\d{4})");
    // Find Matches
    MatchCollection matches = badDate.Matches(currentField);
    // Go through each match
    foreach (Match match in matches)
    {
        // get the match text
        string matchText = match.Groups[0].ToString();
        // Define DateTime
        DateTime parsedDate;
        // If it parses
        if (DateTime.TryParseExact(matchText, "dd MMM yyyy", new CultureInfo("en-US"),
                DateTimeStyles.None, out parsedDate))
        {
            // Replace that specific text
            currentField = currentField.Replace(matchText,
                parsedDate.ToString("MM/dd/yyyy 00:00"));
        }
    }
}

If this doesn't work, I suggest using a CSV file manipulator or try out the following:

string file = File.ReadAllText("C:\\\\Users\\hacknj\\Desktop\\mo_daily_activity_20160627_1412.txt");
// Define bad date
Regex badDate = new Regex(@"(\s\d{2}\s[a-zA-Z]{3}\s\d{4})");
// Find Matches
MatchCollection matches = badDate.Matches(file);
// Go through each match
foreach (Match match in matches)
{
    // get the match text
    string matchText = match.Groups[0].ToString();
    // Define DateTime
    DateTime parsedDate;
    // If it parses
    if (DateTime.TryParseExact(matchText, "dd MMM yyyy", new CultureInfo("en-US"),
            DateTimeStyles.None, out parsedDate))
    {
        // Replace that specific text
        file = file.Replace(matchText,
            parsedDate.ToString("MM/dd/yyyy 00:00"));
    }
}
File.WriteAllText("C:\\\\Users\\hacknj\\Desktop\\mo_daily_activity_20160627_1412.txt", file);