Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Parse a String (RegEx)

naeema
6 - Meteoroid

I need to parse this String into it's relevant components. There should be 32 fields, i'm not able to develop a RegEx expression which can do it. 

 

START

75154,Joseph Standard,JosephS@comp.com,"{ ""ImageType"": """", ""ServiceType"": """", ""VMName"": """", ""VMProperties"": """", ""UsageType"": ""DataTrOut""}",0.039973,Microsoft.Compute,8,0.00099243802617712,,2014-01-01,-2,Unassigned,/subscriptions/9eec06d5-77e5-4cb9-85a1-5cd8b53edca8/resourceGroups/AKAKAKA-UK-LL/providers/Microsoft.Compute/virtualMachines/UAT2AJumpserver,Virtual Network,bbe2e768-80fd-494f3-b786c-dc4a13bb4e464,Egress,,Peering,Virtual Network Peering - Egress,1017022,AKAKAK-UK-LL,americasouth,134,0.00609918239092,,,,,9eec06d5-77e5-4cb9-85a1-5cd8b53edca8,0,Comp BlueSubscription,"{ ""patching"": ""Group1"", ""environment"": ""Production"", ""applicationnane"": ""DaVinci"", ""businessUnit"": ""IT Innovation"", ""flag"": ""Review - built manually"", ""project"": ""60413""}",GB

END

 

The three commas at the end should have a field but it doesn't since it's null ",,," would be "ok,ok,ok" if they were not null. 

Is anyone able to develop an expression which can extract these fields to get this result (32 fields):

 

START

75154,

Joseph Standard,

JosephS@comp.com,

"{ ""ImageType"": """", ""ServiceType"": """", ""VMName"": """", ""VMProperties"": """", ""UsageType"": ""DataTrOut""}",

0.039973,

Microsoft.Compute,

8,

0.00099243802617712,

,

2014-01-01,

-2,

Unassigned,

/subscriptions/9eec06d5-77e5-4cb9-85a1-5cd8b53edca8/resourceGroups/AKAKAKA-UK-LL/providers/Microsoft.Compute/virtualMachines/UAT2AJumpserver,

Virtual Network,

bbe2e768-80fd-494f3-b786c-dc4a13bb4e464,

Egress,

,

Peering,

Virtual Network Peering - Egress,

1017022,

AKAKAK-UK-LL,

americasouth,

134,

0.00609918239092,

,

,

,

,9eec06d5-77e5-4cb9-85a1-5cd8b53edca8,

0,

Comp BlueSubscription,

"{ ""patching"": ""Group1"", ""environment"": ""Production"", ""applicationnane"": ""DaVinci"", ""businessUnit"": ""IT Innovation"", ""flag"": ""Review - built manually"", ""project"": ""60413""}",

GB

END

 

Please note where there is just a comma, the value in that field will be NULL or empty. 

To be fair the field values won't have a comma at the end but the reason why it is shown for consistency and ease of comparison to the original string.

 

If someone knows a better way to parse this other than RegEx please let me know.

Thanks. 

 

 

Final question does anyone know how to request a particular format of data using the download tool. Currently i am using the download tool in Alteryx and it is viewing the data in one long field, but not in it's own rows and columns. Is it possible to instruct Alteryx somehow to request it already in rows and columns? 

4 REPLIES 4
neilgallen
12 - Quasar

@naeema I don't believe a regex is needed here. All you have described is using the "text to columns tool" set to "Split to rows."

 

I'd add a record ID before this in order to keep the records grouped. The only curveball is the record you have below as 

 

"Tables,Tables - Write Operations," As it contains a comma. Was this a typo in your post?

naeema
6 - Meteoroid

Hi @neilgallen

Sorry my previous String was a bit dodgy,

i've edited my post and posted the new one, please and have a check and tell me if it is still possible,

Thank you. 

neilgallen
12 - Quasar

The same situation still applies, just make sure you have the "ignore delimiters in quotes" option checked.

naeema
6 - Meteoroid

@neilgallen 

Good stuff, thank you mate. 

Labels