Skip to main content
Solved

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


Johan van Eijsden
Captain
Forum|alt.badge.img+6

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?

Best answer by Anne Buit

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

Anne Buit
Community Manager
Forum|alt.badge.img+5
  • Community Manager
  • May 20, 2020

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?


Johan van Eijsden
Captain
Forum|alt.badge.img+6

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?

 


Anne Buit
Community Manager
Forum|alt.badge.img+5
  • Community Manager
  • May 27, 2020

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


Anne Buit
Community Manager
Forum|alt.badge.img+5
  • Community Manager
  • May 27, 2020

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);

 


Johan van Eijsden
Captain
Forum|alt.badge.img+6

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.


Johan van Eijsden
Captain
Forum|alt.badge.img+6

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!


Anne Buit
Community Manager
Forum|alt.badge.img+5
  • Community Manager
  • May 29, 2020

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


Johan van Eijsden
Captain
Forum|alt.badge.img+6

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

Have a nice weekend!


Johan van Eijsden
Captain
Forum|alt.badge.img+6

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?


Anne Buit
Community Manager
Forum|alt.badge.img+5
  • Community Manager
  • September 15, 2020

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.


Johan van Eijsden
Captain
Forum|alt.badge.img+6

Hi Anne,

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


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings