“I need to parse a .csv attached to a SharePoint item, but Power Automate turns the attachment into a complete mess, how can I keep the original csv format?”
When you trigger flow on a .csv file uploaded in a SharePoint document library, Power Automate will read it as .csv. It’ll have the expected format, and you can parse it as needed. But that’s not true if you upload a .csv file as item attachment. If you use the ‘Get attachment content’ on a .csv attachment, the content will be encoded. All the easily readable data is gone, together with the simple processing possibilities.
Save the file as .txt
You can try some conversion to turn it back, but I didn’t find any that works. But what does work is to save the file as a text file (.txt).
Create a library on the SharePoint site, e.g. ‘Attachments’. It’ll serve as a storage for the attachment files. Then, in the flow, ‘Get all attachments’ from the item…
… and ‘Get attachment content’ for all of them. At this moment it doesn’t matter that you can’t read it.
Once you have the attachment content, you can ‘Create file’ in the document library using the original file name with added ‘.txt’ to it. You might need to update the file if it already exists, but that way you’ll handle the conversion back to the original csv format.
The last step is to ‘Get file content’ of the new txt file. It’ll have the original csv format which you can now easily process.
Summary
Processing .csv in Power Automate can be tricky if you upload it as an item attachment. SharePoint will encode it on the background and flow won’t be able to read it easily. But with this workaround, saving the attachment as a file in a document library, you can turn it back.
And it’s actually quite beneficial workaround. Such library will give you a backup in case somebody deletes the attachment by mistake as deleted attachments are otherwise lost forever.
Been trying to solve this issues all week! Processing a CSV attachment from email and could not get the actual data contents. This worked like a charm – too bad we have to add the additional steps but oh well.
Great stuff, will presented article.
Did you try using the any of the base64(…) expressions to convert the content? I believe that should work without having to convert it to a TXT file.
I saw a similar solution here:
https://www.google.com/amp/s/sharepains.com/2020/03/09/read-csv-files-from-sharepoint/
The specific expression would be:
base64ToString(outputs(‘Get_attachment_content’)?[‘body/$content’])
Then you can split that output on \r\n (CrLf) to get an array of the individual rows.
At least this worked for me. 🙂
As they say, “your mileage may vary”, so it’s possible this won’t’ work in your scenario.
Just thought I’d pass this along.
Hello Joey,
thank you for the comment, that’s what I tried first, but the result was as unusable as the encoded string: https://tomriha.com/wp-content/uploads/2021/10/base64toStringcsv.png.