1

I am using Apache Commons CSV library.

I am trying to parse a date / time format to CSVFormat which leads to, if there is any time / date columns it will be formatted according to the format provided to CSVFormat.

When I remove date / time format it's working perfect, but it's NOT working when adding date / time format.

example of date / time formats I would like to add:

  • YYYY-MM-DD
  • DD-MM-YYYY HH:mm:SS
  • YYYY-MM-DD HH:mm:SS

Also here is the code I am trying to add the date / time format to;

public ByteArrayInputStream dataToCSV(List<?> dataList, char delimiter){
        final CSVFormat format = CSVFormat.DEFAULT.withDelimiter(delimiter);
//        final String format = CSVFormat.DEFAULT.withDelimiter(delimiter).format("MM-YYYY-DD HH:mm:SS");
        try (ByteArrayOutputStream out = new ByteArrayOutputStream();
             CSVPrinter csvPrinter = new CSVPrinter(new PrintWriter(out), format);) {
            if (dataList.size() > 0){
                // headers will be written just one time
                csvPrinter.printRecord(this.getHeaders(dataList.get(0)));
                for (Object objectClass : dataList) {
                    List<String> data = getRecordData(objectClass);
                    csvPrinter.printRecord(data);
                }
            }
            csvPrinter.flush();
            return new ByteArrayInputStream(out.toByteArray());
        } catch (InstantiationException e) {
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

The line I commented is the one that not working. The first above one is the one that works properly.

Data is coming from a PostgreSQL DB, also I am using hibernate and commons-csv dependency

<dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-csv</artifactId>
            <version>1.8</version>
</dependency>

example of timestamp field in DB

2022-11-23 11:12:13.123456

example of the same field formatted I got in CSV

2022-11-23 11:12:13.123456

example of needed format

11-23-2022 11:12:13.123456

Any help is appreciated,

Thanks in advance!

Moaaz Gaballah
  • 85
  • 1
  • 9
  • 1
    Not knowing Apache CSVFormat, the way I read [the docs](https://commons.apache.org/proper/commons-csv/apidocs/org/apache/commons/csv/CSVFormat.html#format-java.lang.Object...-) you are supposed to pass the value(s) to be formatted to `format()`. Is that what you are doing? Is `"MM-YYYY-DD HH:mm:SS"` such a value? Also what error message are you seeing when you are adding date / time format? – Ole V.V. Dec 20 '22 at 13:44
  • @OleV.V. Thank you for your response! ```format()``` is a function that returns a String, but I need an CSVFormat Object to pass it to the ```CSVPrinter()``` So if I get it as ```String``` I can't add it as parameter to ```CSVPrinter()``` Also I added SS of the date / time field in DB as well as in CSV file, You can have a look – Moaaz Gaballah Dec 20 '22 at 15:25
  • 2
    This is confusing. Date formats really have nothing to do with `CSVFormat`. The function of that class appears to be to describe *metadata*. Your datetime values are *data* – g00se Dec 20 '22 at 18:21
  • The way I read the [documentation](https://commons.apache.org/proper/commons-csv/apidocs/org/apache/commons/csv/CSVFormat.html) a `CSVFormat` does not (cannot) provide a date format. If you need a particular date-time format in your resulting CSV, would it work to pass formatted strings to your `CSVPrinter`? – Ole V.V. Dec 20 '22 at 19:18
  • For how to handle date-time input in different formats, see for example [Format date in yyyy-MM-dd hh:mm:ss format from whatever format](https://stackoverflow.com/questions/62708324/format-date-in-yyyy-mm-dd-hhmmss-format-from-whatever-format) or [How to format date in java 8? \[duplicate\]](https://stackoverflow.com/questions/64770698/how-to-format-date-in-java-8). – Ole V.V. Dec 20 '22 at 19:42
  • 1
    Well, I think it's not possible to bind it. Thank you guys for reply! – Moaaz Gaballah Dec 21 '22 at 06:46
  • You might be better off using a library that *does* do binding such as OpenCsv. You would then be able to to adapt the binding to use your custom date parsing – g00se Dec 21 '22 at 09:07
  • @g00se thank you for recommendation, could you provide me more details about how openCsv deals with binding date / time formatting. Such as the function that binds, if there is an example of that function it will be so good. Again thanks for recommendations! – Moaaz Gaballah Dec 22 '22 at 06:24
  • 1
    You would register a custom converter. Something like `public class LocalDateTimeConverter extends AbstractBeanField` where you'd insert the 'T' so that parsing went properly. See OpenCsv docs – g00se Dec 22 '22 at 13:25

1 Answers1

4

CSVFormat#format does not do what you think

Your call to CSVFormat#format in the commented line:

final String format = CSVFormat.DEFAULT.withDelimiter(delimiter).format("MM-YYYY-DD HH:mm:SS");

… is incorrect. And shows that you misunderstand how Apache Commons CSV works.

Unfortunately, the Javadoc for that command is nearly useless:

Formats the specified values.

So let's look at the source code:

1427    /**
1428     * Formats the specified values.
1429     *
1430     * @param values the values to format
1431     * @return the formatted values
1432     */
1433    public String format(final Object... values) {
1434        final StringWriter out = new StringWriter();
1435        try (CSVPrinter csvPrinter = new CSVPrinter(out, this)) {
1436            csvPrinter.printRecord(values);
1437            final String res = out.toString();
1438            final int len = recordSeparator != null ? res.length() - recordSeparator.length() : res.length();
1439            return res.substring(0, len);
1440        } catch (final IOException e) {
1441            // should not happen because a StringWriter does not do IO.
1442            throw new IllegalStateException(e);
1443        }
1444    }

We can see that you are supposed to pass values, not a date-time formatting pattern.

We can also see that this method does not write to your CSV file. Instead, this method is merely a shortcut for using CSVPrinter to generate the text of what would be written to your CSV file if your CSV file were the actual target of output. With this format method, the text generated is merely returned to the calling code.

You need to understand that Commons CSV does not do binding. The library merely parses pieces of CSV as plain text, not objects. So specifying a date-time formatting pattern with Commons CSV makes no sense; that's not a feature.

You must write your own Java code to produce the strings you want to send out to a CSV file through Commons CSV.

So you need to do the parsing from text to date-time object yourself. If you have a string such as 2022-11-23 00:00:00.0000, you should be parsing as a LocalDateTime object. To do so, you could define your own formatting pattern with DateTimeFormatter class. But instead I would do a bit of string manipulation to alter the input by replacing the SPACE in the middle with a T:

LocalDateTime myLocalDateTime = LocalDateTime.parse( "2022-11-23 00:00:00.0000".replace( " " , "T" ) ) ;

To learn more about using Apache Commons CSV, search Stack Overflow. Many posts have already been made, including some authored by me.

ISO 8601

Big Tip: Always use ISO 8601 standard formats for data exchange of date-time values. Quite easy to do with java.time classes as they use the ISO 8601 formats by default when parsing/generating text.

String output = myLocalDateTime.toString() ;

… and:

LocalDateTime myLocalDateTime = LocalDateTime.parse( "2022-11-23T00:00:00.0000" ) ;

See that code run at Ideone.com.

Another tip: If you are trying to represent a moment, a specific point on the timeline, always include an offset-from-UTC or a time zone. The LocalDateTime class cannot represent a moment as it lacks the context of an offset or zone.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154