It’s an age-old problem, you have a CSV file that you want to parse into a JSON Array and you want a single quick and easy solution to tackle the common problem. I have a solution for you to consider without using the Premium Connectors. I have built a generic Select Action that I will share with you. Copy this to your clipboard and paste it into your Power Automate and be prepared for an easy life. I’ve recently covered how to efficiently parse a CSV here vs the age-old apply to each solution.
Note, that I now have an ever more improved parsing solution. Handle the column keys dynamically. Any shape or sized CSV without the need to edit the existing flow. Retrieve data from OneDrive, SharePoint and Outlook Attachements.
I’ve just downloaded a CSV from Kaggle and it contains all Netflix Shows with 12 columns of data. I will show you how I parsed this data really fast. Something to note is how the CSV is encoded. Linux files use \n as a new line whereas Windows uses \r\n to indicate a return and new line. For the purpose of this demo I am using linux encoded \n files and am therefore able to use a compose with a return line. If you are using files with \r\n you will have to follow my article here and make a slight change to my sample code it’s important to understand how your CSV is encoded.
The first part of the Cloud Flow is to establish an Array of the CSV data. The easiest method is to use the Get File Content action for One Drive as it returns the file as plain text, use the SharePoint Get File and you must re-encode the file content. Then in order to split the lines I create a Compose Action “ReturnLine” which simply has a single return line (that’s right, insert your cursor and hit return). The final step is a compose action that you split the file content by the ReturnLine Compose action.
The next part of the process is the clever part. I have devised a copy/paste option. Assuming that you have a header in the file like my sample NetFlix data above, you can copy and paste my sample select action into your Cloud Flow and add/remove any additional columns as you see fit. My example has twelve columns and in this case I have supplied a sample action with 12 keys and values built up using split and choosing elements by integer numbers.
Upon Selecting the pasted action “ParseMyCSVByDamoBird365” into Power Automate you will see the pre-populated Action below:
Here is the sample code:
{
"id": "DamoBird365",
"brandColor": "#8C6CFF",
"connectorDisplayName": "Data Operations",
"isTrigger": false,
"operationName": "ParseMyCSVByDamoBird365",
"operationDefinition": {
"type": "Select",
"inputs": {
"from": "@skip(outputs('Compose'),1)",
"select": {
"@{split(first(outputs('Compose')),',')?[0]}": "@split(item(),',')?[0]",
"@{split(first(outputs('Compose')),',')?[1]}": "@split(item(),',')?[1]",
"@{split(first(outputs('Compose')),',')?[2]}": "@split(item(),',')?[2]",
"@{split(first(outputs('Compose')),',')?[3]}": "@split(item(),',')?[3]",
"@{split(first(outputs('Compose')),',')?[4]}": "@split(item(),',')?[4]",
"@{split(first(outputs('Compose')),',')?[5]}": "@split(item(),',')?[5]",
"@{split(first(outputs('Compose')),',')?[6]}": "@split(item(),',')?[6]",
"@{split(first(outputs('Compose')),',')?[7]}": "@split(item(),',')?[7]",
"@{split(first(outputs('Compose')),',')?[8]}": "@split(item(),',')?[8]",
"@{split(first(outputs('Compose')),',')?[9]}": "@split(item(),',')?[9]",
"@{split(first(outputs('Compose')),',')?[10]}": "@split(item(),',')?[10]",
"@{split(first(outputs('Compose')),',')?[11]}": "@split(item(),',')?[11]"
}
},
"runAfter": {
"Compose": ["Succeeded"]
}
}
}
The from of the Select Action is made up of the original array, minus the first line (header). I do this by calling the Skip Expression.
The key is made up of the first line of the Array, split by a comma to make an array and each element is chosen by an integer, starting from 0.
The value is made up with a split by a comma on each item to make an array and each element is chosen by an integer, starting from 0.
**NOTE** as an example – if you have 5 header items in your CSV, simply remove the additional lines from the Select Action to leave 0->4.
I have uploaded a cleansed version of the Netflix CSV for you to try here or a simplified 10 row version here. Let me know how you get on.
Have you tried copying actions and then editing them in a text editor? Did you know that you can do this? Another quick tip is to put all of your actions into a scope and paste them into your next flow. Now you can easily copy large parts of existing flows into new flows!
More great ideas and tips here, don’t forget to bookmark and subscribe to my YouTube channel.
Want it easy? Try copying and pasting the following where I have also included the schema to use the bespoke column dynamic data:
{
"id": "54b0c051-85b3-4d6f-9232-b037-16b336d3",
"brandColor": "#8C3900",
"connectionReferences": {
"shared_onedriveforbusiness": {
"connection": {
"id": "/providers/Microsoft.PowerApps/apis/shared_onedriveforbusiness/connections/shared-onedriveforbu-5a107753-9d6f-4fbf-957e-72c6-987acc35"
}
}
},
"connectorDisplayName": "Control",
"icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
"isTrigger": false,
"operationName": "DamoBird365CompleteSolution",
"operationDefinition": {
"type": "Scope",
"actions": {
"Get_file_content": {
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_onedriveforbusiness",
"operationId": "GetFileContent",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_onedriveforbusiness"
},
"parameters": {
"id": "b!BOIVsACSM02nAvrKA5sQqce2eGDhytpJrD6Ky1g1xl_-qoaIOl6yRo2252_HSDPt.01SRF3RDVKY4JNLOKJOFB3MKKKWQPKLWAP",
"inferContentType": true
},
"authentication": {
"type": "Raw",
"value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
}
},
"runAfter": {},
"metadata": {
"b!BOIVsACSM02nAvrKA5sQqce2eGDhytpJrD6Ky1g1xl_-qoaIOl6yRo2252_HSDPt.01SRF3RDXVNLPLRY4BP5E3JFENUQ7QBRIJ": "/Temp/username-password-recovery-code.csv",
"b!BOIVsACSM02nAvrKA5sQqce2eGDhytpJrD6Ky1g1xl_-qoaIOl6yRo2252_HSDPt.01SRF3RDREN5NIGPEYG5C3DS6WG666PJO7": "/Temp/netflix_titles.csv",
"b!BOIVsACSM02nAvrKA5sQqce2eGDhytpJrD6Ky1g1xl_-qoaIOl6yRo2252_HSDPt.01SRF3RDVKY4JNLOKJOFB3MKKKWQPKLWAP": "/Temp/netflix_titles_small.csv"
}
},
"ReturnLine": {
"type": "Compose",
"inputs": "\n",
"runAfter": {
"Get_file_content": ["Succeeded"]
}
},
"Compose": {
"type": "Compose",
"inputs": "@split(outputs('Get_file_content')?['body'], outputs('ReturnLine'))",
"runAfter": {
"ReturnLine": ["Succeeded"]
},
"description": "split(outputs('Get_file_content')?['body'],outputs('ReturnLine'))"
},
"ParseMyCSVByDamoBird365": {
"type": "Select",
"inputs": {
"from": "@skip(outputs('Compose'), 1)",
"select": {
"@{split(first(outputs('Compose')),',')?[0]}": "@split(item(), ',')?[0]",
"@{split(first(outputs('Compose')),',')?[1]}": "@split(item(), ',')?[1]",
"@{split(first(outputs('Compose')),',')?[2]}": "@split(item(), ',')?[2]",
"@{split(first(outputs('Compose')),',')?[3]}": "@split(item(), ',')?[3]",
"@{split(first(outputs('Compose')),',')?[4]}": "@split(item(), ',')?[4]",
"@{split(first(outputs('Compose')),',')?[5]}": "@split(item(), ',')?[5]",
"@{split(first(outputs('Compose')),',')?[6]}": "@split(item(), ',')?[6]",
"@{split(first(outputs('Compose')),',')?[7]}": "@split(item(), ',')?[7]",
"@{split(first(outputs('Compose')),',')?[8]}": "@split(item(), ',')?[8]",
"@{split(first(outputs('Compose')),',')?[9]}": "@split(item(), ',')?[9]",
"@{split(first(outputs('Compose')),',')?[10]}": "@split(item(), ',')?[10]",
"@{split(first(outputs('Compose')),',')?[11]}": "@split(item(), ',')?[11]"
}
},
"runAfter": {
"Compose": ["Succeeded"]
}
},
"Top5Results": {
"type": "Compose",
"inputs": "@take(body('ParseMyCSVByDamoBird365'),5)",
"runAfter": {
"ParseMyCSVByDamoBird365": ["Succeeded"]
}
},
"Parse_JSON": {
"type": "ParseJson",
"inputs": {
"content": "@body('ParseMyCSVByDamoBird365')",
"schema": {
"type": "array",
"items": {
"type": "object",
"properties": {
"show_id": {
"type": "string"
},
"type": {
"type": "string"
},
"title": {
"type": "string"
},
"director": {
"type": "string"
},
"cast": {
"type": "string"
},
"country": {
"type": "string"
},
"date_added": {
"type": "string"
},
"release_year": {
"type": "string"
},
"rating": {
"type": "string"
},
"duration": {
"type": "string"
},
"listed_in": {
"type": "string"
},
"description": {
"type": "string"
}
},
"required": ["show_id", "type", "title", "director", "cast", "country", "date_added", "release_year", "rating", "duration", "listed_in", "description"]
}
}
},
"runAfter": {
"Top5Results": ["Succeeded"]
}
}
},
"runAfter": {}
}
}
Hy DamoBird,
Imade flow based on this one.
and able to get html table from this.
but how to get content to excel file?
parse json doesnt work
tnx
HI! Thanks!!! Excellent Solution… i have a little issue… in the last item in each line return this extra character \r… How can i delete it?
Example:
“CI afectado”: “SE Seguridad”,
“ID externo de reconocimiento de CE\r”: “1000034629\r”
Nestor – instead of splitting by the typed carriage return,
use a specific code for Windows CR+LF
eg:
split(outputs(‘Get_file_content’)?[‘body’], decodeUriComponent(‘%0A’))
See https://www.tachytelic.net/2021/02/power-automate-parse-csv/ for the source and explanation.
I found the same and replaced ReturnLine from being a CR to a function decodeUriComponent(‘%0D%0A’)
Hello, I’ve try this but at the end I still got Error : “The schema validation failed.” I tried on my csv file as it has 25 row and copy and paste new result to ParseJSON step but not sure why it still getting errors..
Have you had any success generating a map dynamically using another Select action before your ParseMyCSVByDamoBird365 action?
I’ve tried as such, but without much luck. Just can’t seem to dynmically input the value as a formula based on each line.
I thought something like below should work, but nope :S
[I had to remove all my code to get this to post]
I notice you have a dynamic method using an Apply to Each action, but unfortunately that’s to slow for the CSV’s I’m working with 🙁
My code was basically in the From I would generate a range from 0 to the length of an array generated by splitting the top row. Then the Map was basically your line 62 formula in a concat, combining parts of the formula with item() to dynamically replace the 0 with that key’s index. Sorry to be crytic.
HI! Thanks!!! Excellent Solution… i have a little issue… in the last item in each line return this extra character \r… How can i get rid of it. Also, you have a link to you https://damobird365.com/2021/03/21/power-automate-efficiently-parse-a-csv-to-json-without-an-apply-to-each/ which is not working. I think the link which is not working might have the solution for my question.
HI! I am having a trouble, the last item in each line return this extra character \r. How can i get rid of it. Also, you have a link to you https://damobird365.com/2021/03/21/power-automate-efficiently-parse-a-csv-to-json-without-an-apply-to-each/ which is not working. I think the link which is not working might have the solution for my question.