I have many hyperlinks in an LibreOffice Calc table sheet, created with “Insert → Hyperlink”.
How can you export these hyperlinks to the CSV file, at least the URLs of them? Perhaps also in Markdown style? Best would be if they would be recognized on re-importing.
If I export them via “File → Save As”, choosing “Text CSV (.csv)”, the shown text of the hyperlink is exported to the CSV file.
Csv files are plain text files regarding a minimum of syntax to make them suitable for the representation of constant tables. They cannot contain something like a working formula or a linked text. Thus an URL also can be contained only as a piece of plain text.
When opening or importing a csv with Calc you have to select/deselect the syntactical special characters and to set the appropriate options corresponding to the ones used when the csv was written to the file.
If you want to export an URL from a cell you first have to make sure that it is actually present in the cell as text as opposed to as linked URL “in the background”.
If the linking was done automatically for some complete (including the “http://” e.g.) URL(s) entered explicitly into the cell, it (they) is (are) also present as text.
If the link(s) was (were) edited using the ‘Hyperlink’ tool (Ctrl+K) and the cell is showing a different text, you first need to extract the URL from the cell.
I do not know a standard function or a standard tool to do so.
The below given BASIC function is a slightly reworked version of code I found in Andrew Pitonyaks famous “Useful Macro Information…”. It does not try to catch any errors.
Function getURLsFromLinkedCell(pX, pY, pZ)
Dim theCell, theText, theParEnum, theParElement
Dim theEnum, theElement, rString As String, urlDelim As String
urlDelim = "|"
getURLsFromLinkedCell = "No URL found!"
theCell = ThisComponent.Sheets(pZ-1).GetCellByPosition(pX-1, pY-1)
theParEnum = theCell.GetText().CreateEnumeration()
rSting = ""
Do While theParEnum.HasMoreElements()
theParElement = theParEnum.NextElement()
theEnum = theParElement.CreateEnumeration()
Do While theEnum.HasMoreElements()
theElement = theEnum.nextElement()
If theElement.TextPortionType = "TextField" Then
If theElement.TextField.supportsService("com.sun.star.text.TextField.URL") Then
If rString<>"" Then rString = rString + urlDelim
rString = rString + theElement.TextField.URL
EndIf
EndIf
Loop
Loop
If rString<>"" Then getURLsFromLinkedCell = rString
End Function
It will return the one linked URL or, separated by a pipe character, the many linked URLs if present and “No URL found!” otherwise.
The function must be applied to each cell (possibly) containing a linked URL in an available free cell, best an adjacent one. Applied on the same sheet to cell C5, e.g. the formula should read =GETURLSFROMLINKEDCELL(COLUMN(C5);ROW(C5);SHEET(C5)).
Cells linked to an URL using the HYPERLINK standard function will not give access to the URL to the above given user function because it is not implemented as a property of a text field. You would have to extract the link from the formula itself (if contained there at all).
Hi @Lupp
Not in response to the question but may I add a csv export can contain working formulas: File▸Save As▸Type: Text CSV▸tick Edit filter settings▸Field Options▸tick Save cell formulas instead of calculated values
Attention to the choice of separators may be in conflict with the local settings.
Regards
@PYS: I knew about saving fiormulas. My poor English led me to an unclear way of saying: As long as not brought to life again by importing it into a spreadsheet, a formula written to a csv is as “dead” (not actually working) as any piece of text. Regards.