Insert square brackets and double quotes to a string

Hello Alteryx Community,

My current output is looking like this in JSON format as shown below:

Current Output 1
"Name": "AAA","BBB","CCC"

Current Output 2
"Name":""

Current Output 3
"Name":"HHH"

The expected output should be like as shown below:

Expected Output1
"Name": ["AAA","BBB","CCC"]

Expected Output2
"Name": [""]

Expected Output3
"Name": ["HHH"]

Thank you!

Output

Custom Tools

Parse

API

Dynamic Processing

Preparation

Accepted answers

All comments

CathyS_Slalom

Hi @spuri5, If it always start with "Name": you can use the formula below

spuri5

Hello Cathy,

Thank you for the reply,

But the Name string value is not always 3 digits.

Please advise!

CathyS_Slalom

Hi @spuri5 , if that's the case, please use the formula below (locate the : to get the dynamic name length):

ReplaceFirst([Old], left([Old],FindString([Old], ":")+1), left([Old],FindString([Old], ":"))+ ":[") + "]"

binu_acs

@spuri5 Another way of dong this with the RegexReplace formula

Replace_07_06.yxmd

spuri5

Hi Cathy,

With the below formula, the output I am getting:

Current Output 1

"Name": "AAA,BBB,CCC]",

"Name":"]",

"Name":"HHH]",

But the expected output should be as shown below:

Expected Output1
"Name": ["AAA","BBB","CCC"],

"Name": [""],

"Name": ["HHH"],

spuri5

Hi @binu_acs ,

With the below formula, the output I am getting:

Current Output 1

"Name": "AAA,BBB,CCC:[AAA,BBB,CCC]",

"Name":":[]",

"Name":"HHH:[HHH]",

But the expected output should be as shown below:

Expected Output1

"Name": ["AAA","BBB","CCC"],

"Name": [""],

"Name": ["HHH"],

CathyS_Slalom

Hi @spuri5 If your input data is matching the pattern that under Old column, and you use the function in the picture, you should be able to see the expected output as shown under the "New" column. Could you make sure that you applied this new formula after your previous/current output that you listed below?

binu_acs

@spuri5 as @CathyS_Slalom said the regex formula works based on your given input file and its works for me. Can you provide the input file which you are trying with the formula? output as per your given input

spuri5

Hi @CathyS_Slalom and @binu_acs ,

Sorry for the confusion, Please find attached the actual sample Input file.

Expected output as shown below:

Input.xlsx

Matt_D

Hi @spuri5 I'm not 100% sure what you're after here but if you're looking to turn your Excel data into JSON, use the JSON Build Tool

Is this what you're after?

If it's just the specific strings highlighted then I would parse to rows using the comma then use the concatenate function in the Summary Tool to bring it back together, that way it's dynamic.

JSON Build Tool or Concatenate.yxzp

CathyS_Slalom

Hi @spuri5, not sure what steps that you've done before you need insert brackets, could you share a sample workflow for us to work on?

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177