Change text file content based on SharePoint List data

[autoperfect93]

Dear All,

I have a power automate flow which is being triggered whenever an email is received with a text file attached and saves the attached file in the SharePoint folder.

Is it possible to create a second flow which will lookup in SharePoint list and change 2 rows inside the previously saved text file in the SharePoint folder?

I will need to use a logic such as the "IF" statements in Excel.

For example in SharePoint list we have 3 columns: Column 1 - Old Names; Column 2 - New Names and Column 3 - New Names identifier.

What I need the Power Automate flow to do is based on the "Old Name" in row 1 inside the text file to lookup in SharePoint list and select the New Name and New Name Identifier which are equal to the Old Name. And then change the old name in row 1 with the new name and put the name identifier to a different row (row number 2 for example).

Is this doable in Power Automate?

[Nived_Nambiar]

Hi @autoperfect93 

I am bit confused by what you meant by row 1 and row 2 , could you clarify that using screenshots ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[PeterFromDenver]

If I understand correct, and not even focus on the Sharepoint fields, you want to replace the first 2 lines of a text file using power automate cloud workflows. I have not edited text file by rows so not sure if it can be done. But if I were to investigate, here’s what I would look at doing. I performed something similar when I would edit infopath xml files and would an expression that would replace specific strings of xml text

Use the Get Content action and select the text file you want to update.

Using a variable or compose action, plus the use of expression builder, you’ll need to use Trim or Replace or maybe other expressions that can replace or strip out the first two lines of text. If you have any unique characters or standard syntax or character counts, it may help removing the text. 
If you get that far, you will need to use Create file so that it uploads the new content. Not sure if there is an update file action.

If this actually helps some, that’s great. This may have issues if your text file is large. I don’t know how big of a text file can be loaded in to a variable or compose action. 

[autoperfect93]

Hi @PeterFromDenver , @Nived_Nambiar 

Below I will explain exactly what my goal is:

Every day I receive an email with a file attached. In the attached file I need to manually change 2 rows.

Row 5: In the attached file I have Counterparty name which is an old name and I need to look into a separate excel file and replace the old name with a new name. 

Row 17: After "|" I need to put the unique number for this counterparty which I take again from the excel table.

Manual steps:

1) Open the file.

2) Read the counterparty name in row 5.

3) Open the excel table and find the old counterparty name in column "Old Counterparty name". 

4) Copy the new counterparty name from column "New Counterparty name" which corresponds to the old name.

5) Delete the old counterparty name in row 5 and paste the new name.

6) Cut the 10 digit number after the new name in row 5 and paste it on row 17 after "|"

7) Save the file in SharePoint folder.

Can this be achieved with Power Automate ?

Any help will be highly appreciated. Thanks in advance! 

[Nived_Nambiar]

Hi @autoperfect93 

Yes this can be done !

Let me tell how you can update specific row in text file using lookup in excel file using power automate !

I have an excel file like below , a table should be created for this , since power automate identifies data in excel by its table not by sheet.

also i have a text file like this 

I need to replace row2 "old data " by looking up the value corresponding to excel file , here it should be replaced with old data new version.

So follow the below steps

1. Read the content of the text file using get file content actions

here i am hardcoding file path for demo purposes

2. use compose action to split the file content based on new line so to transformed to an array format 

Expression used: 

split(outputs('Get_file_content')?['body'],decodeUriComponent('%0D%0A'))

3. List all rows from excel file from which you need to perform lookup.

4. Now we have to use filter array to filter the excel rows by column Old Counterparty name equals the value present in row 2 (i.e old data) for that in right side, an expression is written in filter array

5. use replace expression to replace the old data in the text file with new data present in output of filter array corresponding New Counterparty name

expression used: 

replace(outputs('Get_file_content')?['body'],first(body('Filter_array'))?['Old Counterparty name'],first(body('Filter_array'))?['New Counterparty name'])

