If I now select this file address.txt (with 60sec refreshment) via the Table -> Link to external data menu.
this is also correctly adopted e.g. in A1 to A4, however
After 60 seconds, the formatting "jumps" back to an undesired (standard?) setting. For example, I use a character height of 8pt and format the text flush left, and after 60sec reset, everything is 10px and black font, no matter what I change, it's all gone after 60sec 😕
demo content of address.txt (simple text file) e.g.
Steps to Reproduce:
1. add sample text file to Table > Link to external data menu with a refreshing of e.g. 60sec
2. modify the format of the data in the table e.g. from size 10 to 6
3. after 60sec it will be "pushed" back to old size bevor
wrong format after table refreshing
unformated import of the textfile
User Profile Reset: No
if u need more pls email
It is how it works.
You can try to link to it directly with a formula as if it was a spreadsheet.
To create the formula, open the txt directly, and copy from it and paste in the target file as link.
Or create a request for enhancement.
Private message from reporter:
sorry it´s my first attempt to try to report a bug
i don´t see any option to add this files directly.
can i do this here now?
Please don't use private messages, use this place.
On the top of the comments, there is an option to 'Add an attachment'
(In reply to m.a.riosv from comment #1)
> It is how it works.
It's at least surprising. In most cases you load just numbers but if I think of rich text or html, overwriting the cell format sounds reasonable. But should reloading external data also affect the formatting?
> Or create a request for enhancement.
Two solutions come in mind: a checkbox "[ ] Keep cell format" and dedicated cell style being kept. And I wonder if Data > Link To External Data and the new Data Provider should behave the same. Third option is to apply a cell style as an additional operation via the Data Provider. And last but not least we could keep the formatting on reloading.
(In reply to Heiko Tietze from comment #3)
> (In reply to m.a.riosv from comment #1)
> > It is how it works.
> It's at least surprising. In most cases you load just numbers but if I think
> of rich text or html, overwriting the cell format sounds reasonable. But
> should reloading external data also affect the formatting?
I guess no, but it can be depended on the cases.
> > Or create a request for enhancement.
> Two solutions come in mind: a checkbox "[ ] Keep cell format" and dedicated
> cell style being kept. And I wonder if Data > Link To External Data and the
> new Data Provider should behave the same. Third option is to apply a cell
> style as an additional operation via the Data Provider. And last but not
> least we could keep the formatting on reloading.
The behavior it's the same as with Menu/Sheet/Insert sheet/ or 'Insert sheet at end' or 'Insert sheet from file', that check for 'Keep cell format' would really nice.
I want to confirm this.
Even though I think this is a feature not a bug. I would like to highlight that the informations about how styling is handled, are really badly documented.
E.g. I try to import a "simple" html table. But € signs do not lead to a currency formatting. The HTML style tag seems to be ignored too.
I guess that there are possibilities for tagging or some "hacks" for displaying. But where to find?
I would be very graceful for Tips and Tricks.
(In reply to Ben from comment #5)
Please review the options in Menu/Tools/Options/Load Save/ HTML compatibility.
Hi there is nothing special there to see.
The import options only could:
- force me to US-Numbers (which would be wrong)
- import unknown html-tags as new fields (which is not my problem as I am not missing data)
- ignore font family (which is not really my problem but could be helpful to further minimize my table)
I investigated last night a bit more on it and and I could figure out that the problem occurse on reopening the files.
Fresh files with fresh imports do import currencys and formatting correct. Reloads do not break the properly detected formatting.
Closing, Opening, and allowing the update of external data _does_ break the formatting.
This is not really about other formattings like background colors which are never properly detected in my case, but more about the detection of cell formats (currencies).
I also started a question on ask
( https://ask.libreoffice.org/t/formatting-of-external-data/89136/4 ).
*** Bug 156587 has been marked as a duplicate of this bug. ***
Format applied to cells linked to external data is lost when updating the data in both a recent master build and 18.104.22.168.
I tested OOo 3.3 and formatting is kept when reloading the data (I tried linking an ODS to a named range in another ODS).
Formatting was never copied across, only data. But cell formatting was kept, so I see this as a regression, although I don't want to tag it as such until we know when it started.
Sounds a lot like bug 100188, maybe we can consolidate.
Created attachment 188745 [details]
test kit to show difference between "Paste Special > As Link" vs "Sheet > External Links"
(In reply to Stéphane Guillou (stragu) from comment #9)
> I tested OOo 3.3 and formatting is kept when reloading the data (I tried
> linking an ODS to a named range in another ODS).
> Formatting was never copied across, only data. But cell formatting was kept,
> so I see this as a regression, although I don't want to tag it as such until
> we know when it started.
Sorry for the confusion, this _paste special_ scenario still works in 24.2. There are differences between "Paste special > As link", and "Sheet > External links", which I didn't realise earlier.
I tested with linked data from ODS and from CSV, using both linking options. See attached test kit.
0. Extract test kit
1. Open includes_linked_data.ods
2. Click "allow updating" in the infobar
How the methods differ:
- Sheet > External links: the source format is used, and restored when updating the data. Which equates to no format for text-based files like .txt or .csv.
This is what Carsten, as well as duplicate bug 156587, and I assume bug 100188 too.
(Note that on update, it goes through the Text Import dialog for the CSV.)
- Paste special > As link: no source format is used, only the data is linked. Which means that newly applied format will be persistent on data update.
So I think what we have here is two features that seem equivalent in their function (i.e. "two different ways to do the same thing") but differ greatly regarding formatting. This is not obvious in the UI and needs improving.
Looking into Edit > Links to external files, one can see that the External Link method uses an "element" in the file, whereas the Paste Special doesn't. But that's pretty much the only thing available to differentiate the two different kinds of links.
In my opinion there's two main options:
A. make the two feature behave in exactly the same way _and_ provide an option to use the source formatting (my preferred solution), see comment 3.
B. Clarify in Help and UI how the two methods differ. (e.g. "As link to data" in Paste Special, and a "Includes the source format" warning in External Links.)
Note that none of the two relevant help sections mention formatting currently:
- Paste special (section "Link"): https://help.libreoffice.org/24.2/en-US/text/shared/01/02070000.html?System=UNIX&DbPAR=CALC&HID=modules/scalc/ui/pastespecial/grid1#bm_id31541011
- External links: https://help.libreoffice.org/24.2/en-US/text/scalc/01/04090000.html
Version: 22.214.171.124.alpha0+ (X86_64) / LibreOffice Community
Build ID: eef0c5d4d45ba35acfb6d8f7551fe565ca4badaa
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
UX/Design team, please see potential improvement options suggested in comment 10.
(In reply to Stéphane Guillou (stragu) from comment #10)
> B. Clarify in Help and UI how the two methods differ. (e.g. "As link to
> data" in Paste Special, and a "Includes the source format" warning in
> External Links.)
My take. Wouldn't cripple one function, or enhance the other with potentially much effort.
Is this bug report about clearing formats when updating from text-only sources, or also from richer, formatted sources? It's all a bit difficult to follow.
We discussed the topic in the design meeting.
The topic is very difficult to understand and probably needs more investigation. More general ideas: external sources are values and should not affect the internal formatting. On the other hand, if the source has formatting it should be used, ideally optionally. The question remains what "no formatting" means both internally and externally, in other words we don't know if a plain cell has explicitly no format and must or must not be overwritten.
Ultimately it sounds correct to follow Stephane's suggestion in comment 10 and harmonize the functions.
Reading all info and ideas, I clearly support Carstens expectation:
- I suggest that refreshing linked data, does not overwrite the applied formatting.
- And clarifying the Help would be good to.
AFAIK, there are various quite different ways to get 'external' data.
1. Sheet > External Links
uses no formatting (plain data)
but currently, refreshing overwrites applied cell formatting
2. Edit > Paste Special
with data from within the application;
either with or without formatting from the source
3. Navigator > Select different file at bottom > Drag named range (copy or link..)
does use the source formatting
NB: Edit > Paste Special, with external data, opens the text import dialog (known from CSV) and is not a link (and also without formatting).
Some Help (partly additional to what Stéphane mentioned):
(In reply to Heiko Tietze from comment #15)
> Ultimately it sounds correct to follow Stephane's suggestion in comment 10
> and harmonize the functions.
Having looked again, I think these are too different, to expect them to act the same wrt possible formatting in the source of the linked data.