Export to Excel regional issue

[bryanl641]

If our European office has their Windows regional settings set properly for their location the Export to Excel option just dumps the csv file into Excel as plain text, not split into columns. We have to override the Windows region settings to "List separator=," and also the decimal and thousands separators back to US style 1,234.56 instead of their preference of 1.234,56 for the export to excel to work properly.

I also found that adding a line at the top of the csv file with "sep=," lets Excel open the profound ui csv files properly.

Can the grid widget be updated to allow specification of the field separator? Or maybe provide a setting so that the "sep=," line is included in the downloaded csv file? Or is there a way to accommodate this already?

--Bryan

[Glenn]

Bryan,

Until we can look into this in more detail, I may have found a workaround.

There is a setting available in Profound UI to change the separator used for the CSV export (see here: http://www.profoundlogic.com/docs/displ ... +Separator). After doing some research it appears that the European versions of Excel expect a semi-colon as the separator. Of course, you probably wouldn't want to change it for everyone...

You can change this property via JavaScript at any time prior to the user clicking the 'export' option. I wrote some quick JavaScript that uses a bound RPG field to decide which 'locale' to use and switches the separator appropriately (this code could be modified to retrieve the locale information from the browser instead, of course the browser might not have the same setting as their Windows config). I assigned this function to the onload property of the record format. When I change the RPG field between 'Europe' and 'US' I get the specified separator.

Code: Select all

function setCSVSep() {
	
	var regionValue = getElementValue("regionField");
	
	if (regionValue == 'Europe') {
		pui["csv separator"] = ";";
	} else {
		pui["csv separator"] = ",";
	}

}

I don't have an easy way to test this with a European configured PC so YMMV.

Glenn

[bryanl641]

Thanks. I'll give this a try next week!

[bryanl641]

That solution almost works, but for one initial character entity we always get when we use the Export to Excel link.

Every export begins with this: 
So when I add the ability to say the semi-colon is the CSV separator, we get &#65279 as the first column heading, causing a column mis-alignment. For our non-European PCs, we just have that character entity showing up *with* the actual column heading for column A.

Is there any way to prevent  from being included in the Export to Excel? I don't see it in the HTML when I inspect with FireBug or Chrome Developer tools.

--Bryan

[Scott Klement]

I think what you're seeing here is the Byte Order Mark. We deliberately add this so that Windows will see the file as Unicode... programs like Notepad will otherwise tread the file as ASCII instead of UTF-8, which of course would cause us to lose tons of the international characters.

But, it shouldn't be coming back escaped as an HTML entity. After all, this is a CSV file not an HTML file! It should just be hex FEFF at the start of the file... a byte order mark.

[bryanl641]

When I click on "Export to Excel" and save the file, then open it in notepad, the first line is this:
"BillTo","Customer name","CSR","Nbr Regions","Stores in Regions"

--Bryan

[Scott Klement]

Hmm... I can't seem to get it to do that... I tried the current versions of Firefox, Chrome and Internet Explorer, all seem to just put out the hex value (not the HTML entity.)

Does this happen to you in a particular browser? Or all of them? Does it only happen if you set the csv separator?

Trying to figure out how I can reproduce it...

[bryanl641]

For me I get the entity code on every browser, every client platform. That makes me think it's a server setting. I should try it from the profoundui http instance and then (assuming it doesn't have the same issue) look for settings that differ from our other instances. I'll let you know how it goes.

Thanks!
Bryan

[bryanl641]

I found that an Apache config statement was missing from our instance config: DefaultNetCCSID 1208. After adding this, I no longer get the html entity when I export from Excel. Provided this doesn't cause any issues with any of our other applications I'll make the same change in our production instance.

Then it seems I can just add a checkbox to allow the user to toggle the separator, unless I can find a way to read the windows configuration.

[ ] Use semi-colon separator

Code: Select all

Onclick:
if (this.checked) {
      pui["csv separator"] = ";";
   } else {
      pui["csv separator"] = ",";
} 

--Bryan

[Glenn]

I found a couple of links that might help. They show usage of navigator.language. Note that I believe these all return the setting from the browser, not the OS.

http://www.w3schools.com/js/js_window_navigator.asp
https://alicoding.com/detect-browser-la ... avascript/

Glenn