Forum Discussion

darshthakkar's avatar
darshthakkar
Valued Contributor
3 years ago

Inserting a line break while writing an excel file

Hi Team,

I’m planning to write an excel file with statistics listed on it and I’ve been successful to record the counts from my previous snaps however at the end, the entire data comes as a long text which I would want to break it up.

For instance, I’m planning to write:

However, my output is coming as below:

Basically, I want line breaks in excel file I’m writing. Can we even achieve this in mapper snap?
Also, is it possible to define indentation (like Top/middle/bottom align, Left/Centre/Right align) while writing an excel file?

Thanking in advance for all the help 🙂

Best Regards,
Darsh

expression_language
pipeline
transform

7 Replies

  • alchemiz's avatar
    alchemiz
    Contributor III
    3 years ago

    Hi @darshthakkar ,

    Good day, the line breaks I think is doable by means of excel formula

    e.g.

    then in the excel formatter snap checked the “convert the formula to excel formulas”

    when open in excel

    In excel you need to manually set the cell/s to wrap text

    As for the worksheet attributes/settings/style maybe there’s a way in setting this configs from the excel metadata, to see the excel metadata create a copy of the excel file then rename that file to .zip then extract

    Which document/schema/xml to update, that I have no idea 🙂

    e.g.

    Thanks,
    EmEm

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      3 years ago

      Thank you @alchemiz for testing this out and sharing your insights, I really appreciate that.

      I’m actually looking for no manual intervention once the flat files have been generated so I will have to research a bit on this. I did try something with csv files before however didn’t find any luck with it.

      The old post was Can we hyperlink inside a mapper snap? - #19 by darshthakkar

      @alchemiz - If not line breaks, can we display it on a different row altogether? Thinking out loud as we wouldn’t have to worry about the formulas, press enter after opening a flat file or select wrap text option.

  • ForbinCSD's avatar
    ForbinCSD
    Contributor
    3 years ago

    BTW, I saw you tried CHAR(10).

    Did you try char(13)+char(10)?
    Maybe just char(13)?
    Or the inverse, char(10)+char(13)?

    I’d try those – in that order – if you haven’t already.

  • ddellsperger's avatar
    ddellsperger
    Admin
    3 years ago

    In order to accomplish this, you will need to do two things, the first is in your mapper, where you want a new line, you’ll use a \n in the string. You’ll have to do this with an expression-enabled field (you can’t type it out plain text without doing a later replacement of \\n with \n. Below is a screenshot from an example mapper (pipeline is attached at the bottom of this post)

    The Second thing you will have to do is in excel, for any cell(s) that have line breaks, you’ll have to enable wrapping on said cells. This may differ based on your specific excel version, but on the mac version it’s under “Format Cells” → “Alignment” → “Text control:” → “Wrap text” (see screenshot below)

    With all of these steps, you’ll see the excel now displays the new lines appropriately:

    Excel New Line_2023_04_05.slp (5.2 KB)

  • ForbinCSD's avatar
    ForbinCSD
    Contributor
    3 years ago

    Just my two cents worth here: you definitely do not want to try to embed newlines within the interiors of fields in a CSV file.

    Different programs parse CSV differently, but almost all of them treat the newline as an end-of-record terminator. That is, each line is a single record (regardless of using any quotiing or not).

    I realize this next statement is “painting with an overly wide brush”, but in general, CSV is an “okay but not great” data format for simple data interchange between systems, but NOT good for human-readable formatted data. Are you writing the formatted output as something like an XLSX file? You mentioned “flat file” several times, and Excel is not “flat” so I’m not sure here.

    (BTW, by “okay but not great” I mean that are better formats for data interchange too, but often CSV is “all you’ve got” and you make do.)

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      3 years ago

      Hey @ForbinCSD,

      Thank you for your time, comments and sharing your perspective on this. I edited by post and replaced “flat file” with an “excel file”.
      I agree that CSV is not good for human-readable formatted data but I wanted to understand whether line breaks were even achievable via an excel (preferred solution) or worst-case scenario (a CSV i.e. flat file)

      The reason I edited my post is that, I agreed that using flat and excel would have created a confusion and I wanted to double down on excel approach. Frankly, even if line breaks are not achievable, it would be fine; I just have to compromise on the readability part, at the end of the day, I can still use an “email-sender” snap and define my template accordingly on top of generating an excel file (as a backup)

      Having said that, if Line breaks can be introduced, that is something I would learn from this thread as I’m not sure how to do that without any manual intervention after the file has been generated.

      Thank you.

  • ForbinCSD's avatar
    ForbinCSD
    Contributor
    3 years ago

    Hi, @darshthakkar -

    I agree the Excel file format is a different story.

    And there has to be a way to embed linefeeds in a field there, else you couldn’t do it in Excel itself and get the line breaks back when you reopen the file later!

    I suggest if you don’t get an adequate answer in this forum, you check on a more general forum like Stack Exchange, and remove any references to SnapLogic. Just tell them you’re using an older version of JavaScript (not sure which one is embedded here, but I can find out if you really need it). Boil it down to just representing the line breaks in an Excel file (I guess you’re wanting .XLSX?), and how to get it in JavaScript.

    Then apply here!