Dear VisualCron team and members,
I would like to ask you for help with regex replace function, where I need to replace each new line (\r\n) with new line and the SQL words (\r\n insert into [table] values(')

As an example let's say that I have these data:

Adam,20
Roman,30

And I want to get:
insert into [table] values ('Adam,20
insert into [table] values ('Roman,30

I decided to use the REGEX Replace function in this way:
{REGEX(Replace|
Adam,20
Roman,30|\r\n|\r\n insert into [Table] values (')}

But the result is:
\r\n insert into [Table] values ('Adam,20\r\n insert into [Table] values ('Roman,30

So the last part of replace argument is not taking \r\n as new line and it is pasting as text instead.
Is there any way how can I rephrase it to use \r\n as new line?

Thank you in advance :)

Best Regards,

Hi

I think you should chose another aproach to solving this. I assume you have this data in a textfile, and you read this into output in visual cron?

If that's the case you should loop over the result set, and do an insert for each line. No need to use regex.

Alternatively you can bulk load the textfile directly into Sql server (if that's your database) using Format files or an SSIS package. This is the approach I would take if your file is huge, but for a few records, looping and inserting is fine.

Shout out if you need help with the looping part.


Thomas

Hi Thomas,
thank you for your feedback. Yes, I have the text file, which I'm reading and then I have a write job, which is checking the previous output and doing the replace over that output.

But let me try the loops - it is actually good idea.

I will update you with the result (of loop implementation) for future references.

Hi Thomas,
thank you for your idea - the loop really helped.

In the end my write job contains this:

insert into [Table] values('{LOOP(CurrentValueXLine)}

Thank you very much!

Hi

I just thought about it a little bit longer....The drawback with the loop, is that you open/close a connection to the database on each iteration. Probably not a disaster, but it's unnecessary. So instead of regex, you could use the 'Get column by row...' function to create the statments you mentioned. That way you insert everything in one go.

Basically you were right the first time :)


Thomas