Inserting a line break while writing an excel file

[darshthakkar]

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

[alchemiz]

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]

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]

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]

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.