Times not converted correctly

dilshod / xlsx2csv

Convert xslx to csv, it is fast, and works for huge xlsx files

MIT License

1.68k stars 302 forks source link

Times not converted correctly #217

Open mikerob-w4 opened 3 years ago

mikerob-w4 commented 3 years ago

I am getting strange output when trying to convert an Excel sheet containing times in format h:mm:ss (I am using timeformat='%H:%M:%S'). In the csv file most times appear correctly, such as 00:00:00 however others are appearing as numbers such as 1.0416999999999999E-2.

Excel and converted csv attached,

testxl.xlsx Values.csv

mikerob-w4 commented 3 years ago

Just to provide a bit more information, converting times as numbers seems to happen for some cells if excel Format Cells is 'Time'. If Format Cells in Excel is Custom hh:mm:ss then the cells convert times to hh:mm:ss format in the csv.

Converting times as numbers only seems to happen to some times. 00:00:00 is converted as 00:00:00 while 00:15:00 is converted as a number.

JamesW-NHS commented 2 months ago

This is still happening as of 0.8.3 for all Time values between 00:00:01 and 01:00:00.

I've worked around it in Python by reading the .xlsx into a pandas DataFrame and writing it back out again. (Why this works: the DataFrame doesn't have a Time dtype itself, and therefore converts any incoming Time cells into string.)