Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

SOLVED Go to solution
Highlighted

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

New Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

Super Guru
@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.

4 REPLIES 4

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

Super Guru
@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.

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

New Contributor

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(" ","")}

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

Super Guru
@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

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

New Contributor

@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?