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

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

ushasri — Thu, 11 May 2023 05:01:27 GMT

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?

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

steven-matison — Thu, 11 May 2023 12:42:58 GMT

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

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

ushasri — Thu, 11 May 2023 13:22:17 GMT

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.

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

ushasri — Fri, 12 May 2023 04:46:26 GMT

Dears,

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

Thanks in Advance.

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

cotopaul — Fri, 12 May 2023 11:28:02 GMT

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