question Re: Nifi || Mail || Display csv files content in tabular format. in Support Questions

Nifi || Mail || Display csv files content in tabular format.

ashishtiw26 — Mon, 30 Dec 2019 08:11:57 GMT

Hi All,

I have to display the records of csv file over mail body in tabular format using Putmail.

I am extracting records by executing sql which is scheduled at some duration and than forwarding the same using putmail to some specific users.

Can someone please tell me how to display the data of generated csv file in tabular format.

Re: Nifi || Mail || Display csv files content in tabular format.

stevenmatison — Mon, 30 Dec 2019 14:30:41 GMT

A replaceText processor can change from Comma Separated to Tab Separated. This would be the easiest option. Configured with [tab] in Search Value, and [,] in Replacement Value:

Re: Nifi || Mail || Display csv files content in tabular format.

ashishtiw26 — Mon, 30 Dec 2019 15:43:14 GMT

Hi,

I tried doing the same by replacetext but I am getting the output as:

Release Cause,Previous Count,Current Count,Change_Ratio,SEVERITY

test_data,276,730,1.644927536231884058,Critical

but i want the same output in tabular format:

Release Cause	Previous Count	Current Count	Change_Ratio	SEVERITY
test_data	276	730	1.644927536231884058	Critical

Please help for the same.

Re: Nifi || Mail || Display csv files content in tabular format.

stevenmatison — Mon, 30 Dec 2019 16:39:24 GMT

If the output is still comma separated, then replace text did not work. The solution above is how you do it. You may need to experiment with other replacements. For example maybe \t instead of [tab]. Again, the method above is what you need.

Here is a similar post that includes some of the other forms of "tab"...

https://community.cloudera.com/t5/Support-Questions/How-best-to-replace-all-TABs-t-by-COMMAs-in-the-content-of-a/m-p/225115

Re: Nifi || Mail || Display csv files content in tabular format.

ashishtiw26 — Mon, 30 Dec 2019 17:19:29 GMT

Thanks for your help but this is not solving my problem as i want the csv record which itself is in comma separated to be displayed in tabular format as in my mail body through PUTMAIL.

I want records to be displayed in table format rather than comma separated or tab one.

I have comma separated record :

Release Cause,Previous Count,Current Count,Change_Ratio,SEVERITY

SIP: [487] Request Terminated,173,393,1.271676300578034682,Critical

and I want like this in mail body:

Release Cause	Previous Count	Current Count	Change_Ratio	SEVERITY
test_data	276	730	1.644927536231884058	Critical

Please suggest a suitable method in order to do the same.

Re: Nifi || Mail || Display csv files content in tabular format.

stevenmatison — Mon, 30 Dec 2019 17:31:04 GMT

I understand you are not able to figure out the exact ReplaceText syntax that you need. I have quickly created a flow and validated the following works as demonstrated. I had to use \t to match your string:

Output:

The regex you need is:

${'$1':replace(',','\t')}

Table formatting is something entirely different and is not part of "csv" parsing....

Re: Nifi || Mail || Display csv files content in tabular format.

stevenmatison — Mon, 30 Dec 2019 17:53:25 GMT

To get it into a table, you need something like this:

<table><tr><td>
${'$1':replace(',','</td><td>'):replace('\n','</td></tr>\n<tr><td>')}
</td></tr></table>

and it should output an HTML Table like this:

<table><tr><td>
Release Cause</td><td>Previous Count</td><td>Current Count</td><td>Change_Ratio</td><td>SEVERITY</td>
</tr><tr><td>SIP: [487] Request Terminated</td><td>173</td><td>393</td><td>1.271676300578034682</td><td>Critical
</td></tr></table>

You may have to play with the last </td></tr> depending on if you have an empty line on bottom or not.

You may also have to adjust \n to \r\n again depending on the source file actual return chars (not seen they are hidden).

Re: Nifi || Mail || Display csv files content in tabular format.

ashishtiw26 — Tue, 31 Dec 2019 07:39:18 GMT

Thanks Stevenmatison.

It worked!!

Re: Nifi || Mail || Display csv files content in tabular format.

ashishtiw26 — Wed, 01 Jan 2020 06:49:47 GMT

Hi Steven,

Your solution worked well.

but i have a question related to this topic.

In Putemail, for the solution you provided i am using content type 'text\html' which is delivering result in table format (in mail body) but i am also attaching csv in my mail.

Now this csv is also displaying data with html tag, is it possible to display the data in original form.(only csv format for attachment without html tag).

Re: Nifi || Mail || Display csv files content in tabular format.

Sumita — Thu, 30 Sep 2021 07:39:01 GMT

Hello Ashish,

Wanted to check whether you were able to solve this problem. As I have to do something similar. Your help would be appreciated 🙂

Re: Nifi || Mail || Display csv files content in tabular format.

ashishtiw26 — Fri, 01 Oct 2021 05:56:54 GMT

Yes Sumita, it worked.