[Solved] Remove Links/URLs from Spreadsheet

Discuss the spreadsheet application

[Solved] Remove Links/URLs from Spreadsheet

Postby White Phoenix » Sat Jun 09, 2018 4:30 am

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.
Last edited by White Phoenix on Sun Jun 10, 2018 6:13 pm, edited 1 time in total.
Apache OpenOffice 4.1.7 on Windows 7 Professional.
User avatar
White Phoenix
 
Posts: 222
Joined: Tue Jan 01, 2008 7:10 am

Re: Remove Links/URLs from Spreadsheet

Postby Lupp » Sat Jun 09, 2018 11:06 am

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.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3035
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Remove Links/URLs from Spreadsheet

Postby Villeroy » Sat Jun 09, 2018 11:26 am

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. 
Last edited by Villeroy on Sun Jun 10, 2018 4:00 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29486
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove Links/URLs from Spreadsheet

Postby White Phoenix » Sat Jun 09, 2018 12:32 pm

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.
Apache OpenOffice 4.1.7 on Windows 7 Professional.
User avatar
White Phoenix
 
Posts: 222
Joined: Tue Jan 01, 2008 7:10 am

Re: Remove Links/URLs from Spreadsheet

Postby RoryOF » Sat Jun 09, 2018 12:37 pm

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.
Apache OpenOffice 4.1.9 on Xubuntu 20.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 32371
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Remove Links/URLs from Spreadsheet

Postby Villeroy » Sat Jun 09, 2018 12:47 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29486
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove Links/URLs from Spreadsheet

Postby RoryOF » Sat Jun 09, 2018 12:55 pm

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.
Apache OpenOffice 4.1.9 on Xubuntu 20.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 32371
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Remove Links/URLs from Spreadsheet

Postby Lupp » Sat Jun 09, 2018 2:45 pm

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?
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3035
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Remove Links/URLs from Spreadsheet

Postby Villeroy » Sat Jun 09, 2018 6:54 pm

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...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29486
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove Links/URLs from Spreadsheet

Postby White Phoenix » Sun Jun 10, 2018 1:20 am

@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.
Apache OpenOffice 4.1.7 on Windows 7 Professional.
User avatar
White Phoenix
 
Posts: 222
Joined: Tue Jan 01, 2008 7:10 am

Re: Remove Links/URLs from Spreadsheet

Postby White Phoenix » Sun Jun 10, 2018 1:23 am

Trying again.
Attachments
Romaji Conversion before.ODS
(98.01 KiB) Downloaded 413 times
Apache OpenOffice 4.1.7 on Windows 7 Professional.
User avatar
White Phoenix
 
Posts: 222
Joined: Tue Jan 01, 2008 7:10 am

Re: Remove Links/URLs from Spreadsheet

Postby robleyd » Sun Jun 10, 2018 4:42 am

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?
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3847
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Remove Links/URLs from Spreadsheet

Postby Villeroy » Sun Jun 10, 2018 1:19 pm

Ctrl+A
Delete key
[X] Objects
[OK]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29486
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove Links/URLs from Spreadsheet

Postby White Phoenix » Sun Jun 10, 2018 2:40 pm

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.
Apache OpenOffice 4.1.7 on Windows 7 Professional.
User avatar
White Phoenix
 
Posts: 222
Joined: Tue Jan 01, 2008 7:10 am

Re: Remove Links/URLs from Spreadsheet

Postby Villeroy » Sun Jun 10, 2018 3:55 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29486
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove Links/URLs from Spreadsheet

Postby White Phoenix » Sun Jun 10, 2018 6:12 pm

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.
Apache OpenOffice 4.1.7 on Windows 7 Professional.
User avatar
White Phoenix
 
Posts: 222
Joined: Tue Jan 01, 2008 7:10 am

Re: [SOLVED] Remove Links/URLs from Spreadsheet

Postby Villeroy » Sun Jun 10, 2018 6:49 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29486
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [SOLVED] Remove Links/URLs from Spreadsheet

Postby White Phoenix » Sun Jun 10, 2018 7:43 pm

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.
Apache OpenOffice 4.1.7 on Windows 7 Professional.
User avatar
White Phoenix
 
Posts: 222
Joined: Tue Jan 01, 2008 7:10 am

Re: [Solved] Remove Links/URLs from Spreadsheet

Postby Villeroy » Sun Jun 10, 2018 10:03 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29486
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Remove Links/URLs from Spreadsheet

Postby Lupp » Mon Jun 11, 2018 11:16 am

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).
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3035
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Remove Links/URLs from Spreadsheet

Postby White Phoenix » Mon Jun 11, 2018 10:38 pm

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.
Apache OpenOffice 4.1.7 on Windows 7 Professional.
User avatar
White Phoenix
 
Posts: 222
Joined: Tue Jan 01, 2008 7:10 am

Re: [Solved] Remove Links/URLs from Spreadsheet

Postby opensezame » Wed Jan 23, 2019 5:58 pm

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
OPenOffice v4.1.2 on win10
opensezame
 
Posts: 23
Joined: Tue Aug 16, 2016 8:51 pm

Re: [Solved] Remove Links/URLs from Spreadsheet

Postby Lupp » Wed Jan 23, 2019 7:08 pm

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.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3035
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Remove Links/URLs from Spreadsheet

Postby White Phoenix » Sat Jan 26, 2019 2:03 am

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++.
Apache OpenOffice 4.1.7 on Windows 7 Professional.
User avatar
White Phoenix
 
Posts: 222
Joined: Tue Jan 01, 2008 7:10 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 13 guests