0

I have a program built that takes my access DB and exports to a CSV. Everything works great but the program that takes in the exported CSV has some hardcoded regex and cannot handle the different format of certain datatypes.

Date
IS: Fri May 01 00:00:00 EDT 2015
NEEDS: 5/1/2015 00:00:00

Boolean?
Not sure if these field are a boolean but
IS: TRUE or FALSE
NEEDS: 0 or 1

Currency
IS: 0
NEEDS: $0.00

Strings
IS: string
NEEDS: "string"

After reading through the docs this line jumped out at me "the row values are strongly typed Java objects. In Jackcess, the column types are represented by a Java enum named DataType." Any help is greatly appreciated.

methode
  • 5,348
  • 2
  • 31
  • 42
sneakernetsec
  • 3
  • 1

1 Answers1

0

Use uniVocity-parsers for that:

    Writer output = new StringWriter(); // use a FileWriter for your case

    CsvWriterSettings writerSettings = new CsvWriterSettings(); //many options here, check the tutorial

    ObjectRowWriterProcessor writerProcessor = new ObjectRowWriterProcessor(); // handles rows of objects and conversions to String.
    writerSettings.setRowWriterProcessor(writerProcessor);
    writerSettings.setHeaders("A", "B", "C", "D", "E", "F", "G", "H");

    CsvWriter writer = new CsvWriter(output, writerSettings);

    writerProcessor.convertFields(Conversions.toBoolean("0", "1")).add("C", "H"); // will write "0" and "1" instead of "true" and "false" on column "C" and "H"
    writerProcessor.convertFields(Conversions.toDate("M/d/YYYY HH:mm:ss")).add("A", "E");
    writerProcessor.convertFields(Conversions.formatToBigDecimal("$#0.00")).add("B", "D");

    writer.writeHeaders();
    writer.processRecord(new Date(), BigDecimal.TEN, true, new BigDecimal("999.99"), new Date(), "some text here", null, false);
    writer.processRecord(new Date(), BigDecimal.ZERO, false, null, null, null, "more text here", null);
    writer.close();

    System.out.println(output.toString()); //and here is the result

The output will be:

A,B,C,D,E,F,G,H
7/8/2015 07:09:58,$10.00,0,$999.99,7/8/2015 07:09:58,some text here,,1
7/8/2015 07:09:58,$0.00,1,,,,more text here,

Disclosure: I am the author of this library. It's open-source and free (Apache V2.0 license).

josliber
  • 43,891
  • 12
  • 98
  • 133
Jeronimo Backes
  • 6,141
  • 2
  • 25
  • 29
  • Great this looks like what I am going for. – sneakernetsec Jul 08 '15 at 17:50
  • So I have uniVocity tied in to my project now. I was wondering how I can make this code dynamic. I know I want to convert every field that matches the criteria no matter which table it is in. Therefore I cannot setHeaders and which columns should be processed manually. – sneakernetsec Jul 22 '15 at 17:27
  • Should become easier when [this enhancement](https://github.com/uniVocity/univocity-parsers/issues/37) is implemented. Right now you can implement your own [RowWriterProcessor](https://github.com/uniVocity/univocity-parsers/blob/master/src/main/java/com/univocity/parsers/common/processor/RowWriterProcessor.java) to check the class of each input element and apply the conversions accordingly, or extend the existing `ObjectRowWriterProcessor`. If you're willing to contribute to the project, feel free to submit a pull request. – Jeronimo Backes Jul 22 '15 at 21:21
  • This still is what I am looking for and I am going to play with some ideas to make it work for this type of application when I get back to school and have some help. Until then I will use your method shown above as it will do the job. Thank you again. – sneakernetsec Jul 23 '15 at 19:29
  • `Exception in thread "main" com.univocity.parsers.common.TextParsingException: Error processing input: java.lang.IllegalStateException - Error converting value 'Tue Apr 03 00:00:00 EDT 2001' using conversion com.univocity.parsers.conversions.DateConversion` I am getting this error when attempting to convert my dates. I am using the ObjectRowProcessor. Any help would be great. Let me know if I should post any other information. – sneakernetsec Jul 28 '15 at 17:08
  • Seems like you are converting the String 'Tue Apr 03 00:00:00 EDT 2001' instead of a Date object. If that's the case you'll have to convert this String to Date first. – Jeronimo Backes Jul 29 '15 at 01:03