Support Questions
Find answers, ask questions, and share your expertise

INSERT CSV DATA INTO MSSQL DB

Contributor

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.pngScreenshot 2022-02-09 144015.png

 

Thank you for your kind help.

1 ACCEPTED SOLUTION

Master Collaborator

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

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.

View solution in original post

1 REPLY 1

Master Collaborator

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

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.
; ;