[Solved] Remove Links/URLs from Spreadsheet

[White Phoenix]

I don’t know how it happened, but I just found out that a few of my spreadsheets had hidden links in them. I didn’t put them in deliberately and I don’t want them in now. The best I have been able to do so far is to “break” them. I want to remove them completely. Is there any way to do that?

Also I need to know how they would have gotten there so I can avoid putting links in my files in the future.

[Lupp]

If a link is used in Calc to import data, the data are always also kept (embedded) in the Calc-document. The link is only present to enable probably needed/wanted updates. You should be asked if you want to update. "Breaking" the link will omit the availability of such updates. As far as I know it will remove the link completely to do so.
If you want to definitely be sure, you will need to analyse the file with a combination of an unzipper and a XML-editor. You may supply an example in two versions "before" and "after" breaking the link. I will then try to do the analysis for you.

[Villeroy]

Calc knows 6 different types of links to some other files' content. 4 of them appear in the Edit>Links dialog.

1) ='file:///path/name.ods'#Sheet.A1:B99 (spreadsheets)
2) =DDE("soffice";"C:\path\name.ods";"Sheet.A1:B99" (Calc, Writer and sometimes other DDE sources)
3) menu:Insert>Links to external data (named areas in sheets and html tables)
4) menu:Insert>Sheet_From_File with link option (whole sheets of all file types supported by Calc including csv)
5) Databases connection (drag&drop from data source window)
6) Pivot tables connected to a data source

Edit: 7) Linked images attached to the draw page layer of an ODF document

For 1) and 2) you need to edit all formulas where the link is in use. Otherwise they reappear in the links dialog with every recalculation.
3) and 4) can be removed in the links dialog.
5) can be removed in Data>Define...
6) goes away when removing the pivot table

Edit: 7) You can break the link in the Edit>Links dialog of Writer, Calc, Draw and Impress. The list box with the links is a multi-selection box. Ctrl+A selects all links.

[White Phoenix]

The links still show in the navigator under Graphics, so the links are not removed. When I imported the data the standard procedure that I use is to paste into a plain text editor, then from there into Calc. I then remove the column where images were and only show as blank cells. This is why I never thought there would be any links imported. Data>Define I didn’t know about, nor the pivot table. As far as I know, I don’t have a pivot table. I have never used one that I know of.

I just opened one of the files with links listed in the navigator. No data ranges were ever defined, and no pivot tables were ever created. Funny that no one thought to put a “delete links” in the navigator.

If you want to definitely be sure, you will need to analyse the file with a combination of an unzipper and a XML-editor.

Okay, so now I need an XML editor. I still have the backed up file where I have the current iteration of the files I found the unwanted links in. So I can do a before and after, and post them.

[RoryOF]

I use XML Copy Editor, but to be honest I have not yet found an ideal XML editor. I do very little XML editing - only repair of damaged files for Forum users.

[Villeroy]

Firefox gives a pretty print of XML, possibly any other browser as well.
Having a rough idea about the structure and the snippet you need to find, Writer will do or any other editor which can handle the file size.

[RoryOF]

The problem with Firefox (also with XML Copy Editor), when attempting to repair a damaged XML file, is that it will refuse to Pretty Print the damaged file. If it would Pretty Print up to the damage, that would be very helpful.

[Lupp]

The links I talked of (and also Villeroy, though to much more detail) were data-links. Graphics imported as links are very different. What may happen to links (probably already broken ones) in any editor, I lack the experience. Sorry. To go further I would need the file.

Concerning the XML files wrapped into an .ods I always use (for the same purpose mostly as Rory does) my very old WinZip and Notepad++ Portable with a few add-ins.

One thing more: What happens if you doubleclick on one of the links shown in the navigator?

[Villeroy]

You striktly refuse to provide a file or any information about the concrete URLs or the context where they appear. Therefore I listed all types of links in spreadsheet context since you mentioned

The best I have been able to do so far is to “break” them.

but of course you refuse to tell us how you did that. Therefore I assumed you used the [Break LInk] button in dialog Edit>Links...

[White Phoenix]

@RoryOF Yeah, I really don’t have any need for XML editing either. The last time I did what Villeroy suggested, I opened it in Writer. Like you said, the code was a jumbled mess to me. I was guessing where code would start and end. There was absolutely no pattern I could see to go by.

When I double-click on the link I get an image box with an image you can see when you click in the center. This is in both the file with the broken links and the file that still has the links intact. The only difference between the two is the one with the links intact doesn’t have Edit>Links grayed out, asks if I want to update the links, and takes a very long time to load. That’s weird, the file with the links broken is larger than the one with the links intact. I am getting an error message that the after file is too big to upload.

The before file is 98 KB, but the after file is 1.3 MB? What? The file it was complaining about was the before file?

I have been trying to figure out why the files are different sizes, and the files with the links intact take a very long time to load. Then I find I can’t even attach one that is 98 KB on my computer because the forums say it is 136 KB.

