convert csv file to json file without using Copy Activity (Azure Data Factory)
convert csv file to json file.
How to convert json file format without using Copy Activity.
Basically, You shuld use Copy Activity to convert json. But If you want to do something detailed, you can manage it by using Lookup, ForEach, SetVariable Activities.
In this case, You should manage to escape strings such liken “\n”, “\r”, “\”….
1.csv file on blob storage
"name01","value01"
"name02","value02"
2.json file after converted
{
"records" : [
{ "id" : "name01", "info" : "value01"},
{ "id" : "name02", "info" : "value02"}
]
}
3. pileline
3.1. pileline variables
BUFFER_V1_1 String
BUFFER_V1_2 String
BUFFER_V1_3 String
3.2. pileline structure
3.3. Lookup1 (Lookup)
- SRC …. set src csv file
- Uncheck “First row only”
3.4. ForEach1 (ForEach)
- Check “sequential”
- Items(Dynamic content)：
@activity('Lookup1').output.value
3.5. Set V1 (Set variable)
- Name：BUFFER_V1_1
- Value(Dynamic content)：
@concat(variable('BUFFER_V1_2'), '{"id":"', ESCAPE(item().Prop_0), '","info":"', ESCAPE(item().Prop_1),'"}')
ESCAPE() is omitted. you shuld write below code.
uriComponentToString(replace(replace(replace(replace(replace(urlComponent(coalesce( *** , ”)),’%5C’,’%5C%5C’), ‘%22′,’%5C%22’), ‘%0D’,’%5Cr’), ‘%0A’, ‘%5Cn’), ‘%09’, ‘%5Ct’))
replace(**, ‘%5C’,’%5C%5C) ….. replace “\” to “\\”
replace(**, ‘%22’,’%5C%22) ….. replace “”” to “\””
replace(‘**’, ‘%0D’,’%5Cr’) …. replace \r to “\r”
replace(‘**’, ‘%0A’,’%5Cn’) …. replace \n to “\n”
replace(‘**’, ‘%09′,’%5Ct’) ….. replace \t to “\t”
3.6. Set V2 (Set variable)
- Name：BUFFER_V1_2
- Value(Dynamic content)：
@concat(variable('BUFFER_V1_1'), ',')
3.7. Set V3 (Set variable)
- Name：BUFFER_V1_3
- Value(Dynamic content)：
@concat(variable(‘BUFFER_V1_3’), ‘{ “records” [‘, variable(‘BUFFER_V1_1′),’}’)
■Related article
convert csv to json with using Copy Activity(Azure Data Factory)