06-10-2020 08:53 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',
'')
)
)
item()
skip(body('Filter_array'),1)
split(item(),';')[0]
If I wanted to change columns, I would change the 0 to 1... or 2... or 3... etc.
split(item(),';')[0] #COMMENT: This would be column 1 (remember we start from 0 in programming)
split(item(),';')[1] #COMMENT: This would be column 2
Hi!
Thank you for the guide, it really helped. How can we enter values back to the same csv file?
Regards,
Dima Kabakov
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
split(item(),';')[0]
"abc;xyz";gfgf;21;"abc;xyz";gfgf
I can't change delimiter on export.
Does anyone have any thoughts?
I would give just the comma ',' a try. @prodaptiv-c
@DimaDima - I am unsure of how to do that at the moment. To insert values into any spreadsheet it has to be XLSX, with a Table inserted.
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,
json(
uriComponentToString(
replace(
replace(
uriComponent(
split(
replace(
base64ToString(
body('Get_file_content')?['$content']
),
',',','),
outputs('Newline')
)
),
'%5Cr',
''),
'%EF%BB%BF',
'')
)
)
Any ideas as to what I may be doing wrong?
Thanks,
Eric
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.