06-12-2020 09:34 AM
Disclaimer, I did not fully write this guide. I found this elsewhere on the internet with intense Google searching. However, the guide there was rather poorly written. I was unable to find the original author, I would like to credit them for such exploration into this black hole.
I played with it for about 5 hours and finally figured it out. It was truly a pain but this is going to help my team process a report that we do weekly from ServiceNow. I hope this solves a lot of headache for some of you.
Please note that the zip file is empty. Follow the guide. I apologize about the formatting as well but the forums did not like how my ordered/unordered lists were setup with code-snippits in the HTML.
Order of Actions
Copy and Paste this into the Expression input (where it says fx) - it might look like it only pasted the last line. Don't worry, it got all of it.
json( uriComponentToString( replace( replace( uriComponent( split( replace( base64ToString( body('Get_file_content')?['$content'] ), ',',';'), outputs('Newline') ) ), '%5Cr', ''), '%EF%BB%BF', '') ) )
If I wanted to change columns, I would change the 0 to 1... or 2... or 3... etc.
split(item(),';') #COMMENT: This would be column 1 (remember we start from 0 in programming) split(item(),';') #COMMENT: This would be column 2
When you changed back the ";" did you click update on the expression?
I'd advise directly copying and pasting what I have in the guide. I just ran mine and it worked.
Can you create a new flow with the same steps, and just do it for FirstName,LastName like in the example? Maybe your current CSV data doesn't work with this.
Also, sometimes, when updating the expressions, it will not update even if you click the button. Hover your mouse over the expression to check that it changed.
UPDATE TO ALL INVOLVED WITH THE THREAD!
I have discovered there is a limit to how large your CSV files can be when using this method. It is an unknown number at this moment but that is why @ewchris_alaska and I were having a hard time diagnosing his issue today.
Now.... I don't know if this is a "cell" limit or a "character" limit. If your CSV file is 700 lines and 5 columns wide or larger, (3500 cells), it did not work for us. Anything below, did work with his cleaned (confidential info that was wiped) data set. It would be interesting if someone could test this out with their own large data sets in CSV format. @prodaptiv-c this may have been slightly related to your issue.
Thanks. This was very useful.
I only had to correct the JSON code a little bit, to exclude the replacement of ',' by ';' because my source file already had ';' by default and some data on it uses ',' in other contexts.
his idea working well, but i don't use the json schema. I'm grabbing the data directly with split(item(),<delimiter>) and then formating it inside the select.
I've found this to be the simplest way to parse a CSV, but this code as others have mentioned is still highly dependent on the crazy things CSV's can do. I imagine that's part of why it's a premium feature right now. Anyway, if you're struggling here's some tips I found that finally made this work for me.
Reading this over again it's a bit of a scattershot so I hope someone finds it useful, but I really needed to write this in one place as it was a nightmare to make this work. MS needs to just add a parse CSV option, it seems so basic.