Hello,
Really thanks for your answer.
Finally, I choose this solution :
With the ExecuteSQL I use a CSV writer with a special character for separate
rows.
After it’s a ReplaceText which remove all unwanted character (\n, \r,, \t …) in
all content and other ReplaceText which replace the record separation character
with a new line character.
I’m not sure than it’s the easiest (and more efficient) way but the job is done.
Other question :
Is it possible to use UpdateRecord for all field, something like :
/* : ${field.value:replace(‘\n’,’ ‘)}
For applying a transformation in one shot, not field by field ?
Thanks.
Maxime
________________________________
De : Isha Lamboo <[email protected]>
Envoyé : mercredi 11 janvier 2023 14:30
À : [email protected] <[email protected]>
Objet : RE: SQL - Record - Remove newline character
Hi Maxime,
I would try to first use a Recordwriter that doesn’t depend on newlines for
record separation, json being the easiest. Then you can use something like
ReplaceText or JoltTransformJSON (safer but more complex to configure) to
remove the newlines from the field values. Then you use ConvertRecord to get
your CSVs.
Do check if the double conversion has had any unwanted effects on your data,
especially numeric fields.
Regards,
Isha
Van: LEZIER Maxime (ITNOVEM) <[email protected]>
Verzonden: dinsdag 10 januari 2023 17:24
Aan: [email protected]; Christopher J. Amatulli <[email protected]>
Onderwerp: Re: SQL - Record - Remove newline character
Hello,
It’s a Oracle SQL.
I could use REPLACE in request, but there’s a lot of field/table and I’d like
to find a generic solution for all.
Maxime Lézier
Leader CloudOps Flux
ITNOVEM
PARTENAIRE TECHNOLOGIQUE SNCF
19 rue d’Amiens, 59800 Lille
[email protected]<mailto:[email protected]>
LD OPS FLUX :
[email protected]<mailto:[email protected]>
De : Christopher J. Amatulli via users
<[email protected]<mailto:[email protected]>>
Date : mardi, 10 janvier 2023 à 12:53
À : [email protected]<mailto:[email protected]>
<[email protected]<mailto:[email protected]>>
Objet : RE: SQL - Record - Remove newline character
“which” SQL server are you querying? If it’s MSSQL, this is best done through
TSQL (REPLACE(REPLACE(@str, CHAR(13), ' '), CHAR(10), ' '))
Replace a newline in TSQL - Stack
Overflow<https://urldefense.com/v3/__https://eur03.safelinks.protection.outlook.com/?url=https*3A*2F*2Furldefense.com*2Fv3*2F__https*3A*2Fstackoverflow.com*2Fquestions*2F951518*2Freplace-a-newline-in-tsql__*3B!!Nto2ANp9CeU!CbIP4WUJ60KAMT0_rp41qg1YfM5SwsigmY7goCpp21eUNNJ8ROuB_AKWUye3Wl_gKYYegbSs8VVFQ8g4AA*24&data=05*7C01*7Cisha.lamboo*40virtualsciences.nl*7Cf7fc473451dd4be5a00108daf3271f91*7C21429da9e4ad45f99a6fcd126a64274b*7C0*7C0*7C638089646624767566*7CUnknown*7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0*3D*7C3000*7C*7C*7C&sdata=tpwakVhUexAvIkvwsmVaPlatpzhcVkH3bo0JZnjE3hY*3D&reserved=0__;JSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJQ!!Nto2ANp9CeU!H8oh0pgnD47HOjnqTeHijUEO14oqCV25qgXUtI6fMOwv5ic6tAY2xAb-q9CNjmQKPKpk13qFAovAQ2B-OUhSqevxTJ7dPQ$>
From: LEZIER Maxime (ITNOVEM) <[email protected]<mailto:[email protected]>>
Sent: Tuesday, January 10, 2023 6:32 AM
To: [email protected]<mailto:[email protected]>
Subject: SQL - Record - Remove newline character
Hello,
We have a usecase where we fetch rows from oracle table.
I use the ‘ExecuteSQLRecord’ with a Record Writer type CSV.
For some text field, there’s crlf character and my csv is “broken” with the
render multiline result.
Is there an option in the csv writer for removing “\n” from all fields ?
Should I use an UpdateRecord ?
What is the better way for removing all new line character inside all field
from my results ?
I use a NiFi cluster (3 nodes), version 1.15.3 on Ubuntu, java 11.
Thanks for your help
Maxime Lézier
Interne
-------
Ce message et toutes les pièces jointes sont établis à l'intention exclusive de
ses destinataires et sont confidentiels. L'intégrité de ce message n'étant pas
assurée sur Internet, la SNCF ne peut être tenue responsable des altérations
qui pourraient se produire sur son contenu. Toute publication, utilisation,
reproduction, ou diffusion, même partielle, non autorisée préalablement par la
SNCF, est strictement interdite. Si vous n'êtes pas le destinataire de ce
message, merci d'en avertir immédiatement l'expéditeur et de le détruire.
-------
This message and any attachments are intended solely for the addressees and are
confidential. SNCF may not be held responsible for their contents whose
accuracy and completeness cannot be guaranteed over the Internet. Unauthorized
use, disclosure, distribution, copying, or any part thereof is strictly
prohibited. If you are not the intended recipient of this message, please
notify the sender immediately and delete it.
CONFIDENTIALITY NOTICE - This email message is intended only for the use of the
individual or entity to which it is addressed, and may contain information that
is privileged, confidential and exempt from disclosure under applicable law. If
the reader of this message is not the intended recipient, you are hereby
notified that any dissemination, distribution or copying of this communication
is strictly prohibited. If you have received this communication in error,
please notify the sender immediately by telephone, and destroy any hard copies.
Interne
Interne
Interne
Interne
