Ask Your Question
1

How can I make Calc recognise URLs in CSV files? [closed]

asked 2014-06-18 11:32:26 +0100

pschonmann gravatar image

updated 2014-06-18 21:59:02 +0100

bencomp gravatar image

I have CSV that contains http://pastebin.com/EH8vuBe1 that text. First column is hyperlink. Not recognized as hyperlinks. When i click on A2 cell and edit ( add space at end ) clickable blue hyperlink appears. I have lots of rows in CSV, i dont do that manually. Any help, advice or is it bug ?

image description

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-25 22:09:49.598331

2 Answers

Sort by » oldest newest most voted
0

answered 2014-06-18 12:08:25 +0100

Lupp gravatar image

updated 2014-06-18 12:19:31 +0100

1) Why CSV? Calc and other spreadsheet applications are designed for a LOT of things a simple data-exchange format as CSV isn't capable of.

2) If I create a Calc document, save it "as CSV" and continue using it under Calc, it WILL still recognise URL. Saving it again as CSV the linkage will be lost as expected. File reloaded I see the result.

3) After entering an URL the process recognising it is triggered by typing a delimiter (mostly space) or by ENTER. If you 'Paste Special' an URL with option 'Text only'. No link will be created. (Editing: Couldn't reproduce this. Consider the statement in Italic deleted.)

4) For creating clickable links AFTER import there is the function HYPERLINK().

5) Please attach a (minimised) functional example file instead of an image in such a case. Much easier so to avoid misunderstanding.

edit flag offensive delete link more

Comments

1, CSV file IS source file for import https://i.nyx.cz/files/00/00/12/07/12...

2, when opened csv dialog appears. i select "|" as delimiter and import into table. Save as ODS.

3, first column not recognized as hyperlinks, just as text. Expect conversion to clickable blue hyperlinks

pschonmann gravatar imagepschonmann ( 2014-06-18 12:21:35 +0100 )edit

ad 3. ... Why do you expect the conversion? I won't. By the way: How do you edit (rectify) URL once recognised and in consequence shown as a text-field like ONE character?

You may use the HYPERLINK() function like "=HYPERTEXT(A1;A1)" in, say Z1, and you will get a clickable link though not emphasised as you want.

Sorry! How to trigger the URL recognition without entering the cells and performing any editing I don't know. Hope you find a way or get a hint by someone else.

Lupp gravatar imageLupp ( 2014-06-18 15:21:03 +0100 )edit

If you are interested in a work-around:

Define a cell style faking the hyperlink outfit (Backgroung Grey30%, Font colour Blue6) and name it, say 'ColouredHyperlink'. Apply the HYPERLINK() function then together with the STYLE() function like "=HYPERLINK(A1)&T(STYLE("ColouredHyperlink"))" T() does return the (empty) text value of STYLE() result and thus the style gets applied without appending anything.

Subsequently you may hide the original import.

Lupp gravatar imageLupp ( 2014-06-18 15:48:43 +0100 )edit
0

answered 2014-06-20 01:34:07 +0100

pschonmann gravatar image

Autohotkey saves

Loop, 1720
{
Send {F2}
Send {Space}
Send {Enter}
Sleep 10
}
edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-06-18 11:32:26 +0100

Seen: 1,549 times

Last updated: Jun 20 '14