Ask Your Question
0

Export hyperlink's URL in .csv-file

asked 2016-06-09 10:56:23 +0100

Dominik gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-06-09 13:24:58 +0100

Lupp gravatar image

updated 2016-06-09 13:31:12 +0100

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).

edit flag offensive delete link more

Comments

Hi @Lupp

Not in response to the question but may I add a csv export can contain working formulas: FileSave AsType:Text CSV▸tick Edit filter settingsField Options▸tick Save cell formulas instead of calculated values

Attention to the choice of separators may be in conflict with the local settings.

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-06-09 16:26:43 +0100 )edit

@pierre-yves samyn: 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.

Lupp gravatar imageLupp ( 2016-06-09 20:46:47 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-06-09 10:56:23 +0100

Seen: 2,068 times

Last updated: Jun 09 '16