Skip to main content
sachin_32
sachin_32
Silver ⭐️⭐️⭐️
March 1, 2022
Solved

Pick Column Based on Index Number

I have the Flow File which has Duplicate Column i want to pick the Column threw index Number,
is it possible to do with Query Record or any Processor 

Note: Column Will change with every new Flow File coming 

araujo
araujoAnswer
Platinum ⭐️⭐️⭐️
March 3, 2022

Here's the flow template for those who have older nifi versions

-- Was your question answered? Please take some time to click on "Accept as Solution" below this post. If you find a reply useful, say thanks by clicking on the thumbs up button.
View in thread

9 replies

araujo
Platinum ⭐️⭐️⭐️
araujo
Platinum ⭐️⭐️⭐️
March 1, 2022

Hi, @sachin_32 ,

 

I guess this is coming as a CSV file, right?

You can achieve what you want with the following approach:

 

  • Configure your CSV Reader to ignore and skip the header line (if any)
  • Configure your CSV Read to use the following schema:

 

{
  "type": "record",
  "name": "SensorReading",
  "namespace": "com.cloudera.example",
  "doc": "This is a sample sensor reading",
  "fields": [
    { "name": "c1", "type": "string" },
    { "name": "c2", "type": "string" },
    { "name": "c3", "type": "string" }
  ]
}​

 

 

Ensure you use a schema with the exact number of columns that your input file has.

 

  • In your QueryRecord you can then refer to the columns as c1, c2, etc...:

 

select c1, c2, c3
from flowfile​

 

 

 

 

Cheers,

André

 

Translate
-- Was your question answered? Please take some time to click on "Accept as Solution" below this post. If you find a reply useful, say thanks by clicking on the thumbs up button.
sachin_32
sachin_32Author
Silver ⭐️⭐️⭐️
March 1, 2022

Thanks for your Suggestion   but in this case i don't have any Exact Number of columns it will keep changing with incoming flow file it completely depends on the Flowfile And the scenario is i have few columns which can directly pick by giving the name of column  but for some column which is coming more than one for that i need to setup like indexing  and it's around 10-15 files which has this kind of issues so can you suggest for that ?

Translate
araujo
Platinum ⭐️⭐️⭐️
araujo
Platinum ⭐️⭐️⭐️
March 2, 2022

@sachin_32 ,

 

Here one different attempt. You can send your CSV flowfile to a ReplaceText processor with the following configuration:

araujo_0-1646219751801.png

The Search Value is the following regular expression:

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

And the Replacement Value is:

$1,$2,$3,$4,col_a$6

 

Each capture group ([^,\n]*) will match the name of one column. If you want to keep the name of that column you just replace it with $x, where x is the position of the column.

If you want to replace the column with another name, e.g. col_a, you just type the name of the new column name in the replacement instead.

 

The last capture group (.*), will match the remaining of the first line. This way you don't need to match every single column, only the ones up to the position you want to replace.

 

As an example, for this input:

A,B,C,D,A
1,2,3,4,5
2,3,4,5,6

The above replacement will generate this output:

A,B,C,D,col_a
1,2,3,4,5
2,3,4,5,6

 

HTH,

André

 

Translate
-- Was your question answered? Please take some time to click on "Accept as Solution" below this post. If you find a reply useful, say thanks by clicking on the thumbs up button.
sachin_32
sachin_32Author
Silver ⭐️⭐️⭐️
March 3, 2022

Thanks for the Help 🙂

Translate
    Terms & ConditionsAccessibility statement