Export to Excel regional issue
-
- 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
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
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
- 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
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.
I don't have an easy way to test this with a European configured PC so YMMV.
Glenn
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"] = ",";
}
}
Glenn
-
- 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
Thanks. I'll give this a try next week!
-
- 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
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  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
Every export begins with this: 
So when I add the ability to say the semi-colon is the CSV separator, we get  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
-
- 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
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.
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.
-
- 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
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
"BillTo","Customer name","CSR","Nbr Regions","Stores in Regions"
--Bryan
-
- 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
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...
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...
-
- 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
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
Thanks!
Bryan
-
- 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
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
--Bryan
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"] = ",";
}
- 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
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
http://www.w3schools.com/js/js_window_navigator.asp
https://alicoding.com/detect-browser-la ... avascript/
Glenn
Who is online
Users browsing this forum: Bing [Bot] and 0 guests