This week I have been helping a colleague Ritwik who was having some hastle with CSV files from SharePoint and processing them in Logic Apps.
The problem we get is that when the SharePoint Connector queries the file to get the data you can see below that the data comes back as a stream. It proves to be a bit more complicated than I thought it would be to get this into plain text to process it.
Using some of the out of the box conversion expressions the best we can get it to is to be line the below
There are a few articles online about Logic Apps and Power Automate where they talk about various ways of doing javascript or calling a function and doing some stuff in .net or complex expressions all of which see a bit of a pain to maintain.
The problem seems to be highlighted when you use the get file properties action you will see that SharePoint returns the media type as application/vnd.ms-excel
I guess this is SharePoint automatically handling the MIME type and returning excel for a csv which if you open the file as a user in SharePoint it will open in excel in the browser. This is great for a user but for a system integration scenario its a bit of a pain. We really need the file as text/plain so we can process the CSV data.
What we did to work around this with out needing any custom code is quite a simple workaround really. We do the following:
- Use the get content to query the file content for which we get back the stream mime type
- We then use the create file connector and create a new file and populate it with the content from the original file query. The new file thou we change to a .txt file
- We then query the content of the .txt file with the Get File content connector and this time it infers the content type as text/plain so we get the csv text
- We then delete the temp file and can now process the data
You can see below we now get the data as the CSV text
Its kind of frustrating how the infer content type doesnt handle this too well on the sharepoint connector, for something like SFTP it would read the CSV file fine but this is how SharePoint is presenting the file to us and the key thing is that we can do a workaround which doesnt need any custom code so it keeps things simpler and we just need a couple of extra steps in the workflow.
Hopefully this will make someone elses life easier and love to hear if anyone has a better workaround
