cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tutankh
Level: Powered On

Convert .CSV data into json

So, this won't be a one size fits all solution, but may help in some limited use cases. 


I've had need of converting some csv content into json so I could then do other data-y things with it. Unfortunately, there's no action that supports this currently.  The logic below should help in situations where the data is pretty reliable, as in the case below.

 
{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "logicAppName": {
      "type": "String",
      "metadata": {
        "description": "Name of the logic app."
      }
    },
    "logicAppLocation": {
      "defaultValue": "[resourceGroup().location]",
      "allowedValues": [
        "eastasia",
        "southeastasia",
        "centralus",
        "eastus",
        "eastus2",
        "westus",
        "northcentralus",
        "southcentralus",
        "northeurope",
        "westeurope",
        "japanwest",
        "japaneast",
        "brazilsouth",
        "australiaeast",
        "australiasoutheast",
        "southindia",
        "centralindia",
        "westindia",
        "canadacentral",
        "canadaeast",
        "westcentralus",
        "westus2",
        "[resourceGroup().location]"
      ],
      "type": "String",
      "metadata": {
        "description": "Location of the logic app."
      }
    }
  },
  "resources": [
    {
      "type": "Microsoft.Logic/workflows",
      "name": "[parameters('logicAppName')]",
      "apiVersion": "2016-06-01",
      "location": "[parameters('logicAppLocation')]",
      "properties": {
        "state": "Disabled",
        "definition": {
          "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
          "contentVersion": "1.0.0.0",
          "parameters": {
            "$authentication": {
              "defaultValue": {},
              "type": "SecureObject"
            }
          },
          "triggers": {
            "manual": {
              "type": "Request",
              "kind": "Button",
              "inputs": {
                "schema": {
                  "type": "object",
                  "required": [],
                  "properties": {}
                }
              }
            }
          },
          "actions": {
            "csv_content": {
              "runAfter": {},
              "type": "Compose",
              "inputs": "@'column1;column2\ndata1.1;data1.2\ndata2.1;data2.2'"
            },
            "Compose_-_Split_CSV_to_lines": {
              "runAfter": {
                "Initialize_variable_-_table": [
                  "Succeeded"
                ]
              },
              "type": "Compose",
              "inputs": "@split(outputs('csv_content'), '\n')"
            },
            "Compose_-_get_headers_from_first_row": {
              "runAfter": {
                "Compose_-_Split_CSV_to_lines": [
                  "Succeeded"
                ]
              },
              "type": "Compose",
              "inputs": "@split(outputs('Compose_-_Split_CSV_to_lines')[0],';')"
            },
            "Apply_to_each": {
              "foreach": "@skip(outputs('Compose_-_Split_CSV_to_lines'),1)",
              "actions": {
                "Compose_-_current_line": {
                  "runAfter": {
                    "Set_variable_-_new_object": [
                      "Succeeded"
                    ]
                  },
                  "type": "Compose",
                  "inputs": "@split(items('Apply_to_each'), ';')"
                },
                "Set_variable_-_new_object": {
                  "runAfter": {},
                  "type": "SetVariable",
                  "inputs": {
                    "name": "object",
                    "value": {}
                  }
                },
                "Apply_to_each_-_Add_properties_to_new_object,_Add_complete_object_to_array": {
                  "foreach": "@range(0,length(outputs('Compose_-_get_headers_from_first_row')))",
                  "actions": {
                    "Compose_-_property": {
                      "runAfter": {},
                      "type": "Compose",
                      "inputs": "@addProperty(variables('object'), outputs('Compose_-_get_headers_from_first_row')[item()], outputs('Compose_-_current_line')[item()])"
                    },
                    "Set_variable_-_update_object_with_new_content": {
                      "runAfter": {
                        "Compose_-_property": [
                          "Succeeded"
                        ]
                      },
                      "type": "SetVariable",
                      "inputs": {
                        "name": "object",
                        "value": "@outputs('Compose_-_property')"
                      }
                    }
                  },
                  "runAfter": {
                    "Compose_-_current_line": [
                      "Succeeded"
                    ]
                  },
                  "type": "Foreach",
                  "runtimeConfiguration": {
                    "concurrency": {
                      "repetitions": 20
                    }
                  }
                },
                "Append_to_array_variable_-_Add_object_to_table": {
                  "runAfter": {
                    "Apply_to_each_-_Add_properties_to_new_object,_Add_complete_object_to_array": [
                      "Succeeded"
                    ]
                  },
                  "type": "AppendToArrayVariable",
                  "inputs": {
                    "name": "table",
                    "value": "@variables('object')"
                  }
                }
              },
              "runAfter": {
                "Compose_-_get_headers_from_first_row": [
                  "Succeeded"
                ]
              },
              "type": "Foreach"
            },
            "Initialize_variable_-_object": {
              "runAfter": {
                "csv_content": [
                  "Succeeded"
                ]
              },
              "type": "InitializeVariable",
              "inputs": {
                "variables": [
                  {
                    "name": "object",
                    "type": "Object"
                  }
                ]
              }
            },
            "Initialize_variable_-_table": {
              "runAfter": {
                "Initialize_variable_-_object": [
                  "Succeeded"
                ]
              },
              "type": "InitializeVariable",
              "inputs": {
                "variables": [
                  {
                    "name": "table",
                    "type": "Array"
                  }
                ]
              }
            },
            "Select_-_whatever_data_you_need": {
              "runAfter": {
                "Apply_to_each": [
                  "Succeeded"
                ]
              },
              "type": "Select",
              "inputs": {
                "from": "@variables('table')",
                "select": "@item()['Scored Labels']"
              }
            }
          },
          "outputs": {}
        },
        "parameters": {},
        "runtimeConfiguration": {
          "collections": {
            "maximumItemCount": 5000
          },
          "performanceProfile": {
            "throttles": {
              "mode": "Low"
            }
          }
        }
      }
    }
  ]
}
4 REPLIES 4
Highlighted
mokhawaja
Level 8

