not able to parse string into date using jexcel api

Question

I am faced with a problem i faced a few days ago. i have to parse this file and convert the string into date. I had done the same to another CSV file few days ago. However, the new file is giving this error: java.text.ParseException: Unparseable date: ""3/4/2011""

Why is the string getting printed with two "" around it? I think that is the root cause of the problem. I am posting the code below . Please note that the same code works for another file where the date is printed as 3/4/2011 with no "" around it.

     WritableWorkbook wbwrite=null;
     WritableSheet sheet=null;
     Label lwrite=null;
     BufferedReader br=null;
     Number n=null;

     String strFile="quotes.csv";
 String strLine = "";
     String st[];
     String delimiter="[,]";

int lineNumber = 0, tokenNumber = 0;
    int length=0;
    int counter=0;
    Calendar cal = Calendar.getInstance();

    DateTime datecell=null;

     if(strFile.equals("quotes.csv"))
     {
         System.out.print("in loop if");

         try
         {
            File file=new File("quotes.xls");
            wbwrite=Workbook.createWorkbook(file);
            System.out.print("file created");
            //path=file.getCanonicalPath();
            sheet = wbwrite.createSheet("Sheet 1", 0);
            lwrite=null;
            n=null;
            Date dtest=null;
            DateFormat df=null;
            Date dadd=null;

            df=new SimpleDateFormat("dd-mm-yy");

    //create BufferedReader to read csv file
    br = new BufferedReader( new FileReader(strFile));
    strLine = "";
    //st[]=empty;
            delimiter="[,]";
    lineNumber = 0; tokenNumber = 0;
            length=0;
    //read comma separated file line by line
    while( (strLine = br.readLine()) != null)
    {
        //System.out.print("strline="+strLine);
        //break comma separated line using ","
        st=strLine.split(delimiter);
                    length=0;
        while(length<st.length)
        {
            //display csv values
            //
            System.out.println("Line # " + lineNumber +
                    ", Token # " + tokenNumber
                    + ", Token : "+ st[length]);
                           if(tokenNumber==2)
                           {
                                 System.out.print("date="+st[length]);
                                  DateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
                                  Date date = (Date)formatter.parse(st[length]);
                                  System.out.print("date="+date);
                                  cal.setTime(date);
                                  cal.set(Calendar.HOUR_OF_DAY, 17);
                                  cal.set(Calendar.MINUTE, 30);
                                  cal.set(Calendar.SECOND, 0);
                                  cal.set(Calendar.MILLISECOND, 0);
                                  date = cal.getTime();
                                  System.out.print("date="+date);
                                  datecell=new DateTime(tokenNumber,lineNumber,date);
                                  sheet.addCell(datecell);
                                  lwrite=new Label(tokenNumber,lineNumber,st[length]);
                                  sheet.addCell(lwrite);
                         }
                     if(tokenNumber==1||tokenNumber==5||tokenNumber==6||tokenNumber==7)
                     {
                            n=new Number(tokenNumber,lineNumber,Float.parseFloat(st[length]));
                           sheet.addCell(n);
                     }
                     tokenNumber++;
                     length++;
    }
    //reset token number
            //tokenNumber = 0;
}
    wbwrite.write();
    wbwrite.close();
}
catch(Exception e)
{
        System.out.print("error="+e);
}
  }
 }

score 0 · Accepted Answer · answered Mar 06 '11 at 07:58

I solved it. The string already had leading and trailing quotes which led to double quotes when parsed. that's why ""3/4/2011"". I added

      if (st[length].startsWith("\""))
     {
      st[length] = st[length].substring(1, st[length].length());
      }
     if (st[length].endsWith("\""))
  {
        st[length] = st[length].substring(0, st[length].length() - 1);
  }

Plus Even the date format was dd-MM-yyyy ; which was changed to dd/MM/yyyy. Now i am getting the correct answer.

not able to parse string into date using jexcel api

1 Answers1