Datetime year mismatch for different CSV sheet between %y and %Y in Python

Question

I have two CSV files they look identical one has a date format like the following when I double click on each row "11/9/2017 9:18:00 AM" but what looks like in CSV file without a double click is 11/9/17 9:18. For this CSV following format does not return any error. '%m/%d/%Y %H:%M'

The other CSV file when I double click on a row I get the following: "5/17/2020 8:30:00 AM" but without the double click, it looks like this 5/17/20 8:30. However for this file '%m/%d/%Y %H:%M' returns an error that no matching format found.

I know %y is for two digits year and %Y is for 4 digits, however, can anyone tell me why the first CSV does not return any error or why the first CSV returns an error?

Is there any command I can issue to check if there are more differences between these two CSV files?

open the csv in a plain notepad file and see, instead of any office tools like excel — venkata krishnan, Mar 10 '20 at 05:40
Thank you so much. Can you post that as an answer that I can accept. — math, Mar 10 '20 at 06:02

score 0 · Accepted Answer · answered Mar 10 '20 at 06:27

Typically office application interpret the csv files and read the datetime information and display them in the modified format as per the application(MSExcel)

So to know the real different formats, use a plain text editor like nodepad, nodepad++ to observe the difference.

Along with that, you can use a parser function which checks for multiple formats.

from datetime import datetime

def try_parsing_date(text):
    for fmt in ('%Y-%m-%d', '%d.%m.%Y', '%d/%m/%Y'):
        try:
            return datetime.strptime(text, fmt)
        except ValueError:
            pass
    raise ValueError('no valid date format found')

The above code is referred from the answer here

Datetime year mismatch for different CSV sheet between %y and %Y in Python

1 Answers1