What is "u0002 hexadecimal value 0x02" and why is it throwing error "invalid character"? -trigger: Populate a Microsoft Word Document

[HydrogenUtility]

 "message": "BadGateway",
    "innerError": {
      "status": 500,
      "message": "'\u0002', hexadecimal value 0x02, is an invalid character.\r\nclientRequestId: 4a84735b-0f78-4263-9f4b-10db008925d4",
      "error": {
        "message": "'\u0002', hexadecimal value 0x02, is an invalid character."
      },

This message was generated when a flow failed to execute at the point where multiple (100+) excel fields were to fill content control fields in a word doc. This error is intermittent but I cannot tell whether the fault is in the set out of the flow or in a particular row in the excel data. In other words some excel rows execute fine, but I cannot trace the error without knowing what that hex character looks like in an MS Office environment.

I have additionally seen the error report as Bad Gateway with no additional details:

Same error or different error?

Any help and / or perspective is appreciated!

[Paulie78]

It’s the “start of text” character. See this blog post I wrote:

https://www.tachytelic.net/2020/10/remove-unwanted-characters-power-automate-flow/

it covers removing unprintable characters.

[HydrogenUtility]

Great, thanks @Paulie78 I reckon that's going to be the solution.

First of all, though, I'm going to have to devise a way to loop through all the items collected in the action 'Get a row' from the excel connector; how to create a do until or a Apply to each in the case where it seems that the excel data all come during flow creation (not at run time) in an turn up as dynamic content. The actual step (something like items('body')) is not available in this case.

See screenshot; view-port narrowed to demonstrate the enormity of the content.

Any clues on how to implement your awesome nested replace() technique in this awkward case?

Cheers,

Lachlan

[Paulie78]

You can't create a loop on "Get a Row" - because it does not return an array, but a single row. To do apply to each loop you would need to use the action "List Rows Present in a Table".

[HydrogenUtility]

Thank @Paulie78 , that's understood.

However, in the case where I require information in a single row of an excel table, lets say row 1 of 1000 rows and each row has data in, let's say 100 columns, then it's inconceivable that I'd need to loop through 100,000 elements of data to search for a single character '\u0002'. Unless I'm completely misunderstanding, that's what 'List Rows Present in a table' and then 'Apply to Each' would achieve right?

Whilst 'Get a Row' doesn't appear to output an array, the individual cells appear as discrete data items which can be further used or manipulated individually. In my case they are aligned to Content Control fields in a word document for automated document creation.

So... 'Get a Row' probably outputs an object whose parameters are accessible through some syntax (unknown to me) and which could then be operated on? If this is the case then I'd assume the parameters must be able to be worked on recursively.

I wonder if @DamoBird365  my ever-faithful problem solver can shed any light?

And thanks @Paulie78 for taking the time with me on this.

regards,

Lachlan

[Paulie78]

What is your actual objective? You can remove the character from the entire contents of an array or a single record in one action without looping through each record.

is that what you want to do? 

[HydrogenUtility]

Yes, that's the objective.

[Paulie78]

From a list rows action or a get row action? I will demonstrate for you, but want the demo to be as close as possible to what you’re trying to do.

[HydrogenUtility]

Thanks very much @Paulie78 . A single row, 100 cells wide, with data of multiple types. Using the action 'Get a row'.

[Paulie78 · Accepted solution]

Answer here:

https://youtu.be/Xb4psa35oWM

See how you get on.

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

[HydrogenUtility]

Brilliant @Paulie78 , thanks very much for going to the effort. That's a very nice workflow you have there. I'll implement it and let you know how I go.