How do I remove line breaks from a field before exporting data? | Community
Skip to main content
S
stephenjwz
Level 2
October 14, 2016
Solved

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

  • October 14, 2016
  • 5 replies
  • 7823 views

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by vraghav

    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.

    5 replies

    vraghav
    Adobe Employee
    vraghavAdobe Employee
    Adobe Employee
    October 14, 2016

    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?

      S
      stephenjwzAuthor
      Level 2
      October 14, 2016

      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

      vraghav
      Adobe Employee
      vraghavAdobe Employee
      Adobe Employee
      October 14, 2016

      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.

        vraghav
        Adobe Employee
        vraghavAdobe EmployeeAccepted solution
        Adobe Employee
        October 14, 2016

        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.

          S
          stephenjwzAuthor
          Level 2
          October 17, 2016

          Thanks Vipul - perfect solution, really appreciate your help.

          Terms & ConditionsAccessibility statement

          Loading footer...