Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Explorer

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

avatar
Master 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.

View solution in original post

5 REPLIES 5

avatar
Master 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.

avatar
Explorer

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

avatar
Master 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

avatar
Explorer

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

avatar
Explorer

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