Forum Discussion

alex_panganiban's avatar
alex_panganiban
Contributor
3 years ago

LF's in CSV to result in a Multi-Line Cell in Excel

Could use some advice here. I am trying to take a string of data (a group of key/value pairs) that is comma delimited and insert it into a single Excel cell so that it shows as multiple lines within the cell.

For example, I have a data string that looks like this:

dataString == “key1:value1, key2:value2, key3, value3”

When I open my CSV file in Excel, I want the cell that holds this string to look like this:

key1:value1
key2:value2
key3:value3

In my attempt to do achieve this formatted cell, I’m using a mapper that has the following expression.

$dataString.replace(/,/g, /\n/)

I am expecting the commas to be replaced with line feed regex characters, but instead what I see in Excel looks like this:

key1:value1/\n/key2:value2/\n/key3:value3

I feel as though the CSV formatter is not recognizing the escape characters and resolving the \n into a line feed. That’s just a guess because I’ve tried many things, but unsuccessfully. Can anyone help?

3 Replies

  • JensDeveloper's avatar
    JensDeveloper
    Contributor II
    3 years ago

    Hi @alex.panganiban.guild

    You need to update your expression to
    $dataString.replace(/,/g, “\n”)

    This modification will replace the commas with line feed characters, resulting in the following formatted string:
    {
    dataString:
    “key1:value1\n key2:value2\n key3:value3”
    }
    After using the csv formatter and file writer the excel file looks like this:

    Let me know if this helps

    Regards

    Jens

    • alex_panganiban's avatar
      alex_panganiban
      Contributor
      3 years ago

      Thanks, Jens. So I had tried this solution before and again after you posted. Unfortunately, what I get is a truncation after the first line feed. I get the first key/value pair in the cell, but lose the remaining data in my string. I’ll try and play around some more and see if I can send you an actual sample of what I’m doing.

      Thanks.

      • alex_panganiban's avatar
        alex_panganiban
        Contributor
        3 years ago

        Hi. I got this figured out. The problem was in Excel, where the cell’s height wasn’t automatically adjusting to the height of the data, so only the first line was displaying, and the remaining lines were obfuscated. Thanks for the assist!