Re: Convert .CSV data into json

Hi @tutankh,

Just by chance I found this link posted in twitter!!

 

https://sergeluca.wordpress.com/2018/10/28/microsoft-flow-advanced-tutorial-creating-a-csv-converter...

 

let me know if this solves your request.

Cheers,

Mohammad

tutankh
Level: Powered On

Re: Convert .CSV data into json

Thanks Mohammad,

 

This is essentially the same thing the code above accomplishes. I have an issue and a problem with it though.  The issue is that, as the author states, it takes an extremely long time to process.  90 minutes for a 950 row table with 3 columns.  I can confirm that this is about right.  My incoming data has roughly 20-25 columns and about 1000 rows and takes upwards of 6 hours to process the 'parsing' operation.  The reason for this is that Flow cannot (currently) instantiate variables.  This makes it impossible to process loops concurrently. 

The problem it has is when your data has something like Active Directory distinguished names, which have comma's -in- the data.  So it becomes very difficult to split the rows to individual entries. For example... Let's say my data looks like....

Name,samAccountName,DistinguishedName,Enabled

John Smith,JSmith,"CN=JSmith,OU=Users,DC=somewhere,DC=contoso,DC=com"

 

How do you split out rows? I have not found a way to regex match/replace in Flow.

 

In my case, was fortunate that I can have my data generated into an HTML table, so I can split it on things like <br> and <td>, but this wouldn't be possible on CSV data.... Need a good solution from Microsoft.

tutankh
Level: Powered On

Re: Convert .CSV data into json

As I continue to think about this, I had another idea.  You could actually turn the below into an API by adding a 'request' trigger and 'response' final step. What this would allow you to do is call the process from another Flow. Thereby simplifying the amount of steps in your actual flow. I have done this and it does seem to work pretty well.. Obviously it still suffers from the same issues as the initial script, and we still need a solution from Microsoft to do this.

 

Those interested can vote for that here.

Super User
Super User

Re: Convert .CSV data into json

Hi!

 

You can use Parse CSV action from Plumsail Documents connector. It allows you to convert CSV into an array and variables for each column. Please read this article demonstrating how it works.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 540 members 4,626 guests
Please welcome our newest community members: