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?
