Export to Excel regional issue

Use this board to ask questions or have discussions with other Rich Displays users.
Post Reply
bryanl641
New User
Posts: 9
Joined: Thu Sep 18, 2014 11:55 am
First Name: Bryan
Last Name: Leaman
Company Name: InVue Security Products
Phone: 704-752-6513 x1221
Address 1: 15015 Lancaster Hwy
City: Charlotte
State / Province: North Carolina
Zip / Postal Code: 28277
Country: United States
Contact:

Export to Excel regional issue

Post by 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
User avatar
Glenn
Profound Logic Staff Member
Posts: 124
Joined: Mon Apr 14, 2014 4:08 pm
First Name: Glenn
Last Name: Hopwood
Company Name: Profound Logic Software
State / Province: Ohio
Country: United States
Contact:

Re: Export to Excel regional issue

Post by 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
New User
Posts: 9
Joined: Thu Sep 18, 2014 11:55 am
First Name: Bryan
Last Name: Leaman
Company Name: InVue Security Products
Phone: 704-752-6513 x1221
Address 1: 15015 Lancaster Hwy
City: Charlotte
State / Province: North Carolina
Zip / Postal Code: 28277
Country: United States
Contact:

Re: Export to Excel regional issue

Post by bryanl641 »

Thanks. I'll give this a try next week!
bryanl641
New User
Posts: 9
Joined: Thu Sep 18, 2014 11:55 am
First Name: Bryan
Last Name: Leaman
Company Name: InVue Security Products
Phone: 704-752-6513 x1221
Address 1: 15015 Lancaster Hwy
City: Charlotte
State / Province: North Carolina
Zip / Postal Code: 28277
Country: United States
Contact:

Re: Export to Excel regional issue

Post by 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
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: Export to Excel regional issue

Post by 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
New User
Posts: 9
Joined: Thu Sep 18, 2014 11:55 am
First Name: Bryan
Last Name: Leaman
Company Name: InVue Security Products
Phone: 704-752-6513 x1221
Address 1: 15015 Lancaster Hwy
City: Charlotte
State / Province: North Carolina
Zip / Postal Code: 28277
Country: United States
Contact:

Re: Export to Excel regional issue

Post by 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
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: Export to Excel regional issue

Post by 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
New User
Posts: 9
Joined: Thu Sep 18, 2014 11:55 am
First Name: Bryan
Last Name: Leaman
Company Name: InVue Security Products
Phone: 704-752-6513 x1221
Address 1: 15015 Lancaster Hwy
City: Charlotte
State / Province: North Carolina
Zip / Postal Code: 28277
Country: United States
Contact:

Re: Export to Excel regional issue

Post by 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
New User
Posts: 9
Joined: Thu Sep 18, 2014 11:55 am
First Name: Bryan
Last Name: Leaman
Company Name: InVue Security Products
Phone: 704-752-6513 x1221
Address 1: 15015 Lancaster Hwy
City: Charlotte
State / Province: North Carolina
Zip / Postal Code: 28277
Country: United States
Contact:

Re: Export to Excel regional issue

Post by 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
User avatar
Glenn
Profound Logic Staff Member
Posts: 124
Joined: Mon Apr 14, 2014 4:08 pm
First Name: Glenn
Last Name: Hopwood
Company Name: Profound Logic Software
State / Province: Ohio
Country: United States
Contact:

Re: Export to Excel regional issue

Post by 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
Post Reply

Who is online

Users browsing this forum: Bing [Bot] and 1 guest