Validate decimal number

Question

I'm reading some .csv file that contain string that rapresents decimal number. My trouble is that many times I'm reciving file write with different locale. For example:

1. The value of the column price of file1.csv is 129,13 (, is a decimal separator)
2. The value of the column price of file1.csv is 129.13 (. is a decimal separator)

Now I'm trying to read the value in this way:

 DecimalFormatSymbols dfs = new DecimalFormatSymbols(new Locale(en,US));
 DecimalFormat df= new DecimalFormat();
 df.setDecimalFormatSymbols(dfs);
 df.setParseBigDecimal(true);
 bigDecimal = (BigDecimal) df.parse(value);

Using this snippet code the first value become 12913 (not correct), while the second one become 129.13 (correct). Now I would want that if I using en_US local and the file contains values that use , like decimal separator, I have to throw an exception.

How can I do this?

Answer 1

From the Java Tutorials:

DecimalFormatSymbols unusualSymbols = new DecimalFormatSymbols(currentLocale);
unusualSymbols.setDecimalSeparator('|');
unusualSymbols.setGroupingSeparator('^');

String strange = "#,##0.###";
DecimalFormat weirdFormatter = new DecimalFormat(strange, unusualSymbols);
weirdFormatter.setGroupingSize(4);

You need to set the DeciamlFormat pattern to pull it all together.

DecimalFormatSymbols dfs = new DecimalFormatSymbols(new Locale(en,US));
dfs.setGroupingSeparator('@');

DecimalFormat df= new DecimalFormat(#,###.#", dfs);
df.setParseBigDecimal(true);

bigDecimal = (BigDecimal) df.parse(value);

Answer 2

You can read your CSVs with univocity-parsers.

We are still working on version 2.0, which introduces format auto-detection, but you can already get a snapshot version and use it to handle this.

Simple example:

public static void main(String... args) {

    CsvParserSettings parserSettings = new CsvParserSettings();
    parserSettings.detectFormatAutomatically();

    List<String[]> rows = new CsvParser(parserSettings).parseAll(new StringReader("Amount,Tax,Total\n1.99,10.0,2.189\n5,20.0,6"));
    for (Object[] row : rows) {
        System.out.println(Arrays.toString(row));
    }

    System.out.println("####");

    rows = new CsvParser(parserSettings).parseAll(new StringReader("Amount;Tax;Total\n1,99;10,0;2,189\n5;20,0;6"));
    for (Object[] row : rows) {
        System.out.println(Arrays.toString(row));
    }
}

Output:

[Amount, Tax, Total]
[1.99, 10.0, 2.189]
[5, 20.0, 6]
####
[Amount, Tax, Total]
[1,99, 10,0, 2,189]
[5, 20,0, 6]

You can get the latest snapshot version from here.

Or, if you use maven, add this to your pom.xml:

<repositories>
    <repository>
        <id>ossrh</id>
        <url>https://oss.sonatype.org/content/repositories/snapshots</url>
    </repository>
</repositories>

And set the version to 2.0.0-SNAPSHOT:

<dependency>
        <groupId>com.univocity</groupId>
        <artifactId>univocity-parsers</artifactId>
        <version>2.0.0-SNAPSHOT</version>
</dependency>

If you find any problem, just open a new issue in the project's github page

Edit: another example demonstrating how you can convert your input rows to BigDecimal, using multiple formatters:

public static void main(String... args) {
    // ObjectRowListProcessor converts the parsed values and stores the result in a list.
    ObjectRowListProcessor rowProcessor = new ObjectRowListProcessor();

    FormattedBigDecimalConversion conversion = new FormattedBigDecimalConversion();
    conversion.addFormat("0.00", "decimalSeparator=.");
    conversion.addFormat("0,00", "decimalSeparator=,");

    // Here we convert fields at columns 0, 1 and 2 to BigDecimal, using two possible input formats 
    rowProcessor.convertIndexes(conversion).set(0, 1, 2);

    // Create a settings object to configure the CSV parser
    CsvParserSettings parserSettings = new CsvParserSettings();

    //I'll separate the values using | to make it easier for you to identify the values in the input
    parserSettings.getFormat().setDelimiter('|');

    // We want to use the RowProcessor configured above to parse our data 
    parserSettings.setRowProcessor(rowProcessor);


    // Create the parser
    CsvParser parser = new CsvParser(parserSettings);

    // Parse everything. All rows are sent to the rowProcessor configured above
    parser.parse(new StringReader("1.99|10.0|2.189\n1,99|10,0|2,189"));

    // Let's get the parsed rows
    List<Object[]> rows = rowProcessor.getRows();
    for (Object[] row : rows) {
        System.out.println(Arrays.toString(row));
    }
}

And here's the output: 2 arrays with BigDecimal objects, and the correct values:

[1.99, 10.0, 2.189]
[1.99, 10.0, 2.189]

Answer 3

While you cannot set nothing (null) for the group separator (as it is a char) when using the DecimalFormatSymbols you can set it to something very unusual to be found in a valid number, such as '@'.

 DecimalFormatSymbols dfs = new DecimalFormatSymbols(new Locale(en,US));
 dfs.setGroupingSeparator('@');
 DecimalFormat df= new DecimalFormat();
 df.setDecimalFormatSymbols(dfs);
 df.setParseBigDecimal(true);
 bigDecimal = (BigDecimal) df.parse(value);