Replace quotation marks in output

(1) Share

Report

Posted on by dash_7

Hello,

I have an excel file that I load using 'List rows in table'. With 'Select' I chose the columns needed.

Next, using 'Compose' I select the output. I believe this generates a json type structure. Finally I upload this output to a SQL Server using 'Execute a SQL Query'.

Everything works fine - the json output i being uploaded.

However in the the excel file a strings that include quotation marks (') - and this messes with the json structure apparently.

I tried to replace the ' character - see the screenshot below.

However this leads to the following error message:

Unable to process template language expressions in action 'Compose_2' inputs at line '1' and column '23396': 'The template language function 'replace' expects its first parameter 'string' to be a string. The provided value is of type 'Array'. Please see https://aka.ms/logicexpressions#replace for usage details.'.

Bildschirmfoto 2021-04-24 um 11.20.39.png

How can I remove the quotation marks from my output?

Thanks!

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

CFernandes 8,402 Most Valuable Professional on at

Like (1)

Report

Hey @dash_7

Try to Convert the output of "Select" to string .

replace(string(body('Select')),variable('Quote'),'')

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
dash_7 77 on at

Like (0)

Report
thanks @CFernandes that did the trick!

However now that the string would get submitted to the server (30k lines in the excel file) I get this error message in the "Execute a SQL Query" part:
{ "status": "413", "message": "The request is larger than 52428800 bytes." }
Is the generated json string to big for power automate to handle? Is there a way around other than splitting the excel file?

Was this reply helpful? Yes No
CFernandes 8,402 Most Valuable Professional on at

Like (0)

Report

@dash_7

Ahh We have a limitation - You will have to split the excel file - We have an idea in Community - https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Increase-the-maximum-buffer-size-from-100/idc-p/197615

Please Vote- Up.

If was able to answer your original question, please mark it as an answer.

Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
dash_7 77 on at

Like (0)

Report

thanks @CFernandes for the info and the solution for my original question. I did mark it as solution if anybody else stumbles upon the same problem!

However I am kind of lost now...
I thought I could use Power Automate (which I have practically no experience with) to solve a problem that I thought was rather "boring" and easy: send excel data to sql server.

I tried two ways in power automate:
- "List rows in table" and than loop through with "Insert Row". For 30k rows in the excel it would take several hours
- and this solution with "execute sql query" which works but splitting the file each time is not the best solution.

Is there a best practice to solve this kind of task that I missed?
Thanks!

Was this reply helpful? Yes No