cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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.

>