question Re: please suggest me steps,how i can insert csv file(in local system) into database table. i have configured GetFile and PutSql processor but i am getting issue to configured replace text processor. in Archives of Support Questions (Read Only)

please suggest me steps,how i can insert csv file(in local system) into database table. i have configured GetFile and PutSql processor but i am getting issue to configured replace text processor.

santosh_hencha — Fri, 12 May 2017 14:32:37 GMT

Re: please suggest me steps,how i can insert csv file(in local system) into database table. i have configured GetFile and PutSql processor but i am getting issue to configured replace text processor.

mburgess — Sat, 13 May 2017 09:33:04 GMT

As of NiFi 1.2.0, after GetFile you can use the PutDatabaseRecord processor with a CSVReader, giving it a schema if you know the layout, or if the CSV file has a header row you can get the column names from that using "Use String Fields From Header" for the Schema Access Strategy property.

Prior to NiFi 1.2.0, after GetFile you probably want a SplitText to get each row in its own flow file. Do you know the number of columns for the CSV file? If so then you can use a regex in ExtractText; assuming there were four columns you might have a dynamic property called "column" set to something like:

([^,]+),([^,]+),([^,]+),([^,]+)

That should give you attributes "column.1", "column.2", "column.3", and "column.4". Then you can use ReplaceText to generate a SQL statement, perhaps something like:

INSERT INTO myTable VALUES(${column.1},${column.2},${column.3},${column.4})

Then send that to PutSQL. Another option (prior to NiFi 1.2.0) is to convert the CSV to Avro (see this HCC article), then ConvertAvroToJSON, then ConvertJSONToSQL, then PutSQL.

Re: please suggest me steps,how i can insert csv file(in local system) into database table. i have configured GetFile and PutSql processor but i am getting issue to configured replace text processor.

santosh_hencha — Sat, 13 May 2017 22:29:48 GMT

thanks for response...

i have hard coded in replacetext processor (for testing purpose) such as: insert into table_name (ID) values ('2') and in putsql processor i am getting same insert statement(in data provenance input claim view). and same table table_name i have create in data base but i am not getting any error and it's not going to insert.. connection with data base table is fine(no issue)..

flow is getfile>>replacetext>>putsql.

Re: please suggest me steps,how i can insert csv file(in local system) into database table. i have configured GetFile and PutSql processor but i am getting issue to configured replace text processor.

ask_bill_brooks — Tue, 08 Oct 2019 10:25:26 GMT

Moved because this question differed significantly from the original thread and the original question was marked 'solved' in May 2017.