Other ways to convert CSV to JSON (more performance)

Question

Hello everybody,

I am using an Azure Logic app to convert a CSV file to JSON to transform the data into a SharePoint Online list.

I am using this exact same approach as described here: https://sergeluca.wordpress.com/2018/10/28/microsoft-flow-advanced-tutorial-creating-a-csv-converter-from-scratch/

It works, but it is so incredibly slow.
I am using a CSV file that has 2.481 rows and 12 columns.
The Logic app takes 6 hours to complete the first For each and to populate the JSON array.
I need to apply Concurrency control and put it to 1 for this For each loop, otherwise Logic app mixes up the wrong values making the JSON array invaluable.

I am quite a newbie to this CSV -> JSON space, and I was looking into tips and tricks on how to increase the processing speed. I did find this article https://social.msdn.microsoft.com/Forums/en-US/acae74f9-4f07-419f-8a03-464b5ca69522/how-to-convert-flat-file-to-json-in-logic-apps?forum=azurelogicapps
I really have 0 experience with Azure Functions or whatever.

I am still wondering, what would be the best approach in my scenario?

I hope someone could point me in a direction.
All help is appreciated.

Thanks!

Answer 1

Hi @Joost van der Linden

If your files are small (check prerequisites in inline code article) you can write your own inline code to convert csv to json using inline code within Azure Logic App.

Some of the reference article how you can convert csv to array/json. You need to modify and test the code as per your business needs.
https://stackoverflow.com/questions/1293147/javascript-code-to-parse-csv-data/1293163#1293163
https://stackoverflow.com/questions/27979002/convert-csv-data-into-json-format-using-javascript

But in case, if you have a big file to be processed then the suggestion would be calling the function app from the logic app. You can create a function in any of the supported languages

Functions have different combinations for input and output bindings. For example, if you want to send data as http request to function but store the data in the storage blob then the input binding will be 'http' whereas the output binding will be 'blobTrigger'. So according to your business needs, you can choose any of the support languages to create different bindings.

If you are using javascript function then you can use the same sample csv to array/json code in the reference articles.
For C# you can use Netwon.json library for conversion: https://github.com/JamesNK/Newtonsoft.Json

Reference articles that talk about Azure Function csv to json conversion. Please modify and test them according to your needs.
https://gist.github.com/nertim/1367ef1cc4a339b105c6d72aafd7bff4
https://github.com/aaronralls/FunctionAppCSVToJSON
https://github.com/paladique/azure-function-csvtojson

Updated:

I have created the nested script and tested it at my end. Please refer to javascript code and modified if needed according to your needs. I have used '_' for differentiating the nested objects.

Sample Input:

[  
  "name,birthday_day,birthday_month,birthday_year,house_type,house_address_street,house_address_city,house_address_state,house_occupants",  
  "Lily Haywood,27,3,1995,Igloo,768 Pocket Walk,Honolulu,HI,7",  
  "Stan Marsh,19,10,1987,Treehouse,2001 Bonanza Street,South Park,CO,2"  
]  

Sample Output:

[  
  {  
    "name": "Lily Haywood",  
    "birthday": {  
      "day": "27",  
      "month": "3",  
      "year": "1995"  
    },  
    "house": {  
      "type": "Igloo",  
      "address": {  
        "street": "768 Pocket Walk",  
        "city": "Honolulu",  
        "state": "HI"  
      },  
      "occupants": "7"  
    }  
  },  
  {  
    "name": "Stan Marsh",  
    "birthday": {  
      "day": "19",  
      "month": "10",  
      "year": "1987"  
    },  
    "house": {  
      "type": "Treehouse",  
      "address": {  
        "street": "2001 Bonanza Street",  
        "city": "South Park",  
        "state": "CO"  
      },  
      "occupants": "2"  
    }  
  }  
]  

Please 'Accept as answer' and ‘Upvote’ if it helped so that it can help others in the community looking for help on similar topics.