Import of CSV for European countries

[mchoudhary]

Hi,

We are using SmartClient version : v9.0p_2013-09-22/PowerEdition Deployment (built 2013-09-22)

We are having a functionality where we are giving an option to download large CSVs containing numbers. This functionality is being used across the world.

Here, we are facing issues with the users of European countries where decimal and comma is used interchangeably and our downloaded CSV for European countries make all the data in one column and more confusing to end users.

Request you to please help us in getting this issue resolved so that our downloaded CSV can be usable for every users.

Let me know, if you need more input.

Thanks a lot

[Isomorphic]

As best as we can tell, you are probably looking for dataSourceField.format/exportFormat.

[mchoudhary]

Thanks.

We are already using exportFormat to get the file exported in CSV. However, is there any method which does formatting of numbers in the downloaded CSV based on locale specific setting.

Ex: 99,123.0 is seen as 99.123,0 in france.

[Blama]

Did you already try FieldType#LOCALEFLOAT in your ds.xml?

I think I remember there is a currently a similar thread open in the forums.

Best regards,
Blama

[mchoudhary]

Thanks for this info.

We will this today and will let you know.

[Isomorphic]

Please re-read our previous reply.

DataSourceField.format/exportFormat is not the same setting as dsRequest.exportFormat.

[mchoudhary]

Reply to Blama's suggestion -

We tried doing the same as shown below but still not working.

Code:

final ListGrid grid = (ListGrid) getMember(1);
DSRequest requestProperties = new DSRequest();
requestProperties.setExportAs(ExportFormat.CSV);
requestProperties.setExportHeader(DVConstants.CSV_EXPORT_SEPERATOR+DVConstants.CSV_EXPORT_DELIMITER+DVConstants.NEWLINE);
requestProperties.setExportDelimiter(DVConstants.CSV_EXPORT_DELIMITER);
requestProperties.setOperationId(gridConfiguration.getFetchOperation());
requestProperties.setExportFilename(getGridHeader().getGridName().getContents());
String exportFooter = createExportFooter();
requestProperties.setExportFooter(exportFooter);
grid.getDataSource().getField("FIELD_WI_RES_REC").setType(FieldType.LOCALEFLOAT);
grid.exportData(requestProperties);


Output- Still not formatted as per the locale.

Here, we have set the type of that field as LOCALEFLOAT in the request as in ds file is having autodriveschema as true and there is no fields defined into it.

Also, my question here, how LOCALEFLOAT handles the locale specific settings. Do we have to do any further step for this which I am missing here ?

[mchoudhary]

Now, reply to Isomorphic.

It seems that the function you are mentioning is not available in v9.0p_2013-09-22/PowerEdition Deployment (built 2013-09-22) and it only has one method named as setExportTitle. Please confirm.

Thanks

[Isomorphic]

Yes, dataSourceField.format is a 9.1 feature.

[mchoudhary]

So just to reiterate the problem statement, we are exporting the numeric data in a CSV file using SmartClient API. By default the exported CSV gets rendered in Excel when user opens it on their local machine. Problem with Excel is that it picks up the locale information on user's machine and then tries to interpret and display numbers as per that locale. This means that for European countries it picks up decimal as separator in the CSV file and comma represents the decimal. This results in data getting separated at decimal levels into new cells which is incorrect. We can workaround this issue by using a specific separator (like ;) instead of the standard comma while exporting the CSV file. However this still leaves us with the problem of locale specific formatting of data e.g. for Norway the excel displays number 8.2014 as aug.14 and so on.

My query is there a way to override this behavior of excel while exporting the CSV using Smartclient API. Is the dataSourceField.format that you have mentioned in version 9.1 solves this problem?

[Isomorphic]

We don't know of a way to influence the behavior of Excel (without installing something at least), but again, dataSourceField.format solves this problem. We highly recommend reading the documentation for this property.

[mchoudhary]

Thanks.

As suggested, we tried to do the format on the datasourcefield as shown below but it is still not formatting as desired.

final ListGrid grid = (ListGrid) getMember(1);
DSRequest requestProperties = new DSRequest();
requestProperties.setExportAs(ExportFormat.CSV);
requestProperties.setOperationId(gridConfiguration.getFetchOperation());
requestProperties.setExportFilename(getGridHeader().getGridName().getContents());
//requestProperties.setExportRawValues(false);
String exportFooter = createExportFooter();
requestProperties.setExportFooter(exportFooter);

for (DataSourceField field : grid.getDataSource().getFields()) {
if (field.getType() == FieldType.FLOAT) {
field.setFormat("#.###,0");
}
}

grid.exportData(requestProperties);

Also, we have explicitly tried to give the following format in the ds file as shown below but it returns an empty CSV.

<field name="FIELD_WI_RES_REC" type="float" format="#.##0,0"></field>

Please let us know, if we are missing something over here.

If we can have a sample code for the same then it would be of great help.

[mchoudhary]

Also, we have tried to use LOCALEFLOAT with 9.1 as well by inheriting i18n in gwt.xml as shown below -

<inherits name="com.google.gwt.i18n.I18N"/>

<extend-property name="locale" values="en"/>
<extend-property name="locale" values="fr"/>
<extend-property name="locale" values="de"/>

and defined a field type as LOCALEFLOAT as shown below -

<field name="FIELD_WI_RES_REC" type="localeFloat"/>

Also, we observed during export that field type is still float (Using SOPs) even though it has been defined as localeFloat in ds.xml

for (DataSourceField field : grid.getDataSource().getFields()) {
System.out.println(field.getName()+"-"+field.getType());
}

This is also not working. Please help.

[Isomorphic]

Like all properties that affect server-side behavior, DataSourceField.format must be set in the .ds.xml file, not via JavaScript.

GWT translates your Java code into JavaScript that is run in the browser. The Java code that you write will not affect server behavior.

[mchoudhary]

Thanks.

Request you to please see the reply which we have tried by mentioning it in ds.xml (mentioned in the previous reply) but rementioning it over here -

Also, we have explicitly tried to give the following format in the ds file as shown below but it returns an empty CSV.

<field name="FIELD_WI_RES_REC" type="float" format="#.##0,0"></field>


Which will also mean that we cannot change the format at runtime. If we can, please let me know API for the same please.