You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by "LEZIER Maxime (ITNOVEM)" <m....@sncf.fr> on 2023/01/10 11:31:54 UTC
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.
RE: SQL - Record - Remove newline character
Posted by "LEZIER Maxime (ITNOVEM)" <m....@sncf.fr>.
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 <is...@virtualsciences.nl>
Envoyé : mercredi 11 janvier 2023 14:30
À : users@nifi.apache.org <us...@nifi.apache.org>
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) <m....@sncf.fr>
Verzonden: dinsdag 10 januari 2023 17:24
Aan: users@nifi.apache.org; Christopher J. Amatulli <ca...@revealdata.com>
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
m.lezier@sncf.fr<ma...@sncf.fr>
LD OPS FLUX : ITN_LD_BDF_FluxDevOps@sncf.fr<ma...@sncf.fr>
De : Christopher J. Amatulli via users <us...@nifi.apache.org>>
Date : mardi, 10 janvier 2023 à 12:53
À : users@nifi.apache.org<ma...@nifi.apache.org> <us...@nifi.apache.org>>
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) <m....@sncf.fr>>
Sent: Tuesday, January 10, 2023 6:32 AM
To: users@nifi.apache.org<ma...@nifi.apache.org>
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
RE: SQL - Record - Remove newline character
Posted by Isha Lamboo <is...@virtualsciences.nl>.
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) <m....@sncf.fr>
Verzonden: dinsdag 10 januari 2023 17:24
Aan: users@nifi.apache.org; Christopher J. Amatulli <ca...@revealdata.com>
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
m.lezier@sncf.fr<ma...@sncf.fr>
LD OPS FLUX : ITN_LD_BDF_FluxDevOps@sncf.fr<ma...@sncf.fr>
De : Christopher J. Amatulli via users <us...@nifi.apache.org>>
Date : mardi, 10 janvier 2023 à 12:53
À : users@nifi.apache.org<ma...@nifi.apache.org> <us...@nifi.apache.org>>
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://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>
From: LEZIER Maxime (ITNOVEM) <m....@sncf.fr>>
Sent: Tuesday, January 10, 2023 6:32 AM
To: users@nifi.apache.org<ma...@nifi.apache.org>
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
Re: SQL - Record - Remove newline character
Posted by "LEZIER Maxime (ITNOVEM)" <m....@sncf.fr>.
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
m.lezier@sncf.fr<ma...@sncf.fr>
LD OPS FLUX : ITN_LD_BDF_FluxDevOps@sncf.fr<ma...@sncf.fr>
De : Christopher J. Amatulli via users <us...@nifi.apache.org>
Date : mardi, 10 janvier 2023 à 12:53
À : users@nifi.apache.org <us...@nifi.apache.org>
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:/stackoverflow.com/questions/951518/replace-a-newline-in-tsql__;!!Nto2ANp9CeU!CbIP4WUJ60KAMT0_rp41qg1YfM5SwsigmY7goCpp21eUNNJ8ROuB_AKWUye3Wl_gKYYegbSs8VVFQ8g4AA$>
From: LEZIER Maxime (ITNOVEM) <m....@sncf.fr>
Sent: Tuesday, January 10, 2023 6:32 AM
To: users@nifi.apache.org
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
RE: SQL - Record - Remove newline character
Posted by "Christopher J. Amatulli via users" <us...@nifi.apache.org>.
"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://stackoverflow.com/questions/951518/replace-a-newline-in-tsql>
From: LEZIER Maxime (ITNOVEM) <m....@sncf.fr>
Sent: Tuesday, January 10, 2023 6:32 AM
To: users@nifi.apache.org
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.