How can I change specific column title in CSV file using NiFi?

[#1]

This is my dataflow -

Getfile (CSV) -> PutSQL (To truncate the database table in MS SQL) -> PutDatabaseRecord (To Insert record in the table) -> LogAttributes upon success or failure

I have following header row in CSV file.

Date

Location

Name

Manager

Division

Revenue

PV

NMS OnLine

NMS LHSC

RMS OnLine

RMS LHSC

I want to change the name of the last four column

From

NMS OnLine

NMS LHSC

RMS OnLine

RMS LHSC

To

NMSOnLine

NMSLHSC

RMSOnLine

RMSLHSC

as Original name is not matching to table column name in database.

I am trying to use ReplaceTextProcessor but it is not working.

How do I achieve this?

@Shu

[#2 · Accepted solution]

@Sandip Dobariya

If your csv file size is not huge then you can use on of the mentioned way in this link.

(or)

By using record oriented processor(ConvertRecord..etc) which more efficient way of doing this task.

Configure the ConvertRecord processor with CsvReader and CsvWriter controller service(include header line 'False'). then use ReplaceText processor to prepend the header line to the csv file.

Refer to this link for more reference.

[#3]

Hi Shu,

Let me thank you for your quick response and it is really helpful.

I have tried both way and it worked using ConvertRecord and ReplaceText.

May I know the reason why it is not working using just ReplaceText with huge file? My CSV file contains >60K reocrds.

I was using below ReplaceText configs as you advised but it is getting failed.

Search Value

(.*)

Replacement Value

${'$1':replace(" ","")}

[#4]

@Sandip Dobariya

Try with this configurations in ReplaceText processor and this configs, we are only applying nifi expression language replace function on first line of the extracted content not on all lines and this expression replaces spaces in first line.

Search Value:

 (?s)(^[^\n]*)(.*$) 

Replacement Value:

 ${'$1':replace(" ","")}$2 

Replacement Strategy

RegexReplace 

Evaluation Mode

Entire text

Input:

Date,Location,Name,Manager,Division,Revenue,PVNMS OnLine,NMS LHSC,RMS OnLineRMS LHSC

Output:

Date,Location,Name,Manager,Division,Revenue,PVNMSOnLine,NMSLHSC,RMSOnLineRMSLHSC

[#5]

@Shu

Though it did not work with this one and flow file going to failure relationship without modification. It's alright as I get it worked with Convert Record. Thanks for help.

May I know Is there any way to avoid change of header in CSV and We can map CSV header to database table column directly similar to any ETL tool?

[#6]

if I want to replace more then one How? 

for example I tried this 

${'$1':replace("ClientName","Client Name"),'$2':replace("Ageinyears ","Age in years")}

but not work