Write semi structured csv from Azure Data Factory

Question

I've been tasked with writing a CSV file with a certain format - NEM12 (see link below)

MDFF-Specification-NEM12--NEM13-v106.pdf

I'm stumped as to how I could do this in ADF.

The general format has a different number of columns per row, depending on the type of data presented.

100,DMS,AUGUST
200,419215.52,1,1,15,L
300,20200510,20,20,20,20,20,20,15,20,15,20,15,15,20,15,20,15,15,20,20,20,30,30,25,30,25,30,25,25,30,25,30,25,25,30,25,25,30,25,25,25,25,30,25,25,25,25,30,25,25,25,25,30,25,25,25,25,25,30,25,25,25,25,30,25,25,25,25,25,30,25,30,15,15,15,15,15,10,15,15,20,15,15,0,30,15,20,15,20,15,15,20,15,20,20,20,20,A
200,119934.22,1,1,15,L
300,20200510,0,0,0,0,0,0,15,0,15,0,15,15,20,15,20,15,15,20,20,20,30,30,25,30,25,30,25,25,30,25,30,25,25,30,25,25,30,25,25,25,25,30,25,25,25,25,30,25,25,25,25,30,25,25,25,25,25,30,25,25,5,5,30,5,5,5,5,5,0,5,30,15,15,15,15,15,0,15,15,20,15,15,0,30,15,20,15,20,15,15,0,15,0,20,0,0,A
900

I can get the data I need easily enough, but I can't find a way to end up with multiple lines in a CSV with a different number of columns. I tried making multiple files and merged them, however it forces the output to the columns of the first file. I even thought about building up an array of comma-delimited values and writing them as a single column without quotes or escape characters, but I can't seem to write multiple array elements to a single file. Making multiple files just takes me back to the 'columns of the first file' issue.

Any advice on how to proceed would be most helpful.

Thanks

Answer 1

Hi @Peter Sharp ,

Thank you for posting query on Microsoft Q&A Platform.

Azure data factory work on datasets with schema defined it. If your requirement doesn't follow any schema and needs to perform lot of custom values append or getting new rows then consider using Azure functions.

In Azure Functions you can write your own logic in code. You can call that azure functions in side azure data factory pipeline using Azure functions activity.

You can think of Azure Functions similar to Script task in SSIS. But, Azure Functions works on server less fashion. Click here to know more about Azure Functions.

Hope this will help. Thank you.

--------------------------------

• Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
• Want a reminder to come back and check responses? Here is how to subscribe to a notification.