Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 504
  • Last Modified:

SimpleDateFormat does not recognize date in csv

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
bhomass
Asked:
bhomass
  • 8
  • 2
2 Solutions
 
for_yanCommented:
> 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
 
for_yanCommented:
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

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

should work
0
 
for_yanCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
bhomassAuthor Commented:
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
 
for_yanCommented:
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
 
for_yanCommented:
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
 
for_yanCommented:
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
 
for_yanCommented:
> 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
 
for_yanCommented:
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
 
bhomassAuthor Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now