Special Character found in downloaded Analytic CSV report when opened in Excel

[Ran]

See the attached Analytics CSV (SpecialCharacter.csv) and the screenshot where it comes from (01-02).  Notice in column F (ROLE_1) we are seeing a special character in the Analytics downloaded CSV for open conflicts when opened in Excel.  This is not seen in the UI or in the Excel download but only in the CSV download of the report.  We do not know where it is coming from and how to stop it from happening as it is not permitting import to an external system for user access.  See the Excel downloaded file attached as ZIP for comparison.  Has anyone seen this and if so then how can this be resolved?

Note1 - When the CSV is opened in Sublime text we found that the special character is actually zero width space (see PNG screenshot). This is causing failure in the system that is consuming the CSV. Please advise for resolution to have this removed from the downloaded CSV.

Note2 - Role Names in the analytics report are extracted using custom properties which got populated post task completion for the application. These roles are entitlements in Saviynt.

[This message has been edited by moderator to mask sensitive information from attached files]

[rushikeshvartak]

Its from code raise support ticket

[Ran]

What does that mean "Its from code raise support ticket"?  This has nothing to do with a ticket.  It's found in an Analytics Report downloaded as CSV and opened in Excel.  Nothing to do with a ticket.  Please help me understand your response.

[rushikeshvartak]

Excel is generated from product code and this are additional code is getting added

[Ran]

I could accept that as a possible issue however then why isn't any other data in any of the other columns being affected?  This can't be a prejudiced issue that affects data in only column F?  Never mind that, this is the only Analytics Report that does this.  And then what is the solution to stop this from happening in this CSV download?

[rushikeshvartak]

You need to raise support ticket . As product code as csv generation logic

[Ran]

I appreciate your input.  Although we have opened a ticket as suggested, I am not certain this is that kind of issue -- it's happening to the data in only one field so makes me believe there is something else going on here.

I wish to leave this question open for a while longer for others to reply -- especially those who have witnessed the same anomaly -- and what they did as a workaround.

[rushikeshvartak]

Share analytixs query to replicate in v24.8

[Ran]

We are using entitlement(as dynamic attribute) in ars form which is getting stored in these customproperties giving special character issue.

SELECT u.username as USERNAME, u.email as EMAIL, u.firstname as FIRST_NAME, u.lastname as LAST_NAME, 'YES' as ACTIVE , a.customproperty1 as ROLE_1, a.customproperty2 AS ROLE_1_DATA_ACCESS, 'Dataaccess' as ROLE_1_DATA_ACCESS_LEVEL, a.customproperty4 as ROLE_2, a.customproperty5 AS ROLE_2_DATA_ACCESS, CASE WHEN TRIM(a.customproperty4) IS NULL or TRIM(a.customproperty4) ='' THEN '' ELSE 'Dataaccess' END as ROLE_2_DATA_ACCESS_LEVEL, a.customproperty7 as ROLE_3, a.customproperty8 AS ROLE_3_DATA_ACCESS, CASE WHEN TRIM(a.customproperty7) IS NULL or TRIM(a.customproperty7) ='' THEN '' ELSE 'Dataaccess' END as ROLE_3_DATA_ACCESS_LEVEL from users u join user_accounts ua on ua.userkey=u.userkey join accounts a on a.accountkey=ua.accountkey where u.statuskey=1 and a.status in ('Manually Provisioned') and a.endpointkey=6603 union SELECT u.username as USERNAME, u.email as EMAIL, u.firstname as FIRST_NAME, u.lastname as LAST_NAME, 'NO' as ACTIVE , a.customproperty1 as ROLE_1, a.customproperty2 AS ROLE_1_DATA_ACCESS, 'Dataaccess' as ROLE_1_DATA_ACCESS_LEVEL, a.customproperty4 as ROLE_2, a.customproperty5 AS ROLE_2_DATA_ACCESS, CASE WHEN TRIM(a.customproperty4) IS NULL or TRIM(a.customproperty4) ='' THEN '' ELSE 'Dataaccess' END as ROLE_2_DATA_ACCESS_LEVEL, a.customproperty7 as ROLE_3, a.customproperty8 AS ROLE_3_DATA_ACCESS, CASE WHEN TRIM(a.customproperty7) IS NULL or TRIM(a.customproperty7) ='' THEN '' ELSE 'Dataaccess' END as ROLE_3_DATA_ACCESS_LEVEL from users u join user_accounts ua on ua.userkey=u.userkey join accounts a on a.accountkey=ua.accountkey where u.statuskey=1 and a.status in ('Manually Suspended') and a.endpointkey=6603

