MysqlBulkWriter issue with multiline DB column

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

Nismon8er
Posts: 10
Joined: Thu Apr 01, 2021 7:51 pm

MysqlBulkWriter issue with multiline DB column

Postby Nismon8er » Thu Jun 03, 2021 4:52 pm

Hello,

I have a TEXT column in a table and I have a flat file that is ^^ delimited. There are some situations where that data look something like this

^^1^^06/03/2021^^100.00^^This is a multiline
not and it needs
to remain as a multiline note^^1^^0^^
^^1^^06/03/2021^^100.00^^This is a multiline
not and it needs
to remain as a multiline note^^1^^0^^


But bulkwriter for some reason does not like that even though I specified that the ^^ is the delimiter.

I have used Reformat to strip out the "\n" and it works fine but boss does not want to lose the multilines.

^^1^^06/03/2021^^100.00^^This is a multiline note and it needs to remain as a multiline note^^1^^0^^
^^1^^06/03/2021^^100.00^^This is a multiline note and it needs to remain as a multiline note^^1^^0^^


Any solution?

Thanks!

Nismon8er
Posts: 10
Joined: Thu Apr 01, 2021 7:51 pm

Re: MysqlBulkWriter issue with multiline DB column

Postby Nismon8er » Tue Jun 08, 2021 6:35 pm

Ended up solving this.

I still stripped out the "/n" but instead of replacing them with blank string "" I replaced them with a literal '/n' and let the MySQL Utility handle the line break.

Worked like a charm

jandikovae
Posts: 64
Joined: Fri Nov 04, 2016 8:51 am

Re: MysqlBulkWriter issue with multiline DB column

Postby jandikovae » Mon Jun 14, 2021 9:55 am

Hi there,

You were right, your solution works like a charm.

Let me just add to this topic, that in the replace function we might need to be aware of the right linefeed character based on the OS used. In my case, for example, I was working on a Windows machine and the linefeed is "\r\n" then. When transforming the string, I had to also escape the backslash character with another backslash so that the linefeed is replaced properly. Therefore I was able to achieve the goal by using the following function in the Reformat component:

Code: Select all

$out.0.Field4 = replace($in.0.Field4,"\\r\\n",'\\r\\n');

This way the resulting field included the correct \r\n part of the string and has been interpreted by the MySQL database as a multiline string then.

Best Regards,
Eva
---
Eva Jandikova
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com


cron