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

Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

Solved Go to solution

Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

New Contributor

I am trying to copy CSV files from my local directory into a SQL Server database running in my local machine by using Apache NiFi.

 

I am new to the tool and I have been spending few days googling and building my flow. I managed to connect to source and destination but still I am not able to populate the database since I get the following error: "None of the fields in the record map to the columns defined by the tablename table."

 

I have been struggling with this for a while and I have not been able to find a solution in the Web.

Any hint would be highly appreciated.

 

Here are further details.

 

I have built a simple flow using GetFile and PutDatabaseRecord processorsflow.png

 

My input is a simple table with 8 columns

csv_table.png

 

My configurations for GetCSV process are here (I have added the input directory and left the rest as default)

getcsv.png

 

The configuration for PutDatabaseRecord process is here (I have referred to the CSVReader and DBCPConnectionPool controller services, used the MS SQL 2012+ database type (I have 2019 version), configured INSERT statement type, inserted the schema and correct table name and left everything else as default)

put_database_record.png

 

The CSVReader configuration looks as shown here (Schema Access Strategy = Use String Fields From Header; CSV Format = Microsoft Excel)

csv_reader.png

 

And this is the configuration of the DBCPConnectionPool (I have added the correct URL, DB driver class name, driver location, DB user and password)

dbcp.png

 

Finally, this is a snapshot of the description of the table I have created in the database to host the content

db.png

 

Many thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

New Contributor

@justenji 

 

Thanks a lot! The suggestion you proposed works great!

 

However, the source of the problem was not a lack of schema definition. In fact, I managed also to have a working flow with the option Schema Access Strategy = Use String Fields From Header in the CSVReader processor.

 

The warning "None of the fields in the record map to the columns defined by the tablename table." is also obtained when the processor is not able to find the table and this can happen also when the table name is correctly configured in PutDatabaseRecord but there is some issue with user access rights (which ended up to be the actual cause of my error ...).

View solution in original post

6 REPLIES 6
Highlighted

Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

Expert Contributor

@BerniHacker 

As far as I see the cause could be different column names and in the PutDatabaseRecord the option "Unmatched Column Behavior" is set to "Fail on Unmatched Columns".

 

I suppose the options concerning "Unmatched Field Behavior" or "Unmatched Column Behavior" in connection with "Translate Field Names" causing your problem.

 

Perhaps you like to post your schema-definition?
But at the moment I can't give you a concreter answer.

Highlighted

Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

Expert Contributor

@BerniHacker 
Sorry, my reply was not good.
Did not see that you are working with "Use String Fields From Header" and not a separate schema.
I will try to get it work on my local installation.
If you found a solution by so kind to let us know. Thanks.

Highlighted

Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

New Contributor

@justenjiThanks a lot for offering your help!

 

I am really stuck with this. I tried many different things (for example, table name = dbo.alpha, CSV Format = Customer Format, ...), always with same result.

 

Now I changed Unmatched Column Behaviour (Unmatched Column Behaviour = Ignore Unmatched Columns). Again same result.

 

I also tried with xlsx files by using a ConvertExcelToCSVProcessor in the middle. Same result.

 

 

Highlighted

Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

Expert Contributor

@BerniHacker 

The whole day I couldn't achive to work on this further. Now I found a solution but at the moment I can't document this detailed because "work is calling".

In small words:
GetFile -> UpdateAttribute (to set NiFi-attribute schema.name) -> PutDatabaseRecord (with CSV-Record Reader)
BUT the reader has to use a schema which is defined in the controller services as AvroSchemaRegistry and there has to exist a avro schema for the structure.

Maybe this will help you further.


Have a look here https://community.cloudera.com/t5/Support-Questions/Loading-a-simple-CSV-file-using-nifi-PutDatabase...


Tomorrow I will describe my solution detailed! Sorry...

Highlighted

Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

Expert Contributor

@BerniHacker 

Come on work can wait...

Here my description with NiFi 1.12.1. on a windows standalone installation.

This is my test csv.

id;title;first;last
1;miss;marlene;shaw
2;ms;letitia;jordan
3;mr;todd;graham
4;mr;seth;martinez
5;mr;guy;mckinney
6;ms;anna;smith
7;mr;johnny;johnson
8;mrs;robin;white
9;miss;allison;williams
10;mrs;erika;king

 

This is my flow:
FlowFlow

 

This is my sql table.
SQL tableSQL table

This is the configuration of the csv-reader.
Note the green bordered information!
Controller Service - dsv readerController Service - dsv reader

The schema has to be defined in a controller service of the type "AvroSchemaRegistry".
AvroSchemaRegistryAvroSchemaRegistry

 

With the UpdateAttribute you set the NiFi-attribute   schema.name  to the concrete schema you use.
UpdateAttribute schema.nameUpdateAttribute schema.name

 

And last but not least configure the PutDatabaseRecord.
PutDatabaseRecordPutDatabaseRecord

Here is the result in my sql table.

SQL ResultSQL Result

Hope this helps and works for you. Bye!

Highlighted

Re: Error when copying CSV files from Windows directory into SQL Server DB by using Apache NiFi

New Contributor

@justenji 

 

Thanks a lot! The suggestion you proposed works great!

 

However, the source of the problem was not a lack of schema definition. In fact, I managed also to have a working flow with the option Schema Access Strategy = Use String Fields From Header in the CSVReader processor.

 

The warning "None of the fields in the record map to the columns defined by the tablename table." is also obtained when the processor is not able to find the table and this can happen also when the table name is correctly configured in PutDatabaseRecord but there is some issue with user access rights (which ended up to be the actual cause of my error ...).

View solution in original post