Solved

The character "&" is exported to Excel as "&"


Userlevel 1
Badge +4

When exporting data to Excel the character “&” is being shown as “&”. This is done by the button “Exporteren” in the toolbar of the Windows GUI.

 

 

Can this be fixed please?

icon

Best answer by Anne Buit 29 May 2020, 09:07

Hi Johan,

This does resolve the problem with the ampersand. However, you might run into a few more problems with other characters being escaped, such as greater-than and lower-than.

The following solution would prevent any xml escaping from ending up in the final text:

STUFF(
(SELECT x.y.value('.', 'NVARCHAR(MAX)')
FROM (
SELECT ', ' + x.naam
FROM oplosser o
JOIN organisatie x ON x.guid = o.organisatie_guid
WHERE o.melding_guid = t1.guid
ORDER BY x.naam
FOR XML PATH (''), type
) x(y)
), 1 , 2 , '')

Edited: casing of .value

View original

11 replies

Userlevel 6
Badge +4

Hi Johan,

When I open the Software Factory and I filter the translations on translations containing the character & and use the export, no such problems occur, not with XLS or XLSX.

Are you certain a calculated field or a view being exported doesn’t use a for-xml-path construction to aggregate some text, causing this issue?

Userlevel 1
Badge +4

Hi Anne,

Sorry for my late response. I had to get more information from our customer about this issue. After some research I saw that the column that is exported with the text “&” in it is indeed using a for-xml-path construction. This column is an expression:

STUFF((
SELECT ' ,' + x.naam 
FROM   oplosser o
JOIN   organisatie x ON x.guid = o.organisatie_guid
WHERE  o.melding_guid = t1.guid
ORDER BY x.naam
FOR XML PATH ('')), 1 , 2 , '')


How can I prevent that this will show like this?

 

Userlevel 6
Badge +4

Are you by chance using SQL Server 2017 or later? The string_agg function would be perfect for this.

Userlevel 6
Badge +4

For earlier versions of SQL Server you could do something like this:

declare @data table (item nvarchar(100));

insert into @data values (N'test1');
insert into @data values (N'test2');
insert into @data values (N'test3');
insert into @data values (N'test4');
insert into @data values (N'test5 & more');


select
x.y.value('.', 'nvarchar(max)')
from
(
select
iif(row_number() over (order by item) = 1, '', ', ') + item
from @data
order by item
for xml path (''), type
) x (y);

 

Userlevel 1
Badge +4

We have to support multiple SQL versions because we deliver a standard solution for multiple clients. The minimum version that we support is SQL 2012.

Thank you for your suggestions. I’ll try your last solution and let you know if that works for me.

Userlevel 1
Badge +4

Heb de expressie van het veld uiteindelijk als volgt aangepast:

REPLACE(
STUFF((
SELECT ', ' + x.naam 
FROM   oplosser o
JOIN organisatie x ON x.guid = o.organisatie_guid
WHERE o.melding_guid = t1.guid
ORDER BY x.naam
FOR XML PATH ('')), 1 , 2 , ''),
'&', '&')


Toen werkte het bij het exporteren.

Bedankt voor je hulp Anne!

Userlevel 6
Badge +4

Hi Johan,

This does resolve the problem with the ampersand. However, you might run into a few more problems with other characters being escaped, such as greater-than and lower-than.

The following solution would prevent any xml escaping from ending up in the final text:

STUFF(
(SELECT x.y.value('.', 'NVARCHAR(MAX)')
FROM (
SELECT ', ' + x.naam
FROM oplosser o
JOIN organisatie x ON x.guid = o.organisatie_guid
WHERE o.melding_guid = t1.guid
ORDER BY x.naam
FOR XML PATH (''), type
) x(y)
), 1 , 2 , '')

Edited: casing of .value

Userlevel 1
Badge +4

Yes, this works great! I had some troubles implementing it for my specific situation, so thank you for this example!

Have a nice weekend!

Userlevel 1
Badge +4

Hi Anne,

I really don't get it. It worked fine before, but when opening subject “melding” now, it results in an error:

************************************
* Thinkwise Software Error Message *
************************************

Datum: 2-6-2020 23:25:05
GUI: 2019.2.17.0
.NET Runtime: 4.0.30319.42000

Project: JP_Bouwmanagement (6.3)
Softwarefabriek: JPDS_MOD_2018_3
Taal: NL

"VALUE" is not a valid function, property, or field.

SQL:
Error code: 227
select t1.[guid], t1.[melding_nummer], t1.[oplossers_lijst]
from (
select t1.[guid], t1.[melding_nummer], (STUFF(
    (SELECT x.y.VALUE('.', 'NVARCHAR(MAX)')
     FROM (
        SELECT ', ' + x.naam 
        FROM   oplosser o
        JOIN organisatie x ON x.guid = o.organisatie_guid
        WHERE o.melding_guid = t1.guid
        ORDER BY x.naam
        FOR XML PATH (''), type
        ) x(y)
    ), 1 , 2 , ''))
 as [oplossers_lijst]

from [melding] as t1
) as t1
order by t1.[melding_nummer] desc

 

I run this with a Windows GUI 2019.2.17.0 on SQL Server 2016 (13.0.1742).


What is happening? Can you please help me out here?

Userlevel 6
Badge +4

Hi Johan, 

Sorry for the late reply.

The reason seems to be that x.y.value should be used instead of x.y.VALUE. I’m not entirely certain why this case-sensitivity is in place and in which versions of SQL Server this is the case.

Userlevel 1
Badge +4

Hi Anne,

Okay, I’ll try this later. Thank you for your reply.

Reply