Upload contents of CSV as new maximum stock position in Exact Online

Question

I want to upload the contents of a CSV file as new values in Exact Online data set using for instance the following SQL statement:

update exactonlinerest..ItemWarehouses 
set    maximumstock=0 
where  id='06071a98-7c74-4c26-9dbe-1d422f533246' 
and    maximumstock != 0

I can retrieve the contents of the file using:

select * 
from   files('C:\path\SQLScripts', '*.csv', true)@os fle 
join   read_file_text(fle.file_path)@os

But seem unable to change the multi-line text in the file_contents field to separate lines or records.

How can I split the file_contents's field into multi lines (for instance using 'update ...' || VALUE and then running it through @@mydump.sql or directly using insert into / update statement)?

Answer 1

For now I've been able to solve it using regular expressions and then loading generated SQL statement into the SQL engine as follows:

select regexp_replace(rft.file_contents, '^([^,]*),([^,]*)(|,.*)$', 'update exactonlinerest..ItemWarehouses set maximumstock = $1 where code = $2 and maximumstock != $1;' || chr(13), 1, 0, 'm') stmts
,      'dump2.sql' filename
from   files('C:\path\SQLScripts', '*.csv', true)@os fle 
join   read_file_text(fle.file_path)@os rft

local export documents in stmts to "c:\path\sqlscripts" filename column filename

@c:\hantex\path\dump2.sql

However, it is error prone when I have a single quote in the article code.