[White Phoenix]

Trying again.

[robleyd]

Your provided sample contains hundreds of links to images on a web site - for example https://cdn.bulbagarden.net/upload/b/bb/004MS.png

From text in the spreadsheet, it seems you might be trying to create a document to convert to a web page. Can you tell us what ti is you are actually trying to achieve?

[Villeroy]

Ctrl+A
Delete key
[X] Objects
[OK]

[White Phoenix]

Since I prefer flat-file databases, I have been using spreadsheets for my databases. I use the databases to create test arrays for my programs. Last year I decided that since our website has unlimited databases, I would convert all my spreadsheets to MySQL databases. I copied and edited a few tables from another site, but apparently I didn’t follow my routine of copying to a text file, removing the unwanted columns, then pasting into the spreadsheet. If I want to add images to our online databases, I will use our own graphics collection.

OBJECTS. Yes, I remember now. Graphics are objects. Thanks, Villeroy.

[Villeroy]

A spreadsheet can not be a database. Storing text data in a calculator makes not sense, particularly when the table is supposed to be constantly growing.
Anyway, the list box in the Edit>Links dialog is a multi-selection dialog. You can click the box, hit Ctrl+A and break all links in one go preserving the pics.

[White Phoenix]

Didn’t want to preserve the images. I didn’t want them at all. A spreadsheet can even be a program. A database doesn’t always need to grow. Besides, OpenOffice doesn’t provide for a flat-file database like Works. Microsoft abandoned Works, and the last version of Works does not support Unicode. If AOO were to provide a flat-file database, I would use that. As it is, I have been doing this for twenty years and using Calc the way I do works just fine as long as I stick to importing the data properly.

Thank you all for your help.

[Villeroy]

What is a "flat file" database? Base can connect to anything tabular within a flat file and there is at least one type of Base file where a whole relational database is wrapped into one file.

[White Phoenix]

Yes, you have answered your own question. The interface of Works Database was more simple and easier to use. I could basically import a properly formatted spreadsheet directly into it, because the interface was a grid like a spreadsheet. I have been looking into it and it looks as if Excel-type spreadsheets can be imported into MySQL as well. In any event, because of the forums needing an administrator that can create extensions in PHP and I need to import some of my Pokémon-related projects into databases for the site, I have to learn PHPMyAdmin any way. So I may as well take advantage of the situation. Once I learn C++ I can port all of the spreadsheet “programs” to that. A flat file database will also make the offline pokédex easier to port. Although that could be left in HTML format. The hardest part for me has been finding the esoteric information I need to make my programs work.

[Villeroy]

MySQL requires that you run a MySQL server. It is possible to import any spreadsheet into any type of writable database if the spreadsheet data fulfill the rules imposed by the database. You don't have to learn any programming language.
The HSQL database engine which is shipped with the office suite is a full featured relational database similar to MySQL.
Valid spreadsheet data can be copied into database tables. For simple, unrelated lists you can also save the spreadsheets as *.dbf in a dedicated directory and connect a Base document to that dBase directory.
You can not simply copy any clipboard content into a database. However, you can paste into a spreadsheet and then into your database.

[Lupp]

Just for completeness concerning the original question here:

See also https://ask.libreoffice.org/en/question ... her-files/.
There the questioner found an answer himself (in his specific case) I hadn't thought of in decades (without analysing the CONTENT.xml again).

[White Phoenix]

Never wrote that I had to learn any programming language for MySQL. Just that I needed to learn MySQL, and PHPMyAdmin. I have used MySQL databases, but with no experience and not really having any knowledge of any of its features. I have access to the server on our website which has MySQL via PHPMyAdmin. I do have to learn PHP which looks similar to HTML, but I haven’t really done anything extensive other than to patch files for specific problems. Certainly not enough to replace the missing modifications that we use to have on our forums, or fix the broken ones.

[opensezame]

Persistent problem with unidentifiable links identified when opening a SS
none of the above searches identify the links

makes OO insecure to have things like this hidden this well
editing HTML should not be an undocumented everyday /maintenance usage feature

vileroy , you are as frustrated by our comments as we are in OO

ONLY WAY I FOUND TO CLEAR THIS WAS TO TAKE THE OFFENDING sheets in the spreadsheet AND MOVE THEM ONE BY ONE TO A NEW SPREADSHEET
which with 25 tabs was a monster PITA
but it worked

SOLVED
pass it on

[Lupp]

Did you ever act on my (indirect) suggestion to check whether the original file contained dead links due to tracked changes? This was reported as a probable cause of the problem in the thread I linked to two posts above yours.

[White Phoenix]

Lupp, it doesn’t apply. I don’t use track changes, so they are always off.

Also to clarify a point I missed, I need to change the spreadsheets that I use as programs into C++ programs. Which is why I need to learn C++.