Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Contributor

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.current_flow_1.png

 

 

1 ACCEPTED SOLUTION

avatar
Super Guru

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).

 

View solution in original post

10 REPLIES 10

avatar
Super Guru

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:

 

Screen Shot 2019-12-30 at 9.29.24 AM.png

 

 

avatar
Contributor

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 CausePrevious CountCurrent CountChange_RatioSEVERITY
test_data2767301.644927536231884058Critical

 

Please help for the same.

avatar
Super Guru

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-...

avatar
Contributor

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 CausePrevious CountCurrent CountChange_RatioSEVERITY
test_data2767301.644927536231884058Critical

 

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

avatar
Super Guru

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:

 

Screen Shot 2019-12-30 at 12.27.11 PM.png

 

Output:

Screen Shot 2019-12-30 at 12.26.53 PM.png

 

The regex you need is:  

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

 

 

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

avatar
Super Guru

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).

 

avatar
Contributor

Thanks Stevenmatison.

It worked!!

avatar
Contributor

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).

avatar
New Contributor

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 🙂