Support Questions

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

How to convert Excel filed names to as our like column names in Apache nifi

avatar
Explorer

Dears,

 

We have a small issue with excel sheet  data loaded into DB table.

We are unable to load the data from excel in nifi if  the field name have space or symbols.

ex: In excel field name is (Cost Center) , In DB (Cost Center) got error like column not matched.

if we send fileld name like Cost_Center then it is worked fine.

So, we request you that is there any possible in Nifi we can change (Cost Center (field name )  in excel  to add(_) in that space to make it work?

ex:2 P&L item  in excel field name also  not work out because of & symbol.

 

Could you please suugest any approach for this if possible?

4 REPLIES 4

avatar

@ushasri I believe the solution here is to use the Record based processors with a specified schema.  This allows you to provide the correct schema to the Reader and the Writer regardless of the field names in original excel data source.

avatar
Explorer

Sorry @steven-matison ,

I could n't Understand properly .

Request  you to  please elobrate on this.

My requirement is What ever the field name in excel, when it reach to nifi it should be like space between two words convert into _.

ex: in excel -- steven matison

    to nifi -- steven_matison

because with space field names  are not match with column names in db.

avatar
Explorer

Dears,

Can you please suggest which record processor is exactly suitable for this scenario?

Thanks in Advance.

avatar

@ushasri,


If you are reading an Excel File, you can use an ConvertRecord processor, in which you define an Record Reader (CSVReader most likely) where you tell NiFi how to read the value (value separator). Next, with the help of a Record Writer (I assume CSV again or maybe Avro) you define how your data should look like, including the header. For both the Reader and the Writer you will have to define a schema, where you can do the replace of blank space with underline.