MysqlBulkWriter issue with multiline DB column

nismon8er

• June 03, 2021 00:00

• Answered

Follow

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

  • June 08, 2021 17:35

  0

  Comment actions Permalink

  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
• 

  admin

  • June 14, 2021 08:55

  0

  Comment actions Permalink

  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:
  
  

  $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

Please sign in to leave a comment.