06-12-2020 08:23 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
Hi, this is really handy, thank you. I have just one issue to get my solution working and hoping you can help.
I need to somehow replace the ; between the quotes otherwise the columns shift when splitting using
I can't change delimiter on export.
Does anyone have any thoughts?
Hello @Yuhas ,
I really appreciate the assistance with this. When I test this i am getting an "Invalid Template" error,
"Unable to process template language expressions in action 'Filter_array' inputs at line '1' and column '2880': 'The template language function 'uriComponent' was invoked with invalid parameters. The parameter at index '0' cannot be converted to URI component.'."
The only changes I made to your steps was to change the ; to a , in the filter array and the "split" function, because that is what my data uses as a separator.
Here is what my filter array says,
Any ideas as to what I may be doing wrong?
I would have to see which step is failing but give me a moment to let my coffee settle in, I will read this over.
@ewchris_alaska, so why again are you changing the ";" to "," ?
You shouldn't need to change anything if your file is comma delimited already.
I changed it because I thought that your data was separated by a ; based on the inputs shown
"Joe; Snuffy", (etc)
I will change it back and try again. I appreciate your help.
Yeah I could see that being slightly confusing. I think what the original author was doing is using a ";" during the conversion to JSON.
He/she probably had issues with going straight from "," -> JSON. So they used the "replace()" method to get around it. I'd be interested to see if it works without. Maybe I'll try it later.