Power Platform Community Forum Thread Details

From the website I extraxt HTML table into the DataTable variable (some of the variables contain a comma)

In the next step I want to write the above variable into a column in the table

INSERT TABLE1(column1) VALUES ('%DataTable%');

Value is then added to the base (dataTable values are separated by a "comma" - some values in this dataTable may contain a comma) :

SELECT * FROM TABLE1
"xxxx,,
xxxx,yyyy,
xxxx,10528,00 yy,zzz"

Can I influence which seperator will be used?

I would like the seperator "|" to be used

SELECT * FROM TABLE1
"xxxx||
xxxx|yyyy|
xxxx|10528,00 yy|zzz"

Categories:

Power Automate Desktop

Yes, it is possible, but you will need to do some text processing for that to happen.

If you want the entire table to be inserted to a database as a single string, but separate rows to be separated by newlines, while the items inside a row are separated by "|", you'll need to create a list, loop through the table, use Join text on each row to join it using "|" as a separator, then add the result to the list and then when you're done with all those rows, use Join text again to join the list into a single string using newline as the separator.

It should look somewhat like this:

You can then use %Result% in your insert statement.

Here's a snippet you can copy and paste directly into PAD to create the actions for you:

Variables.CreateNewList List=> List
LOOP FOREACH CurrentItem IN DataTable
 Text.JoinText.JoinWithCustomDelimiter List: CurrentItem CustomDelimiter: $'''|''' Result=> JoinedText
 Variables.AddItemToList Item: JoinedText List: List
END
Text.JoinText.JoinWithDelimiter List: List StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> Result

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.