Undesired characters in the string output
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi everyone,
I'm working with a JSON file in Alteryx that needs cleaning and parsing. Everything seems fine within Alteryx, but when I export the data as a .csv file (in UTF-8 format), I encounter issues with extra double quotes ("), slashes (/), and newline characters (\n). Here’s an example to illustrate what’s happening:
Current Output:
{
"type": "radio",
"id": "67",
"formId": "2052",
"label": "Wie war dein Familienstand im Jahr @{F:1}?",
"adminLabel": "",
"isRequired": "1",
"errorMessage": "",
"visibility": "visible",
"choices": [
{
"text": "ledig",
"value": "1",
"isSelected": "0",
"tooltipContent": "{\"title\":\"Ledig\",\" content\":\"\n<p>Dieser Status bezieht sich auf Personen, die am Ende des Jahres weder verheiratet, verwitwet noch geschieden waren. Bei Vorliegen einer dieser Bedingungen ist die Einstufung als \"ledig\" nicht möglich.</p>\n\"}",
Desired Output:
"type": "radio",
"id": 67,
"formId": 2052,
"label": "Wie war dein Familienstand im Jahr @{F:1}?",
"adminLabel": "",
"isRequired": "1",
"errorMessage": "",
"visibility": "visible",
"choices": [
{
"text": "ledig",
"value": "1",
"isSelected": "0",
"tooltipContent": {"title":"Ledig"," content":"<p>Dieser Status bezieht sich auf Personen, die am Ende des Jahres weder verheiratet, verwitwet noch geschieden waren. Bei Vorliegen einer dieser Bedingungen ist die Einstufung als "ledig" nicht möglich.</p>"}.
As you can see, I want to remove unnecessary quotes around certain numeric values (BUT not all of them - only "id" and "formdID")and properly format the tooltipcontent without escape characters. Although I've used the Replace tool and StripQuotes in Alteryx to clean the data, these unwanted characters still appear in the final output.
So yes, certain pairs - like form ID and ID should not have "". I was able to clean the data in alteryx with replace tool, but I after the file will be written I got this undesired charachets.
I thried to use /t and /0 as delimiters. Quote output fields is "Never" but it is still not working.
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Here is an file thet could be used as an example
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Can you export the workflow? It's a bit hard to visualize and help without it...
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hmm, well because I'm not hooked up to your system, I can't see/check everything. But if you're facing the extra quotes in the numeric portions, you can try something like this:
-
Find:
"(id|formId)": "(\d+)"
-
Replace:
"\1": \2
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks but this won´t help, as the problem occurs only when Alteryx prepares the output file - means in the Output tool.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@DFstr Try the attached workflow