Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

New Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

Super Guru

@Kamlesh Pant

In your ExecuteSql processor keep the below property value to

Normalize Table/Column Names

true

ExecuteSQL configs:

87603-es.png

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.

2 REPLIES 2
Highlighted

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

Super Guru

@Kamlesh Pant

In your ExecuteSql processor keep the below property value to

Normalize Table/Column Names

true

ExecuteSQL configs:

87603-es.png

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.

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

New Contributor

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

Don't have an account?
Coming from Hortonworks? Activate your account here