Hello everyone, and thanks for reading and helping in advance.
I have a flow that watches for excel files and inserts the data dynamically to an sql server database.
My problem begins when some of the strings in the excel files have astrophes, and sometimes they dont. As mentioned, its dynamic.
The astrophes are not inserted well into the SQL DB, instead of "O'brien" for example it inserts "O#39&brien".
This is the template im using to insert the data to the database-
INSERT INTO dbo.name1(Num_ID,Date,Customer_Number,Customer_Name);
VALUES ('{{Num_ID}}','{{Date}}','{{Customer_Number}}','{{Customer_Name}}');
I've tried replacing all the astrophes in the above template to double quotes (") in order to cancel the astrophe and that caused syntax errors.
I dont mind even removing the astrophes from the strings, so I added a change node in which I search for ' and replace it with blank. However, it leads to syntax errors no matter which format I use (msg.payload, msg.payload.Customer_Name, msg.Customer_Name).
I am new to this and have been struggling with this for over a month. I highly appreciate any help. Thanks.