How to split a single string into multiple columns in Kusto?

Question

I am getting data as a single string. For example, my one record looks like - 2020/01/01 "Anna Thomas" 21.(Note: fields are space separated). I want them in a Kusto table with schema Date, Name, and Age. I read about first inserting them to a source table and then ingesting into Target Table using the update policy. But how will I parse this type of records (thought of using splitting the record based on single space but then the name also gets splitted)?

Date	Name	age
2020/01/01	Anna Thomas	21

Are all the fields quoted? Can you add an example for a line or more with datatable()? — Avnera

Slavik N Slavik N · Accepted Answer · 2021-06-22T06:18:25

You should use the parse operator:

let MyTable = datatable(str:string) [
    '2020-01-01 "Anna Thomas" 21',
    '2020-01-05 "Slavik Kusto" 32'
];
MyTable
| parse str with Timestamp:datetime ' "' Name:string '" ' Age:long
| project-away str

Output:

Timestamp	Name	Age
2020-01-01 00:00:00.0000000	Anna Thomas	21
2020-01-05 00:00:00.0000000	Slavik Kusto	32

Note: I changed the / to - in your timestamps, in order for the string to comply with the ISO 8601 datetime format that is required by Kusto. If you have no control over the input data, then you can do the replace as part of the Kusto query, like this:

let MyTable = datatable(str:string) [
    '2020/01/01 "Anna Thomas" 21',
    '2020/01/05 "Slavik Kusto" 32'
];
MyTable
| parse str with Timestamp:string ' "' Name:string '" ' Age:long
| extend Timestamp = todatetime(replace("/", "-", Timestamp))
| project-away str

Output:

Timestamp	Name	Age
2020-01-01 00:00:00.0000000	Anna Thomas	21
2020-01-05 00:00:00.0000000	Slavik Kusto	32

How to split a single string into multiple columns in Kusto?

1 Answers