Support Questions

Find answers, ask questions, and share your expertise

Useing a CSV file content in SQL condition

avatar
Contributor

I need to create a flow that update rows in my database based on csv file.
I have :


1- GetFile

statusclient_id
Active123
Inactive456

 

2- UpdateAttribute to update the Baobab_file (the schema is already in the AvroSchemaRegistry (collection)

 

3- SplitRecord to get each line 

statusclient_id
Active123

 

4- ExecuteSQL : update table_client set current_status = status where id = client_id

 

How can I do this I tried ${status} and \status but no luck

 

Thank you

1 ACCEPTED SOLUTION

avatar
Super Guru

Hi ,

 

I think after you split your csv you need to extract the values of both columns: status and client_id to attributes and then use in the ExecuteSQL processor, for that you need to :

 

1- convert the record to from CSV to JSON format using ConvertRecord Processor

2- use EvaluateJsonPath to extract both columns into defined attribute (dynamic properties). Make sure to set the Destination property to "flowfile-attribute".

After that you can reference those attribute in the SQL query as ${status} & ${client_id}, assuming thats how you called the attributes in step 2.

 

Another option if you dont want to use two processor , you can use ExtractText processor and provide regex to extract each value but you have to be careful how you define your regex for each value to make sure you are only pulling those values and nothing else.

 

Hope that helps.

If that answers your question please accept solution.

Thanks

View solution in original post

1 REPLY 1

avatar
Super Guru

Hi ,

 

I think after you split your csv you need to extract the values of both columns: status and client_id to attributes and then use in the ExecuteSQL processor, for that you need to :

 

1- convert the record to from CSV to JSON format using ConvertRecord Processor

2- use EvaluateJsonPath to extract both columns into defined attribute (dynamic properties). Make sure to set the Destination property to "flowfile-attribute".

After that you can reference those attribute in the SQL query as ${status} & ${client_id}, assuming thats how you called the attributes in step 2.

 

Another option if you dont want to use two processor , you can use ExtractText processor and provide regex to extract each value but you have to be careful how you define your regex for each value to make sure you are only pulling those values and nothing else.

 

Hope that helps.

If that answers your question please accept solution.

Thanks