How do I remove line breaks from a field before exporting data?

[#1]

Hi

I am trying to export some delivery logs

Unfortunately one of the related fields I need has some line breaks in it

Consequently, when it gets exported, these line breaks mess up csv/tsvs, whichever newline option I choose when extracting the data.

Is there an easy way to replace these characters?

Cheers

Stephen

[#2 · Accepted solution]

stephenjwz​ I've been able to find a workaround.

So for my tst, I took example of nms:delivery schema. It has a description field with attribute name as desc and it can contain multiline text.

So here's what I did.

1. Inside the Export wizard, come to Data formatting screen. On the right side you have a button to add a computed field. Please click on it.
2. Provide it a Label and internal name.
3. Select Type to be Javascript expression
4. Inside the Javascript expression editor you can select the field from database by accessing the Expression menu, going to Export fields and then selecting the column of choice. Here I'm selecting Description field
5. This adds an expression source.desc in the editor. All now required is to make use of Javascript replace function.
6. If you wish to replace newline characters please use the expression like I used. source.desc.replace(/(\r\n|\n|\r)/gm,""). I'm replacing the newline characters by a blank space. You can add any character of your choice.
7. Last step, We need to uncheck the option to export Description field. We added it to export menu so as to create our formula.
8. Please complete the wizard to export this in a CSV file and it should do the trick for you.

Hope this helps.

[#3]

Additionally when the XML file has been saved, you can open it in MS Excel. Excel will automatically convert it to its format and then you can save it as XLSX and maybe CSV as well.

[#4]

Hi stephenjwz​, please try exporting them as a XML and see if it helps.

In case it is mandatory to export in CSV, please can you show a sample data as stored in the database table column?

[#5]

Thanks Vipul - perfect solution, really appreciate your help.

[#6]

Hi Vipul,

Thanks for the fast reply - using XML definitely does solve the problem, though it seems to make the output a pretty big file, which is kind of a problem in itself. If it's possible to fix the data & keep csv or tsv format, that'd be my preference, but I may resort to xml if not.

Data looks like

value1

value2

value3

it's the description field from delivery log... ideally we wouldn't keeo so much stuff in a big raw text field, but it's unfortunately the way we're currently using it. I figured it might need a sql or javascript thing to perform the replace function on the string.

Thanks