1

I have one Python file that helps me pull and organize datetime data from a server into a pandas DataFrame, and then export out into a nice and usable CSV. My second Python script needs to read the CSV and analyze the data. If I do not touch the exported CSV, the analysis can read the CSV and runs smoothly. However, as soon as I try to merge a few CSV files together with Excel/other spreadsheet software, I get a datetime error

ValueError("time data %r does not match format %r" %    
ValueError: time data '2019-12-26 23:00' does not match format '%Y-%m-%d  %H:%M:%S'

Even though, it is a direct copy/paste and still saved as a CSV. Any guru can provide some insight on this matter?

FObersteiner
  • 22,500
  • 8
  • 42
  • 72
Jenny West
  • 15
  • 3

2 Answers2

0

I see from the error that not all your CSV files have the same time format.

Some on them are in [hour:minute] and some in [hour:minute:second].

ValueError: time data '2019-12-26 23:00' does not match format '%Y-%m-%d  %H:%M:%S'

Make sure that datetime fields in all of your CSV matched the same format.

Mahmoud
  • 9,729
  • 1
  • 36
  • 47
  • 1
    they are, since all the export are done with the same python file with the same format. I think it has something to do with Excel converting the string. Once I paste some datetime, Excel recognize it is a date and does something internally.... – Jenny West Nov 27 '20 at 02:29
  • I suggest converting every datetime field into UNIX time stamp format. you can go back and forth easily between ISO and UNIX (even in Excel). – Mahmoud Nov 27 '20 at 03:00
  • "*go back and forth easily between ISO and UNIX (even in Excel)*" - like [this](https://stackoverflow.com/questions/46130132/converting-unix-time-into-date-time-via-excel)? that sounds equally painful like the [formatting option](https://stackoverflow.com/a/65034346/10197418) ^^ – FObersteiner Nov 27 '20 at 09:28
0

Pretty sure this is an Excel issue, not a Python problem.

If you load a .csv that has timestamp strings into Excel, Excel recognizes the datetimes - and formats them. This format seems to default to MM.DD.YYYY hh:mm (the date component might be different depending on your locale): enter image description here

If you save the file in Excel, the seconds are removed in the .csv!

  • The only procedure that seems to reliably prevent this behavior is to set a specific date/time format for the respective column, e.g. DD.MM.YYYY hh:mm:ss. AFAIK, You'll have to do this manually for each workbook

  • Or perhaps write a macro. In older Excel versions, I had a PERSONAL.XLSB for that; should still work with newer versions, you'll have to put it in C:\Users\[username]\AppData\Roaming\Microsoft\Excel\XLSTART

  • you can use .xlsx format instead of .csv, presumably you won't loose format information there

FObersteiner
  • 22,500
  • 8
  • 42
  • 72