I have csv files and I want to move the content of files along with its meta data (File name, source (To be hard coded), control number (Part of file name - to be extracted from file name itself) using NiFi. So here is the sample File name and layout -
File name - 12345_user_data.csv (control_number_user_data.csv)
source - Newyork
CSV File Content/columns -
Fields - abc1, abc2, abc3, abc4
values - 1,2,3,4
Postgres Database table layout
Table name - User_Education
fields name -
control_number, file_name, source, abc1, abc2, abc3, abc4
Values -
12345, 12345_user_data.csv, Newyork, 1,2,3,4
I am planning to use below processors -
ListFile
FetchFile
UpdateAttributes
PutDatabaseRecords
LogAttributes
But I am not sure how to combine the actual content with the meta data to load into one single table. Please help
You can use UpdateRecord
before PutDatabaseRecord
to add the control_number
, file_name
, and source
fields to each record, setting the populating the "Replacement Value Strategy" property to "Literal Value" and use Expression Language to set the values to the corresponding attributes.
For example, you could have a user-defined property /file_name
set to ${filename}
, that will add the file_name
field to each record and set the value to whatever is in the "filename" attribute of the FlowFile.