INSERT CSV DATA INTO MSSQL DB

Question

I have many zip files each containing a csv file. The header of the csv file contain fields and some field names have space between words e.g "field one" instead of "field_one" which is the one in the database.

Please, what is the optima method to change the csv field names to match the ones in the database.

NOTE: I am using MSSQL as the DB. And my NIFI implementation is

1) GetFile : from a repository that contain zip files of very large size

2)UnpackContent: to extract the csv from the zipped file

3) PutDabaseRecord: to persist the csv data into db because of its large size.

Screenshot 2022-02-09 144059.png Screenshot 2022-02-09 144015.png

Thank you for your kind help.

araujo · Accepted Answer

You can use a QueryRecord processor before the PutDatabaseRecord.

You can add a relation to the QueryRecord processor with the following associated query:

select "field one" as field_one, "field two" as field_two, "field three" as field_three from flowfile

In the query above you can reference one field names using double-quotes if they have spaces. You can specify an alias for that column, which is the field name that will be used in the output.

Cheers,

Andre

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.