DateTime columns incorrectly read from CSV with European locale

Aspose.Cells Product Family
1

We have a problem reading a CSV file with DateTime columns in it (see attached file).

We are located in the Netherlands, where the DateTime format is dd-mm-yyyy.
When I read the attached file and let Aspose “guess” the type of the columns, it correclty guesses that all 4 columns are DateTime.
However, it converts 01-02-2012 to January 2, 2012 in stead of February 1, 2012, as if the locale was EN_us (mm-dd-yyyy). The same goes for 01-02-2013 and 01-40-2014 in the example file: day and month are swaped.
However, as soon as this pattern does not “fit” a US date format any more, it uses a European locale:
31-7-2015 is correctly converted to July 31, 2015.
The end result is a workbook with many (but not all) dates incorrect.

This is the Java code we use:
TxtLoadOptions options = new TxtLoadOptions( type );
options.setLocale( new Locale(“nl”, “NL”) );
options.setRegion( CountryCode.NETHERLANDS );
options.setEncoding( Encoding.getUTF8() );
Workbook workbook = new Workbook( stream, options );

We are using Aspose.cells 7.5.2 for Java.
Regards,

Wim Roeling
2

Hi Wim,

Thanks for your posting and using Aspose.Cells.

I have tested this issue with the latest version: Aspose.Cells
for Java v7.7.2.3 and found that Aspose.Cells recognizes both type of values e.g 23-01-2013 and 01-23-2013 as dates. The first value will is treated as dd-mm-yyyy and second value is treated as mm-dd-yyyy.

So locale or regional setting does not have any effect. Could you please test your issue with the above version and let us know your run results.

Java


String filePath = “F:\Shak-Data-RW\Downloads\datetime.csv”;


TxtLoadOptions options = new TxtLoadOptions(LoadFormat.CSV);

options.setLocale( new Locale(“nl”, “NL”) );

options.setRegion( CountryCode.NETHERLANDS );

options.setEncoding( Encoding.getUTF8() );

Workbook workbook = new Workbook( filePath );


Worksheet worksheet = workbook.getWorksheets().get(0);


Cell cellA1 = worksheet.getCells().get(“A1”);

Cell cellB1 = worksheet.getCells().get(“B1”);

Cell cellC1 = worksheet.getCells().get(“C1”);

Cell cellD1 = worksheet.getCells().get(“D1”);


System.out.println(cellA1.getType()==CellValueType.IS_DATE_TIME);

System.out.println(cellB1.getType()==CellValueType.IS_DATE_TIME);

System.out.println(cellC1.getType()==CellValueType.IS_DATE_TIME);

System.out.println(cellD1.getType()==CellValueType.IS_DATE_TIME);


workbook.save(filePath + “.out.xlsx”);

3

Hi Wim,

Thanks for using Aspose.Cells.

We have looked into this issue further and found issue about reading datetime value with nl_NL locale.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40757.

4

Hi Wim,

Thanks for using Aspose.Cells.

We have fixed the issue of reading datetime in csv with nl_NL locale.

Please download and use the latest version: Aspose.Cells
for Java v7.7.2.4 now.

5

Hi,


I tested this fix and it works fine now!
Thanks for the quick response!

Wim Roeling
ADP Amsterdam, The Netherlands
6

Hi,


Thanks for your feedback.

Good to know that your issue is resolved by the new fix/version. We have closed your ticket now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
7

The issues you have found earlier (filed as CELLSJAVA-40757) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.