question

PeterSharp-4330 avatar image
0 Votes"
PeterSharp-4330 asked ShaikMaheer-MSFT commented

Write semi structured csv from Azure Data Factory

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





azure-data-factory
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm now thinking that this is something ADF cannot do.

I'll probably start looking into taking this back on-prem and use SSIS to produce the file with a script task and send it back to the data lake with AzCopy.

Any insights or alternatives would be awesome.

Thanks,

0 Votes 0 ·

Hi @PeterSharp-4330 ,

Following up to check is below provided answer helps. If yes, Please Accept Answer. Accepting answer will help community too.

Feel free to ask if any further queries. Thank you.

0 Votes 0 ·

Hi @PeterSharp-4330 ,

Just checking is below provided answer helps. If yes, Please Accept Answer. Accepting answer will help community too.

0 Votes 0 ·

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT edited

Hi @PeterSharp-4330 ,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.