2

I have a CSV file I'm trying to import using java. File with date column and dates in different formats I'd like to parse dates suitable for myqsl.

My code as:

CSVFormat format = CSVFormat.RFC4180.withHeader().withDelimiter(',');
CSVParser parsernew =CSVParser(File, format)
List<CSVRecord> csvRecordList = parser.getRecords();
String cellvalue=csvRecordList.get(0);
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date date = dateFormat .parse(cellvalue);

I'm trying to parse date column value but got Exception

java.text.ParseException: Unparseable date:

date column values as likes

10/31/2014
12/13/2013
2001-02-04
7/27/2001
2001-02-04
5/15/2008

Is there any way to parse different date formats and date format changes run time time how to handle that..

in excel

Thanks!

Improve this question
3
  • Try this.. link there's a section on parsing Commented Dec 9, 2014 at 11:33
  • 1
    Looks like you need an intermediate step to sanitize the data. Commented Dec 9, 2014 at 12:13
  • Thank you, folks.Yes i need an intermediate step. In link parse section pattern is fixed every time like that(DateTimeFormatter.ofPattern("MMM d yyyy");) . I m searching for all or most of the pattern dynamic Commented Jan 15, 2015 at 5:57

2 Answers 2

Reset to default
2

Fix the source of data

Firstly, you should educate the publisher of this data about using only standard ISO 8601 formats when serializing date-time values as text. For a date-only without time-of-day and without time zone, that would be YYYY-MM-DD.

Attempt parsing each of the two known formats

Fortunately, your example data shows only two formats: the standard YYYY-MM-DD format, and M/D/YYYY.

So try parsing with a pair of formatters, one for each case. If both fail, throw an exception to alert you to the publisher’s unfortunate use of even more formats.

java.time

Use only java.time classes. Never use the terrible old date-time classes such as Date and DateFormat. Those legacy classes were supplanted years ago with the adoption of JSR 310 in Java 8 and later.

DateTimeFormatter

Define the pair of formatters. The standard one is already defined as a constant: DateTimeFormatter.ISO_LOCAL_DATE. Define the other.

DateTimeFormatter f = DateTimeFormatter.ofPattern( "M/d/uuuu" ) ;

Get your input string in your code using the Apache Commons CSV library. But your code looks wrong in the Question. You need to add a loop for each of the CSVRecord objects in your list. Then extract each column from each row represented by that object.

CSVRecord record = = csvRecordList.get( i ) ;  // Fetch nth object from list.
String cellvalue = csvRecordList.get( 0 ) ;    // Extract each column/cell from each row/object. Must use annoying zero-based index counting.

Catch DateTimeParseException

Attempt to parse the input using each of the two formatters using LocalDate.parse. Catch the DateTimeParseException.

LocalDate ld = null;

if( Objects.isNull( ld ) ) {
    try {
        ld = LocalDate.parse( cellvalue , DateTimeFormatter.ISO_LOCAL_DATE ) ;
    } catch ( DateTimeParseException e ) {
        // Swallow this particular exception in this particular case.
    }
}

if( Objects.isNull( ld ) ) {
    try {
        ld = LocalDate.parse( cellvalue , f ) ;
    } catch ( DateTimeParseException e ) {
        // Swallow this particular exception in this particular case.
    }
}

if( Objects.isNull( ld ) ) {
    // Yikes! Failing all parsing attempts above means we encountered an unexpected format. 
    // FIXME: Handle this error scenario.
}

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You can use SimpleFlatMapper to define different date formats.

As an example:

CsvParser.mapWith(CsvMapperFactory.newInstance()
    .addColumnProperty(k -> k.getName().toLowerCase().contains("date"), new DateFormatProperty("dd/MM/yyyy"))
    .addColumnProperty(k -> k.getName().toLowerCase().contains("date"), new DateFormatProperty("dd-MM-yyyy"))
    .newMapper(Trade.class))
    .stream(reader).collect(toList());
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.