Support Questions
Find answers, ask questions, and share your expertise

NiFi ReplaceTextWithMapping CSV

NiFi ReplaceTextWithMapping CSV

New Contributor

Hi,

 

I'm new to NiFi and investigating if it's the right tool for our use case. So I'm trying to setup a simple flow, that will load a csv file, transform one of the fields and write the results to a MySQL table. I've manage to load the file and write to MySQL, however I'm struggling to do the bit in the middle.

Here is my test CSV:

 

 

UserId,Name,DepartmentId,Age
1,John,1,49
2,Jane,1,38
3,Sarah,2,27
4,Bill,3,34
5,Bob, ,22

 

 

 I'm using ReplaceTextWithMapping to try and replace the DepartmentId field with the following mapping file:

 

 

1<tab>Technical
2<tab>HR
3<tab>Commercial

 

 

I'm using the following RegEx (which could be the problem):

 

 

^[0-9](?:[^\,]*\,){2}([^,]*)

 

 

On RegExr.com it is shown with the multiline flag /^[0-9](?:[^\,]*\,){2}([^,]*)/gm I'm not sure how I set this in NiFi. I've set Matching Group to 1.

I need to be able to select the nth column in a CSV file, is there some better RegEx to do this?

 

I also have a problem that ReplaceTextMapping says it is designed to match a simple regex that will map one group of non-whitespace characters to another group of characters (can have white space and back references). However my DepartmentId field can sometimes contain whitespaces, how can I get around this issue?

 

Thanks for any advice.

 

Regards,

Adam