[rushikeshvartak]

SELECT
u.username as USERNAME,
u.email as EMAIL,
u.firstname as FIRST_NAME,
u.lastname as LAST_NAME,
'YES' as ACTIVE,
REPLACE(a.customproperty1, CHAR(8203), '') as ROLE_1,
a.customproperty2 AS ROLE_1_DATA_ACCESS,
'Dataaccess' as ROLE_1_DATA_ACCESS_LEVEL,
a.customproperty4 as ROLE_2,
a.customproperty5 AS ROLE_2_DATA_ACCESS,
CASE
WHEN TRIM(a.customproperty4) IS NULL or TRIM(a.customproperty4) = '' THEN ''
ELSE 'Dataaccess'
END as ROLE_2_DATA_ACCESS_LEVEL,
a.customproperty7 as ROLE_3,
a.customproperty8 AS ROLE_3_DATA_ACCESS,
CASE
WHEN TRIM(a.customproperty7) IS NULL or TRIM(a.customproperty7) = '' THEN ''
ELSE 'Dataaccess'
END as ROLE_3_DATA_ACCESS_LEVEL
FROM users u
JOIN user_accounts ua ON ua.userkey = u.userkey
JOIN accounts a ON a.accountkey = ua.accountkey
WHERE u.statuskey = 1
AND a.status IN ('Manually Provisioned')
AND a.endpointkey = 6603

UNION

SELECT
u.username as USERNAME,
u.email as EMAIL,
u.firstname as FIRST_NAME,
u.lastname as LAST_NAME,
'NO' as ACTIVE,
REPLACE(a.customproperty1, CHAR(8203), '') as ROLE_1,
a.customproperty2 AS ROLE_1_DATA_ACCESS,
'Dataaccess' as ROLE_1_DATA_ACCESS_LEVEL,
a.customproperty4 as ROLE_2,
a.customproperty5 AS ROLE_2_DATA_ACCESS,
CASE
WHEN TRIM(a.customproperty4) IS NULL or TRIM(a.customproperty4) = '' THEN ''
ELSE 'Dataaccess'
END as ROLE_2_DATA_ACCESS_LEVEL,
a.customproperty7 as ROLE_3,
a.customproperty8 AS ROLE_3_DATA_ACCESS,
CASE
WHEN TRIM(a.customproperty7) IS NULL or TRIM(a.customproperty7) = '' THEN ''
ELSE 'Dataaccess'
END as ROLE_3_DATA_ACCESS_LEVEL
FROM users u
JOIN user_accounts ua ON ua.userkey = u.userkey
JOIN accounts a ON a.accountkey = ua.accountkey
WHERE u.statuskey = 1
AND a.status IN ('Manually Suspended')
AND a.endpointkey = 6603;

[Ran]

Thank you for the valiant effort, but that did not resolve the issue either.  The strange/special character is still showing itself in the same place and thusly the CSV download from Analytics is failing import into the external system.

[rushikeshvartak]

Do you see special character in customproperty ?

[Ran]

It is in the database, validated by using data analyzer.

[rushikeshvartak]

So its issue when request is raised its get added by code in cp or is this imported using import sheet?

[Ran]

Request is submitted, approved
WSRetry job is run
Analytics job is run
External system fails to consume CSV due to this issue

[rushikeshvartak]

customproperty1 is populated from request / via import sheet ?

[Ran]

customproperty1 comes from the request

[rushikeshvartak]

Share the below results from data analyzer and analytics preview 

Query :

select a.name,a.customproperty1, REPLACE(a.customproperty1, CHAR(8203), '') as ROLE_1 from accounts where a.status IN ('Manually Suspended') AND a.endpointkey = 6603 

[Ran · Accepted solution]

While I was on holiday the developer determined the issue was bad data the way it stored in the DB.  They recreated the data and then it was clean - no special characters.  Thanks for the help trying to figure out a work-around.  This support question can be closed.