ASCII Control Characters In Data With Scribe Online
Article ID: KB0078770
Updated On:
| Products | Versions |
|---|---|
| TIBCO Cloud Integration - Connect ( Scribe ) | - |
Description
Scribe Online currently has an issue with source data containing some ASCII control characters with values less than hex 0x20 (see http://en.wikipedia.org/wiki/ASCII). In general, Scribe Online can replicate or synchronize this data, but when a record with one of these characters fails for some reason, Scribe Online may be unable to display the row properly in the Execution History. Also, if you try to preview source data containing these characters, the system may look like it is loading the preview data continuously until you cancel the operation.
Finally, systems that have APIs based on XML such as Salesforce.com and Microsoft Dynamics CRM are unable to accept characters in this low range. With systems that use these characters as a way to represent null, there is a technique to work around it.
If Salesforce or CRM are your target and you try to integrate data with these control characters in it, the data is not be allowed to go into these systems. This is because these control characters are not compatible with XML and the API that you used to connect with Salesforce and CRM need XML compatible characters. Some systems use these control characters to represent null. We've seen the ASCII character that represents Start of Text used. On the ASCII chart, this character is Decimal 2 or Hex 002 (sometimes shown as Hex 0x02). You can use the CLEAN function on a mapped field to strip out this character. For example, if the field in your source data that is giving you the problem is CUSTOMER.FAX, set up a formula on your mapped field that looks like this:
CLEAN takes a string expression as input and returns the expression with all non-printable characters removed, effectively turning the string into null if all that was in the string was one of these control characters.
If you need to integrate data from Salesforce or CRM back into the other system, and that other system wants to store null as a control character, you can use a formula with an escape sequence to turn the null into the control character.
Assume you are working with Dynamics CRM as your source and you are still working with the fax field. Use a formula like this to check for null and if it is null, set a value to the control character you want, in this case Hex 0x02. If the value is not null, then this formula passes along whatever the value is without changing it.
See the following for more information:
Finally, systems that have APIs based on XML such as Salesforce.com and Microsoft Dynamics CRM are unable to accept characters in this low range. With systems that use these characters as a way to represent null, there is a technique to work around it.
If Salesforce or CRM are your target and you try to integrate data with these control characters in it, the data is not be allowed to go into these systems. This is because these control characters are not compatible with XML and the API that you used to connect with Salesforce and CRM need XML compatible characters. Some systems use these control characters to represent null. We've seen the ASCII character that represents Start of Text used. On the ASCII chart, this character is Decimal 2 or Hex 002 (sometimes shown as Hex 0x02). You can use the CLEAN function on a mapped field to strip out this character. For example, if the field in your source data that is giving you the problem is CUSTOMER.FAX, set up a formula on your mapped field that looks like this:
CLEAN(CUSTOMER.FAX)
CLEAN takes a string expression as input and returns the expression with all non-printable characters removed, effectively turning the string into null if all that was in the string was one of these control characters.
If you need to integrate data from Salesforce or CRM back into the other system, and that other system wants to store null as a control character, you can use a formula with an escape sequence to turn the null into the control character.
Assume you are working with Dynamics CRM as your source and you are still working with the fax field. Use a formula like this to check for null and if it is null, set a value to the control character you want, in this case Hex 0x02. If the value is not null, then this formula passes along whatever the value is without changing it.
IF(ISNULL( account.fax ), $"\u0002", account.fax)
See the following for more information:
- What character escape sequences are available in C#?
- Scribe Blog: Scribe Online Tips and Tricks: Using Escape Characters with Scribe Online Integration Services
Issue/Introduction
Issues with Scribe Online and source data containing some ASCII control characters and possible workarounds using formulas.
Additional Information
Feedback
Yes
No
Powered by
