Solved

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

Johan van Eijsden
Rank
Userlevel 2
Badge +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?

icon

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

View original

11 replies

Anne Buit
Rank
Userlevel 6
Badge +5

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
Rank
Userlevel 2
Badge +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
Rank
Userlevel 6
Badge +5

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

Anne Buit
Rank
Userlevel 6
Badge +5

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
Rank
Userlevel 2
Badge +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
Rank
Userlevel 2
Badge +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
Rank
Userlevel 6
Badge +5

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
Rank
Userlevel 2
Badge +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
Rank
Userlevel 2
Badge +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
Rank
Userlevel 6
Badge +5

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
Rank
Userlevel 2
Badge +6

Hi Anne,

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

Reply


Terms & ConditionsCookie settings