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?
Solved! Go to Solution.
@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?
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.
The same situation still applies, just make sure you have the "ignore delimiters in quotes" option checked.
Good stuff, thank you mate.