Now create the file with same name with file content as output of above compose action

Now the result you get is like below in sample_text.txt after running the flow:

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[autoperfect93]

Hello @Nived_Nambiar 

Thank you for the detailed explanation.

Currently I am facing the below error:

Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template function 'decodeDataUriComponent' is not defined or not valid.'.

What might be causing this error?

[PeterFromDenver]

@Nived_Nambiar thanks for that missing piece to splitting out text using the New Line. I need to save that code clip in my notes. Or at least test it to see why it doesn’t work for @autoperfect93. It’s terrible that I always seem to be on the forums when I’m not at my laptop. 

Expression used: 

split(outputs('Get_file_content')?['body'],decodeUriComponent('%0D%0A'))

[PeterFromDenver]

@autoperfect93 I can't replicate the error. But are you getting the error when you "Test using recently used trigger"? If so you may want to create a new test job because changing/adding certain actions to an existing flow can cause errors when testing using triggers from earlier tests. When I see 0s on your action to 'Get file content', it may be missing data and therefore the compose is failing due to an expression with null source value.

Otherwise, I created a new workflow with 3 actions.  Manually trigger a flow, Get file content (in my case a file from OneDrive instead of SharePoint but shouldn't matter), and a Compose. In the Get file content, point it to a test file like a text file using the same content that Nivad provided. Then use the same expression in the compose action. Test again and see if there are errors.

To troubleshoot the errors, while looking at your results check for the following.

1) Expand the 'Get file content' action and verify it sees your content and looks like normal plain text data.

2) Expand the 'Compose' action (if it worked) and verify you see the similar content but formatted with

[

"row 1 text",

"row 2 text",

"row 3 text"

]

 

[Nived_Nambiar]

Hi @autoperfect93 

From the error message, i got the reason behind the error, see you have used decodeDataUriComponent function instead of decodeUriComponent function.

see the expression below:

split(outputs('Get_file_content')?['body'],decodeUriComponent('%0D%0A'))

This should be the expression then.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[autoperfect93]

Hi @Nived_Nambiar ,

Thank you! The previous error was solved. But now I face an error on the Compose 2 step:

Unable to process template language expressions in action 'Compose_2' inputs at line '0' and column '0': 'The template language function 'replace' expects its second parameter 'old string' to be a string. The provided value is of type 'Null'

What might be causing this error ?

[autoperfect93]

Hello @Nived_Nambiar ,

I found the root cause of the issue I face. In my case the problem is that by doing a lookup into the excel table the flow needs to check every row from that table and based on the old counterparty name in the text file to find the corresponding new counterparty name in the excel table. Let me show you what I mean.

In the example given by you on "Filter Array" step you are performing a lookup based on a specific row. In this case row 2:

This is valid only if we lookup to row 2 in the excel table every time. 

This means that every time we run the flow it checks the value only inside row 2 and does not check the other rows in the excel table.

For example I have an excel table with 39 rows and each row contains different counterparty name.

The flow has to lookup each row and check if the counterparty name in the .txt file matches with the counterparty name in Column A. If match is found it needs to replace the name with the one in Column B.

Can this be achieved by Power Automate?

[Nived_Nambiar]

Hi @autoperfect93 

sorry for late response !

May i know what you meant by row2 , is it row 2 of excel table or row 2 of text file ?

[autoperfect93]

Hi @Nived_Nambiar ,

What I meant row 2 of the excel table.

As I have 39 rows the lookup should check each row (not only row 2).

[Nived_Nambiar · Accepted solution]

see it is doing that lookup as well, the filter array is doing that, the filter array action will filter the first column with value of row2 obtained from text file by passing as value. You can try and see the result !

see the example whichi have shared at beginning, notice that value which was replaced from excel table was at row 1 not at row2 🙂

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[autoperfect93]

Hello @Nived_Nambiar ,

Thank you very much for your help. The solution works !