Solved

SimpleDateFormat does not recognize date in csv

Posted on 2012-03-10
10
434 Views
Last Modified: 2012-08-14
I read in a csv file with multiple columns with one date column in the beginning. When you open the file, the date looks like 5/16/1997. However, once the file is read, the date string becomes "1997-05-16".

When I call SimpleDateFormat.parse("1997-05-16"), it throws a ParseException.

any reason why the date format is altered when read, and why SimpleDateFormat can not recognize the date?
0
Comment
Question by:bhomass
  • 8
  • 2
10 Comments
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
> When you open the file, the date looks like 5/16/1997. However, once the file is read, the date string becomes "1997-05-16".

How can that be?

please, show your code
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

java.util.Date dd = sdf.parse("1997-05-16",new ParsePosition(0));

should work
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
This is tested and works without problems:

SimpleDateFormat sdf750 = new SimpleDateFormat("yyyy-MM-dd");

java.util.Date dd750 = sdf750.parse("1997-05-16",new ParsePosition(0));

        System.out.println(dd750);

Open in new window


Output:

Fri May 16 00:00:00 PDT 1997

Open in new window

1
 

Author Comment

by:bhomass
Comment Utility
I need this code to work w/o knowing the exact format. So I don't want to specify new SimpleDateFormat("yyyy-MM-dd");

my code for reading the csv.

try {
                  File csvFile = new File(uploadDir + filename);
                  BufferedReader bufRdr = new BufferedReader(new FileReader(csvFile));
                  String line = null;
                  int row = 0;

                  // read each line of text file
                  while ((line = bufRdr.readLine()) != null) {
                        List<String> oneLine = new ArrayList<String>();
                        StringTokenizer st = new StringTokenizer(line, ",");
                        while (st.hasMoreTokens()) {
                              // get next token and store it in the array
                              oneLine.add(st.nextToken());
                        }
                        if (row==0){
                              headers = oneLine;
                        } else {
                              dataLines.add(oneLine);
                        }
                        row++;
                  }
                  bufRdr.close();
                  return buildRecords(headers, dataLines);
            } catch (Exception ex) {
                  ex.printStackTrace();
            }
0
 
LVL 47

Accepted Solution

by:
for_yan earned 200 total points
Comment Utility
You cannot parse the date without knowing the foramt - you may go through a number of possible formats if you want, but without knowledge of any possible formats
you cannot parse out the date
- you need to enumerate at least some finite number of possibl formats
and then go through them one by one finding the one which works
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 47

Expert Comment

by:for_yan
Comment Utility
In this way you can test any number of formats,
but still you need to be sure that date will follow at least
one of the expected formats:

SimpleDateFormat sdf750 = new SimpleDateFormat("yyyy-MM-dd");
 SimpleDateFormat       sdf751 = new SimpleDateFormat("yyyy/MM/dd");

        sdf750.setLenient(false);
        String dateString = "1997/05/16";

java.util.Date dd750 = sdf750.parse(dateString,new ParsePosition(0));

         if(dd750 == null) {
             System.out.println(" trying again");
                dd750 = sdf751.parse(dateString,new ParsePosition(0));
         }

        System.out.println(dd750);

Open in new window


Output:
 trying again
Fri May 16 00:00:00 PDT 1997

Open in new window

0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
You actually should setLenient(false) on all SimpleDateFormat instances whoch you would use, so better add one more setLenient(false) like here:

SimpleDateFormat sdf750 = new SimpleDateFormat("yyyy-MM-dd");
 SimpleDateFormat       sdf751 = new SimpleDateFormat("yyyy/MM/dd");

        sdf750.setLenient(false);
        sdf751.setLenient(false);
        String dateString = "1997/05/16";

java.util.Date dd750 = sdf750.parse(dateString,new ParsePosition(0));

         if(dd750 == null) {
             System.out.println(" trying again");
                dd750 = sdf751.parse(dateString,new ParsePosition(0));
         }

        System.out.println(dd750);

Open in new window

0
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 200 total points
Comment Utility
> When you open the file, the date looks like 5/16/1997.
don't try to open csv file by double-clicking it, when it opens it in Excel

Open csv file in the Notepad - then you'll see it the way java will be reading it - not with Excel, where Exc el does a number of changes after reading the actual file
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
Yes, I tested, and created the csv file with these dates, which I enetred with Notrpad:

abc,1,03-05-1997
def,3,11-03-2002


when I opened this csv file in Excel,
Excel showed dates with slashes

03/05/1997
11/3/2002


It is definitel the Excel which transfroms the way the date is shown.

So when you read .csv file with java you program will of couse read it as they sit in the csv
file
03-05-1997
Open your file with Notepad - and that's how your java program will see everything in the file.
Don't open in excel - it becomes misleading, as Excle makes transformation of its own
0
 

Author Comment

by:bhomass
Comment Utility
This comment - "You cannot parse the date without knowing the foramt" was not true. it will parse some common date patterns successfully even if you never fed it any pattern, just not the unusual pattern "yyyy-MM-dd".

The case about the internal format differing from Excel display was true.

I ended out listing all possible date patterns I might run into, as suggested by one of the experts.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now