Clarity

My script creates a CSV file based on query results.

The relevant part of the script is

  </sql:query>
          <core:forEach items="${user_query.rowsByIndex}" trim="true" var="row">
            <file:line>
     
     
              <core:forEach indexVar="c" begin="0" end="46" >
                <gel:log category="User-defined process" level="info" message="inside file loop " />
                <file:column value="${row[c]}"/>
              </core:forEach>
     
     
            </file:line>
          </core:forEach>

That works fine most of the time. When the field queried is displayed as a text box and there are line breaks the line break is inserted while there should only be line breaks at the end of each record. That is valid data. How do I prevent the line breaks within the data from breaking the records.

End of records

When I use a script to put the data into an email that does not happen.

<gel:email from="username@mailserver.com"
fromName="Clarity Admin"
to= "martti.kinnunen@xxxx.com"
subject="CSV to email">
          <gel:log category="User-defined process" level="INFO" message="Writing column headers " />
<![CDATA[        
"INV_ID",

....

 <core:forEach items="${user_query.rowsByIndex}" trim="true" var="row">
              <core:forEach indexVar="c" begin="0" end="46" >
<core:choose>
    <core:when test="${c != 46}">
 <![CDATA[  "${row[c]}", ]]>
    </core:when>
    <core:otherwise>
         <![CDATA[  "${row[c]}" ]]>
    </core:otherwise>
</core:choose>              
              

           </core:forEach>
 <![CDATA[  <br>   ]]>

<gel:log category="User-defined process" level="info" message="inside file loop " />
          </core:forEach>

</gel:email>        
       

How do I eliminate line breaks within the records or create the file from the body of the email

Hi

I quickly tested a gel script writing a csv file.. and it does not happen to me:

<!-- Write a csv file -->

<gel:script
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:ftp="jelly:com.niku.union.gel.FTPTagLibrary"
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:core="jelly:core"
xmlns:sql="jelly:sql">

<gel:setDataSource dbId="niku"/>

 

<sql:query var="results">
SELECT first_name, last_name, unique_name
FROM SRM_RESOURCES
WHERE is_active=?
<sql:param value="1"/>
</sql:query>

 

<!-- Write the file locally -->
<file:writeFile fileName="c:/writeTestFile.csv" delimiter="," embedded="false">

 

<!-- Add a comment header to the file -->
<file:comment value="FIRST_NAME, LAST_NAME, UNIQUE_NAME"/>
<core:forEach items="${results.rowsByIndex}" var="row" trim="true">

<file:line>
<file:column value="${row[0]}"/>
<file:column value="${row[1]}"/>
<file:column value="${row[2]}"/>
</file:line>

</core:forEach>

</file:writeFile>

 

</gel:script>

result:

Could you pls provide you code so I can test in my lab?

Thanks Aurora_Gaimon 

Of course it does not happen
When the field queried is displayed as a text box and there are line breaks the line break is inserted while there should only be line breaks at the end of each record.

Those fields you tried are simple text field not text boxes and do not contain line break within the field content.

I do not have a problem with such fields.

Thanks.

If you get the problem then while you are at it you could also try how it goes to email body content and display all characters in Notepad++

What I am after is the opposite of
https://communities.ca.com/message/101123795?commentID=101123795#comment-101123795 

Thanks Aurora_Gaimon 

Looks like you get the same as I do.

Back to the question: Is it possible to remove those unwanted line breaks and to keep the complete record in one line?

For email you could take the section I posted above.

So to see if the newline is on the data or happening in the file:column tag.

<file:column value="${row[c]}"/>

with

<file:column value="${row[c].toString().trim()}"/>

V/r,

Gene

Thanks gcubed 

tried that and will let you know the result when I get the file.

(Don't have access myself)

On a side note, if one wants to keep the line feeds.  I think it is like this:

     <core:forEach indexVar="c" begin="0" end="46" >
          <gel:log category="User-defined process" level="info" message="inside file loop " />
          <file:column value="${row[c]}"/>
     </core:forEach>

     <!-- Replace with -->

     <core:forEach indexVar="c" begin="0" end="46" >
          <gel:log category="User-defined process" level="info" message="inside file loop " />
          <core:set var="fieldString" value="${row[c].toString()}" />
          <core:invoke method="replace" var='updateFieldValue' on="${fieldString}">
               <core:arg value='"' />
               <core:arg value='""' />
          </core:invoke>
          <file:column value='"${updateFieldValue}"'/>
     </core:forEach>

So base on Common Format and MIME Type for Comma-Separated Values (CSV) Files

https://www.ietf.org/rfc/rfc4180.txt 

We should double quote each double quote contained within the field.

Then wrap the field in double quotes which will allow both commas and line feeds in the field value.

V/r,

Gene

I guess that you will have to do it in the SQL query when getting results.

replace(replace(my_column,chr(10),''),chr(13),'')

seems to do that  in SQL Developer when and exporting to CSV.

Wait and see how the file the process wrote looks.

Doing it in the query as above removed the unnecessary line breaks.

Tryin to remove them when the script generates the lines did not do it

Thanks  Aurora_Gaimon