Base64tostring on csv file $content is filled with junk

[Louis1911]

Hello all!

I have built a Sharepoint site where Sharepoint events point to Teams webinar registration links for various training courses used by various employees through my organisation.

I'm then getting the .csv report from each event to get the attendance list and I have created a flow that triggers when the .csv files are dropped in a specific Sharepoint folder.

Before the flow triggers, please note that I can open the .csv file in Excel or Notepad++ and the information is displayed correctly (the data is not corrupted or badly encoded).

The flow does 2 specific things:

1. Get information from the ActiveDirectory for each attendee and put that information in a dashboard so we can get statistics for attendance in each department.

2. Export the user data on a Sharepoint list used to send e-mail surveys based on attendance for each event (sending the survey is a separate flow to automate it all).

I'm using the trigger "When file created (properties only)" following by a condition that makes sure it's a .csv file.

I then get the file content (It doesn't matter if I select "yes" or "no" to the infer question; the result is the same):

with the following output:

{

  "$content-type": "application/octet-stream",

  "$content": "//4xAC4AIABSAOkAYwBhAHAAaQ..."

}

I have searched for a while as to what to do past this point. I read that I can use the base64tostring() formula to decode the content. While using this formula:

decodebase64(outputs('Obtenir_le_contenu_du_fichier')?['body']['$content'])

I can see that I decoded the string, however it's filled with garbage:

When I input the $content string on https://www.base64decode.org/, I can see the data formated the way I want it and without the garbage.

I'm wondering what to do at this point, I have tried using the OneDrive for Business action to get the file, but I can't seem to use it to fetch a file on Sharepoint. I have found a few posts with users reporting this exact behavior, but none of them get a solution that works for me.

I know I could purchase some premium connector to parse the .csv, but that option isn't accessible to me. I'm confident I can manually parse the .csv and do what I want with the data after I get a clean conversion. I'm assuming it must have something to do with the conversion format, but I'm not sure how to tackle it.

Oh and I had another flow that did the exact same thing, but with the deprecated trigger "get file when created" and I'm trying to build a flow that is not based on a deprecated action.

Thank you!

Edited to clarify my goal with the flow as well as additional test information.

[FLMike]

Hi @Louis1911 

Stupid question... have you tried simply writing the data to a file and opening it to see if its correct?

You would write the body/content value. This is normally what I would do so I am just wondering if there really is a need to do anything else?

Because I dont know why if you are just trying to grab the content of a file and write it somewhere else, you need to do anything to it but read it and write it.

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[Louis1911]

The goal of the flow is to take the attendee report and to do some operations with the names (namely to get some information from the ActiveDirectory and to put it on a dashboard). I also use the users information in another Sharepoint list to do some more operations (namely to send them an e-mail with a survey in it).

The attendee report is a .csv file that I can open and manipulate with no problem at all. I'll give it a try tommorow to create a new file in the flow and see what the result is.

[FLMike]

@Louis1911 

Ahh okay it wasn't clear what you were doing. Sorry.

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[Louis1911]

I have made a few other tests where I take the string from $content in the flow and then use the plugin -->MIME tools --> Base64 decode in Notepad++

I'm getting the same result as my flow (each character seems to be separated by a NULL value) :

However, if I open the .csv file manually and encode it in Base64 using the same tool, it gives me a completely different string.

If I take that string in my compose and use the base64tostring formula, it works!

I have done the same exercice on base64encode.com and it's giving me the exact same string as notepad++.

I'm thinking now that the action to get the file content is doing an additional operation that I'm not aware of.

If I use the deprecated trigger when a file is created in a folder that immediately returns the content, it detects the proper content type and automatically decodes the information properly:

It gives me the application/octet-stream content-type with the get file content action:

edited to add some test results