Spliting CSV files into 2 and saving them on Postgresql.

[#1]

Hello. I am currently using Nifi and Postgres via docker compose and is currently working on a sample project to get used to the tools.

I am given a sample CSV file with 4 columns (grade, breed, gender, price)

my scenario is as follows. 

1. Split the sample CSV file into 2. (rows that have null value and rows that dont. So in the case below, row 1, 4 will be in the same table, and 2, 3 in the same table)

Grade   ,BreedNm       ,Gender      ,Price
C++     ,beef_cattle   ,Female      ,10094
C++     ,milk_cow      ,Female      ,null
null    ,beef_cattle   ,Male        ,12704
B++     ,milk_cow      ,Female      ,16942

2. Save each of them into Postgresql tables

3. Also save the logs into a table, using ID and the sequence of the log table

Below is what I tried 

1. Split flowfile in two.

2. On one side, only save rows without null values and save them into a table 

3. On the other side, save rows with null values and also save them into a table 

How can I achieve the desired outcome? 

I am mainly having struggle with 

1. Only saving rows with null value 

2. Saving them into postgresql table 

Thank you for your help in advance! 

[#2 · Accepted solution]

Hi,

I'm not sure how are you splitting the CSV using UpdateAttribute. You probably need to use something like QueryRecord processor where you can query the records in SQL like language, there you create two routing properties: one for  ValuesWithNull and another for ValuesWithoutNull, then you redirect each to Save to the DB. Also why are you using the ConvertRecord Processor ? Are you converting the csv to another format like Json?

[#3]

I am an absolute newbie and was following other tutorials. 😂

I thought it was necessary to convert it to JSON to store the data into Postgresql.

Thanks a lot for the help ! 

[#4]

As @SAMSAL pointed out kindly checked the attached xml:

1) Get the csv file

2) query the csv file for null & not null using query record. The queries are represented by variables.

3) the null & not null variables are used for routing.

I hope it helps.

get xml from here: https://drive.google.com/file/d/1DjrW46GCRlVUTVGRP1HXRVKOREWCKUWe/view?usp=sharing