Output a CSV record containing a multi-line field to a single line using awk

Question

I am trying to process my CSV file such that each record is output as a single line, with any line breaks in the data replaced by a temporary placeholder <br>.

This is a sample record in my CSV file.

"Chvro"|"SCRIPT.CHANGE.PASSWORD.1"|0|"script.change.password.1"|"ShellScriptTemplate"|"CLOBPART"|||"import com.company.dto.exception.UCMException;
import com.jscape.inet.ssh.SshException;

try {
    logger.info(""Send passwd command"");
    session.sendWait(""passwd ""+userId, "".*exist.*"", true, timeout);
} catch (SshException e) {
    logger.error(""Send passwd command failed"", e);
    throw new UCMException(""Send passwd command failed"", e);
}

try {
    logger.info(""Send old password"");
    session.sendWait(oldPassword, "".*[N|n]ew.*"", true, timeout);
} catch (SshException e) {
    logger.error(""Send old password failed"", e);
    throw new UCMException(""Send old password failed"", e);
}

try {
    logger.info(""Send new password"");
    session.sendWait(newPassword, "".*[R|r]e-enter.*"", true, timeout);
} catch (SshException e) {
    logger.error(""Send new password failed"", e);
    throw new UCMException(""Send new password failed"", e);
}

try {
    logger.info(""Send confirm new password"");
    session.sendWait(newPassword, "".*success.*"", true, timeout);
} catch (SshException e) {
    logger.error(""Confirm new password failed"", e);
    throw new UCMException(""Confirm new password failed"", e);
}

logger.info(""Change password succeed"");
"||"N"|

In the above sample, each record consists of twelve fields, and the multi-line script (if any) is in the ninth field. The desired output should be

"Chvro"|"SCRIPT.CHANGE.PASSWORD.1"|0|"script.change.password.1"|"ShellScriptTemplate"|"CLOBPART"|||"import com.company.dto.exception.UCMException;<br>import com.jscape.inet.ssh.SshException;<br><br>try {<br>    logger.info(""Send passwd command"");<br>    session.sendWait(""passwd ""+userId, "".*exist.*"", true, timeout);<br>} catch (SshException e) {<br>    logger.error(""Send passwd command failed"", e);<br>    throw new UCMException(""Send passwd command failed"", e);<br>}<br><br>try {<br>    logger.info(""Send old password"");<br>    session.sendWait(oldPassword, "".*[N|n]ew.*"", true, timeout);<br>} catch (SshException e) {<br>    logger.error(""Send old password failed"", e);<br>    throw new UCMException(""Send old password failed"", e);<br>}<br><br>try {<br>    logger.info(""Send new password"");<br>    session.sendWait(newPassword, "".*[R|r]e-enter.*"", true, timeout);<br>} catch (SshException e) {<br>    logger.error(""Send new password failed"", e);<br>    throw new UCMException(""Send new password failed"", e);<br>}<br><br>try {<br>    logger.info(""Send confirm new password"");<br>    session.sendWait(newPassword, "".*success.*"", true, timeout);<br>} catch (SshException e) {<br>    logger.error(""Confirm new password failed"", e);<br>    throw new UCMException(""Confirm new password failed"", e);<br>}<br><br>logger.info(""Change password succeed"");<br>"||"N"|

But the closest I have gotten is the command

awk -v FS='|' -v OFS='|' -v ORS='\n' -v fields=12 '{f+=NF; str=(str?str "<br>": "") $0} f>=fields{gsub(/\n/, "<br>", str); print str; str=""; f=0}' myinput.csv

which produces the following output:

"Chvro"|"SCRIPT.RESET.PASSWORD.1"|0|"script.reset.password.1"|"ShellScriptTemplate"|"CLOBPART"|||"import com.company.dto.exception.UCMException;<br>import com.jscape.inet.ssh.SshException;<br><br>try {<br>    logger.info(""Send passwd command"");
    session.sendWait(""passwd ""+userId, "".*[N|n]ew.*"", true, timeout);<br>} catch (SshException e) {<br>    logger.error(""Send passwd command failed"", e);<br>    throw new UCMException(""Send passwd command failed"", e);<br>}<br><br>try {<br>    logger.info(""Send new password"");<br>    session.sendWait(newPassword, "".*[R|r]e-enter.*"", true, timeout);<br>} catch (SshException e) {<br>    logger.error(""Send new password failed"", e);
    throw new UCMException(""Send new password failed"", e);<br>}<br><br>try {<br>    logger.info(""Send confirm new password"");<br>    session.sendWait(newPassword, "".*success.*"", true, timeout);<br>} catch (SshException e) {<br>    logger.error(""Confirm new password failed"", e);<br>    throw new UCMException(""Confirm new password failed"", e);<br>}<br><br>logger.info(""Reset password succeed"");<br>"||"N"|

If possible, I want to avoid hardcoding $9 in the final awk command and try to extend the replacement to the entire record, so that if this occurs in any other field then the same substitution takes place. How can I modify my existing awk command to achieve this?

1 sample input/output record isn't adequate to demonstrate your needs or test a potential solution since the newline between records has to be handled differently from the newline within a record. Anyway, see [whats-the-most-robust-way-to-efficiently-parse-csv-using-awk](https://stackoverflow.com/questions/45420535/whats-the-most-robust-way-to-efficiently-parse-csv-using-awk) and just change the separator from `,` to `[|]` and `gsub(/\n/," "...)` to `gsub(/\n/,"
",...)` in whichever script that handles embedded newlines you end up using from the answer there (the gawk or non-gawk one). — Ed Morton, Aug 01 '23 at 11:24
You might want to add some code to change any coincidental `
`s in your input (e.g. if `logger.error(""Coping with
failed"", e);` existed) to something else before changing newlines to `
`s otherwise you'll never be able to change `
`s back to newlines again if you have to. — Ed Morton, Aug 01 '23 at 11:31

Output a CSV record containing a multi-line field to a single line using awk

0 Answers0