How to remove/replace the start and the end of a string in flow?

[blinabj]

Hi, 

I am wondering how to remove/replace the start and the end of a string in flow?

I I have this string:

Base64ToString(
Title,Dato,Department
Test 1,2022-12-30,NY
Test 3,2022-12-31,PARIS
Test 5,2023-03-04,NY
Test 6,2023-07-01,LONDON
Test 2,2023-01-21,PARIS

)

How can I remove the "Base64ToString(" and ")", so that the string looks like this?

The reason I need this is because I need to export it as a csv-file.

Title,Dato,Department

Test 1,2022-12-30,NY
Test 3,2022-12-31,PARIS
Test 5,2023-03-04,NY
Test 6,2023-07-01,LONDON
Test 2,2023-01-21,PARIS

It needs to happen in flow. I tried using replace, but it didn't quite work maybe I wrote something wrong...?

Really appreciate the help!

[grantjenkins]

I've built a couple of options for you.

I've used a variable called text that contains your data.

You can use the following expression to remove the first and last lines, including the new line characters.

replace(replace(variables('text'), concat('Base64ToString(', decodeUriComponent('%0A')), ''), concat(')', decodeUriComponent('%0A')), '')

However, it looks like you actually want to extract the data, so you could actually do the following.

See full flow below. I'll go into each of the actions.

Initialize variable is what we had before - string variable called text that contains your full string.

Compose splits the string by new line character to give you an array of strings. We also use skip to exclude the first two rows. The expression used is.

skip(split(variables('text'), decodeUriComponent('%0A')), 2)

Filter array then takes that data and removes any items that are ")" which is our last extra line. The expression used is:

item()

Finally, we have a Select that takes in the output from our Filter array and builds up each of the objects. Below are the expressions used:

//Title
split(item(), ',')?[0]

//Date
split(item(), ',')?[1]

//Department
split(item(), ',')?[2]

After running the flow, we would get the following output from our Select.

[
  {
    "Title": "Test 1",
    "Date": "2022-12-30",
    "Department": "NY"
  },
  {
    "Title": "Test 3",
    "Date": "2022-12-31",
    "Department": "PARIS"
  },
  {
    "Title": "Test 5",
    "Date": "2023-03-04",
    "Department": "NY"
  },
  {
    "Title": "Test 6",
    "Date": "2023-07-01",
    "Department": "LONDON"
  },
  {
    "Title": "Test 2",
    "Date": "2023-01-21",
    "Department": "PARIS"
  }
]

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[VictorIvanidze]

Show your flow. Where did you get "Base64ToString(..." ?

[blinabj]

Hi @VictorIvanidze,

I am adding it in order to read a csv file in Power Apps flow. If you know a better way to read it in flow I would be happy to here it 😀

Hent filinnhold = Get file content

Then, after this step, what I ideally want to do is create a new CSV file that looks like this (all in one column):

So, the main goal with this flow is to format a CSV file and create a new file with the right format.

[VictorIvanidze]

Please show the output of Komponer (Compose I guess) action.

[blinabj]

Thanks for helping me @VictorIvanidze! The output of the Komponer/Compose is:

Base64ToString(
Title,Dato,Department
Test 1,2022-12-30,NY
Test 3,2022-12-31,PARIS
Test 5,2023-03-04,NY
Test 6,2023-07-01,LONDON
Test 2,2023-01-21,PARIS

)

[VictorIvanidze]

Now please show the same action in edit mode.

[blinabj]

@VictorIvanidze, that's the picture I showed you earlier:

Hent filinnhold = Get file content

Komponer = Compose

The file that I am getting content from is saved on SharePoint and the content looks like this:

So, my goal with this flow is to get this content, and make a new csv file with the content that looks like this:

[VictorIvanidze]

It looks your compose action is wrong.  The Base64ToString is a function and you are using it as a prefix - for what?

[blinabj]

@VictorIvanidze 

I need the compose action in order to read the file content. If I don't use it, I only get some wired data information sentences. See picture below:

I found the tip about using Base64ToString here:

Solved: Re: How to read CSV file content from a file saved... - Power Platform Community (microsoft....

[VictorIvanidze]

Again:  Base64ToString is a function. Use it as a function.

[blinabj]

Thank you for clearing that up @VictorIvanidze! I tried using it as a function now, but then I get this error message:

My Compose looks like this:

, with the function being:

Base64ToString(outputs('Get_file_content')?['body'])

[VictorIvanidze]

Re-read https://powerusers.microsoft.com/t5/General-Power-Automate/How-to-read-CSV-file-content-from-a-file-...

[blinabj]

I am sorry @VictorIvanidze , but I really don't understand what I am missing/don't understand... I have tried re-reading it several times.

[VictorIvanidze · Accepted solution]

OK, please find a difference:

Base64ToString(outputs('Get_file_content')?['body'])

AND

Base64ToString(body('Get_file_content')?['$content'])

[VictorIvanidze]

Hi @blinabj,

did I solve your problem? 

[grantjenkins · Accepted solution]

Here's an example of what you're hopefully after.

For this example, I'm using the following CSV data (same as your example).

See full flow below. I'll go into each of the actions.

Get file content retrieves the content from my CSV file.

Compose converts the content to a string so we can parse it easily. The expression used is:

base64ToString(outputs('Get_file_content')?['body']?['$content'])

Filter array does a few things. It splits the data by new line and character return, skips the first row (headers) and filters out any empty rows (normally the last row). The expressions used are:

//From (input)
skip(split(outputs('Compose'), decodeUriComponent('%0D%0A')), 1)

//Filter
item()

Create CSV table uses the output from the Filter array and builds up the CSV. It has a single header which is your headers combined and uses the following expression to get the row of data. Note that you will need to select Advanced mode to specify the header and value.

item()

Finally, Create file takes the output from Create CSV table to create a new CSV file. Note that you need to include .csv as part of the file name.

After running the flow, we would have a CSV file with the following data.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[blinabj]

That's perfect! Thank you so much, @grantjenkins for taking the time to show me!😀