question Spliting CSV files into 2 and saving them on Postgresql. in Support Questions

Spliting CSV files into 2 and saving them on Postgresql.

Lewis_King — Tue, 12 Jul 2022 04:23:55 GMT

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!

Re: Spliting CSV files into 2 and saving them on Postgresql.

SAMSAL — Tue, 12 Jul 2022 13:30:40 GMT

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?

Re: Spliting CSV files into 2 and saving them on Postgresql.

rafy — Tue, 12 Jul 2022 20:01:21 GMT

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

Re: Spliting CSV files into 2 and saving them on Postgresql.

Lewis_King — Wed, 13 Jul 2022 00:41:56 GMT

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 !