Parse String to Hours in CSV data

Question

I have been trying to parse the String I got from the CSV file and convert the column "start" to hour. Here is my code:

public class BeerSong {


    private static final String csvFileName = "HourList201403.csv";
    public static void main (String[] args) throws IOException, java.text.ParseException {        

        CSVReader csvReader = new CSVReader(new FileReader(csvFileName));
        List<String[]>allLine = csvReader.readAll();

        SimpleDateFormat start = new SimpleDateFormat("HH:mm");
        Calendar calendar = GregorianCalendar.getInstance();
        int startH;
        Date startHour;
        for (String[] line : allLine) {
            startHour=start.parse(line[3]);
            calendar.setTime(startHour);
            startH = calendar.get(Calendar.HOUR);
            System.out.println(line[3]+", "+line[4]);
            System.out.println("Work: "+startH);            
        }
    }
}

When I try to run, it outputs an error:

Exception in thread "main" java.text.ParseException: Unparseable date: "Start"

So, what am I doing wrong here and how to fix this ?

The CSV format is like this, it has a lot rows so I put some here:

Person Name, Person ID, Date, Start, End
Scott Scala, 2, 2.3.2014, 6:00, 14:00
Janet Java, 1, 3.3.2014, 9:30, 17:00
Scott Scala, 2, 3.3.2014, 8:15, 16:00
Larry Lolcode, 3, 3.3.2014, 18:00, 19:00

You're probably parsing the header of your csv. Maybe skip a row? — Mena, Aug 17 '15 at 12:38
@Mena: Hi, how can I skip the first row as I am reading all of them at once ? — Tuan Dinh, Aug 17 '15 at 12:41
FYI, the [Apache Commons CSV](https://commons.apache.org/proper/commons-csv/) project is a handy library that takes all the bother out of parsing CSV files (and other such delimited text files). — Basil Bourque, Aug 17 '15 at 21:16

score 2 · Answer 1 · answered Aug 17 '15 at 12:43

The java.text.ParseException is thrown on attempting to parse the string "Start" as a Date.

Clearly that's the header of your spreadsheet, so you should skip the first row (note index 1 here):

 for (int i = 1; i < allLine.size(); i++) {

Note that you'll need the rest of the fields in your column 3 to consistently be of format HH:mm, i.e. not empty, etc.

The trick here is to either use fast-enumeration and skipping the first line, or as I display above, use an int-based iteration starting at index 1 (collections are 0-based, so that'll skip the first item).

MrT · Accepted Answer · 2015-08-17T13:02:09.073

2

You run through all of the parsed lines in your loop. The first line is: Person Name, Person ID, Date, Start, End . So the 4th item which you want to read out with: startHour=start.parse(line[3]); is Start. I think you have to skip the first line in your loop with:

for(int i = 1; i < allLine.size(); i++){ String[] line = allLine.get(i);

edited Aug 17 '15 at 13:02

answered Aug 17 '15 at 12:43

MrT

594
2
17

Hi, it seems that String[] line = allLine[i] get error, so I use the allLine.get(i) and it works – Tuan Dinh Aug 17 '15 at 13:01
and there is one problem I notice, in the file, there are some row that the hour is like 14:00 16:00, but when I convert them, it become 2 and 4. How can I keep the 14 and 16 ? – Tuan Dinh Aug 17 '15 at 13:26
H vs h is difference between 24 hour vs 12 hour format. HH is the format for 24 hours and 12 for am and pm. Have a look [here](http://stackoverflow.com/questions/6842245/converting-date-time-to-24-hour-format) – MrT Aug 17 '15 at 13:33
and if you dont mind, there is some row that the end is in the next day, so when I calculate the working duration between start and end, it returns negative number. And for some row, the start is 14h30 and end is 16h00. It should be 1h30 duration, but I got 2h. Is there any way to fix it ? I tried getting the minutes out from the string and fix some, but that's no good. – Tuan Dinh Aug 17 '15 at 15:58
1

if you like you can have a look at [JodaTime](http://www.joda.org/joda-time/) which provides easy utils to do this tasks you are searching for. – MrT Aug 18 '15 at 05:46

Parse String to Hours in CSV data

2 Answers2