How to handle special character quote and \ in MS-SQL database Columns in NiFI ExecuteSQL ---NiFi

[#1]

Dear Community,

I have to extract the data from MSSQL table and one of the table has quoted and special characters in the table column names

as per below details

select "1stWelcomePackageSentDate", "1stWelcomePackageSentValue", "2ndReviewDate", "2ndReviewValue", "2ndWelcomePackageSentDate", "2ndWelcomePackageSentValue", AssignedCloser, AssignedCloserUpdated, AssignedOpener, AssignedOpenerUpdated, AssignedTo, AssignedToUpdated, AssignedUnderwriter, AssignedUnderwriterUpdated, BorrowerConfirmationLetterSentDate, BorrowerConfirmationLetterSentValue, "Completed/signed4506TDate", "Completed/signed4506TValue",

When I execute <select * from XYZ table> in ExecuteSQL nifi Processor it throwing error as below

SchemaParserException : illegal initial character in 1stWelcomePackageSentDate

Considering <select * form XYZ> may be an issue then I tried MS_SQL [] bracket column name SQL format as per below even though it has same error

SELECT [1stWelcomePackageSentDate] ,[1stWelcomePackageSentValue] ,[2ndReviewDate] ,[2ndReviewValue] ,[2ndWelcomePackageSentDate] ,[2ndWelcomePackageSentValue] ,[AssignedCloser] ,[AssignedCloserUpdated] ,[AssignedOpener] ,[AssignedOpenerUpdated] ,[AssignedTo] ,[AssignedToUpdated] ,[AssignedUnderwriter] ,[AssignedUnderwriterUpdated] ,[BorrowerConfirmationLetterSentDate] ,[BorrowerConfirmationLetterSentValue] ,[Completed/signed4506TDate] ,[Completed/signed4506TValue] ,[CompletePcgReceivedDate] From <TABLE>

Please help me how to solve this special characterized DB column names in NIFI

Thanks

Kamlesh

@kkpant75

[#2 · Accepted solution]

@Kamlesh Pant

In your ExecuteSql processor keep the below property value to

Normalize Table/Column Names

true

ExecuteSQL configs:

enclose column names in square brackets []

-

If the Answer helped to resolve your issue, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.

[#3]

Tons of thanks Shu , I was struggling to find the solution for last 2 days doing all nonsense things like turning off \" in short escape sequencing and others but did not work at all.

Thanks again