You’re offline. This is a read only version of the page.
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:
Share
ReportUnable 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.'.
How can I remove the quotation marks from my output?
Thanks!
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!
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!
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:
All responses (
Answers (
{
"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?
Hey @dash_7
Try to Convert the output of "Select" to string .
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!
#1
stampcoin
87
#2
Michael E. Gernaey
70
Super User 2025 Season 1
#3
David_MA
48
Super User 2025 Season 1
