1

I have a CSV with time value as one column & it has below list of values

enter image description here

Excel has formatted this time value to normal time format. i.e.

44:11.0 --> 12:44:11 AM

Even if i open this CSV in text editor value is displayed as 44:11.0.

Please help me understand this time format, as i need to implement parser for this column CSV in Java.

Karthikeyan
  • 496
  • 2
  • 8
  • 18
  • What does 44 & 11.0 signify? are they minutes and seconds? – Dusayanta Prasad Apr 22 '20 at 06:59
  • Refer (https://www.ablebits.com/office-addins-blog/2015/06/23/excel-time-format-now-time-functions/) . The decimal portion is the time, in your case, while the data is being generated (and converted), the integer portion (the date) is somehow being lost and the resulting time is being formatted as Excel time only. You could check how the data is being generated and the Excel export procedure from the source. – Ironluca Apr 22 '20 at 09:42
  • Are the times durations, amounts of time, 44 minutes 11.0 seconds? And why the java tag? – Ole V.V. Apr 22 '20 at 16:34
  • 1
    @OleV.V. I had added Java tag.. because i need to parse & format this time string using Java SimpleDateFormat. – Karthikeyan Apr 23 '20 at 07:48
  • @DusayantaPrasad: i am trying to understand this excel formatted time. It formats 44:11.0 to 12:44:11. I dont know whether it is correct way of formatting. – Karthikeyan Apr 23 '20 at 07:49
  • I recommend that you stay away from `SimpleDateFormat`. It’s a notorious troublemaker of a class. And while it can parse and format a time of day such as 12:44:11 AM (depending), it cannot parse a duration such as 44 minutes 11.0 seconds. – Ole V.V. Apr 23 '20 at 15:54
  • Does this answer your question? [Java: How to convert a string (HH:MM:SS) to a duration?](https://stackoverflow.com/questions/8257641/java-how-to-convert-a-string-hhmmss-to-a-duration) There are more similar questions. – Ole V.V. Apr 23 '20 at 15:57

1 Answers1

1

You have to use Apache Commons CSV to read .csv files.

Download the jar from here - https://commons.apache.org/proper/commons-csv/download_csv.cgi

You can try this :

I created an .csv file in my local - Book1.csv

enter image description here

You should modify the column to make your work easier.

Select the all the rows of the column and right click and click on Format cells.

enter image description here

Click on number tab -> Click on Custom menu and select -> hh:mm:ss

enter image description here

After this Click on OK to get the changes.

enter image description here

The code :

public class Testing {
    public static void main(String args[]) throws ParseException {
        try (Reader reader = new FileReader("C:\\Users\\Anish\\Desktop\\Book1.csv");
                CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT);) {
            System.out.println("Loaded");
            for (CSVRecord csvRecord : csvParser.getRecords()) {
                SimpleDateFormat format = new SimpleDateFormat("hh:mm:ss");
                Date date = format.parse(csvRecord.get(0));
                System.out.println(date.getHours() + ":" + date.getMinutes() + ":" + date.getSeconds());
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

Output :

Loaded
0:44:11
0:29:4
0:22:41
Anish B.
  • 7,985
  • 3
  • 16
  • 39
  • 1
    Yes, I am able to find the format using your suggestion. I identified the format & used that same format in Java to decode the value. Thank you – Karthikeyan May 05 '20 at 09:28