Hyperlink to Page Title conversion

by 11 replies
13
I am hoping someone has a solution to this little problem.

What I need to do:

Convert hyperlinks and page titles list to clickable page titles only then export to HTML file

They are currently in a OpenOffice .csv calc file as:

Column A with hyperlink
Column B with page title

There are hundreds of rows.

The hyperlinks are formatted as --- http:// whatever.com , some with trailing /

Have done some searches for this solution but none seem to work so far.

Or perhaps copy the rows and columns to a text file and try to accomplish the same thing in a different way?

Any example suggestions would be greatly appreciated!

Thank you,
Charles
#programming #conversion #hyperlink #page #title
  • ok, basically the idea is to make use of the HYPERLINK formula to achieve this:

    =HYPERLINK( "http://www.google.com" , "Click here" )

    Paste the above line to excel to see how it works:


    step0: Check out this screenshot http://postimage.org/image/z24u2l5nt/full/ to get an idea.
    step1: move all the items in Column B (page title)) to Column D.
    step2: move all the items in Column A (hyperlink) Column B.
    step3: Paste the following to Column A:
    DUMMY=HYPERLINK( "
    step3: Paste the following to Column C:
    " , "
    step4: Paste the following to Column E:
    ")
    step5: Copy all the data to notepad/wordpad/text editor
    step6: Fire up the "replace" option, and replace all the words "DUMMY" with nothing. We don't want that word to show up in excel
    step7: copy the data in text file, and paste it back to excel and Voila!
    • [ 1 ] Thanks
    • [1] reply
    • Thanks sautaja!

      I will give this a try and let you know.

      Appreciate you taking the time to provide a solution.

      Charles
      • [1] reply
  • Can you try to paste the following to an empty cell in excel to see if that works?
    =HYPERLINK( "http://www.google.com" , "Click here" )
    The cell will be populated with a clickable link.


    I don't have a copy of Microsoft Excel, but I use LibreOffice Calc at the moment. A "text import" screen will pop up when importing the data from text file. I am really not sure if it's only LibreOffice/OpenOffice that can do this, you might want to try them out first:
    Home ยป LibreOffice
    OpenOffice.org - The Free and Open Productivity Suite


    http://s16.postimage.org/5aazf3e1f/screen1.png
    • [1] reply
    • Am already using OpenOffice and got an error message. So uninstalled OO and installed LibreOffice, even though they should be fundamentally the same. And your example above worked... got a clickable link from within the spreadsheet. Nice!

      When I exported the file as HTML however, the link was not clickable.

      So using your original instructions to see if that will work and it does not provide a clickable link like the above method. Used Text Import with exactly the same settings. The screenshot shows what results.
  • I think your libre office has "smart quote" option enabled, so all the double quotes will be automatically converted to curly double quotes by default, and that will cause some problems when pasting data from a text file back to the spreadsheet.


    There are two solutions for this:
    a) On libre office's top menu, go to Tools -> AutoCorrect -> Localized Options, uncheck the box beside "Replace" under the Double quotes section

    b) OR replace all the curly double quotes in the text file with dumb double quotes.

    The text import should become successful then.

    As for the html file export, I tried that and it didn't work using libre office. After you import the data from a text file, you need to save the spreadsheet as, say, an openoffice spreadsheet file: test.ods. After that use https://docs.google.com to import the ods file from your computer and select "download as html", google document should give you a clickable html file.
    • [1] reply
    • I think you are absolutely correct about the smart vs dumb quotes.

      And I can do the Google docs part for HTML output.

      Okay, let me give all this a try. I am getting behind in other project stuff, so it may be the latter part of next week before I have time to work on this again. Will let you know how it turns out then.

      Thanks very much again for going to all this effort to help me out!

      Charles

      ---------------------

      It looks like this is working fine now. Along with Michael's method, now I have two ways to make this work for future needs. Many thanks again sautaja for helping me get this done!!
  • I was bored so I created something for you:

    http://www.mgourlay.com/csvToHtml/

    Upload a CSV in the following format:
    Column A: URL
    Column B: Title

    No header rows.

    It'll spit you out a page with the hyperlinks. Just use your browser to save it.

    It won't work if any of your data has commas in it.

    I can't keep this up forever, let me know if it works and if you want the php code.
    • [ 1 ] Thanks
    • [1] reply
    • Hi Michael,

      I'm glad you were bored.

      Thanks very much for providing that link to try. I will have to remove the the commas from the csv file, but it looks like this would do the trick nicely.

      So yes, if you would allow me to use the php code, I can install it on my web host and give it a whirl.

      Can you attach it to an email via my Profile page?

      I Really Do appreciate you taking the time and effort to help me solve this issue!

      Best,
      Charles
      • [1] reply
Join the discussion

Next Topics on Trending Feed