Hyperlink to Page Title conversion

by realworldincome

Posted: 12 years ago 11 replies

PROGRAMMING

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

#conversion #hyperlink #page #title

sautaja 12 years ago

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
Signature
Jomify - Free multi-channel shopping cart. Open your free store now.
{{ DiscussionBoard.errors[5705980].message }}
- realworldincome 12 years ago
  
  Thanks sautaja!
  
  I will give this a try and let you know.
  
  Appreciate you taking the time to provide a solution.
  
  Charles
  
  Thanks
  
  1 reply
  
  {{ DiscussionBoard.errors[5710143].message }}
  
  realworldincome 12 years ago
  
  Sadly, this did not work.
  
  I tried it several times using the instructions and screenshot exactly, and with some variations.
  
  The only change that results is a blank column A. I also tried it without column headers for hyperlink and page title.
  
  Where did I go wrong? :confused:
  
  Charles
  
  Thanks
  
  {{ DiscussionBoard.errors[5712584].message }}

sautaja

12 years ago

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

Thanks
1 reply

Signature

Jomify - Free multi-channel shopping cart. Open your free store now.

{{ DiscussionBoard.errors[5719373].message }}

realworldincome

12 years ago

Originally Posted by sautaja

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.

Thanks

{{ DiscussionBoard.errors[5723740].message }}

sautaja 12 years ago

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.
- Thanks
- 1 reply
Signature
Jomify - Free multi-channel shopping cart. Open your free store now.
{{ DiscussionBoard.errors[5724891].message }}
- realworldincome 12 years ago
  
  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!!
  
  Thanks
  
  {{ DiscussionBoard.errors[5725293].message }}

michael_gourlay

12 years ago

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

{{ DiscussionBoard.errors[5728934].message }}

realworldincome

12 years ago

Originally Posted by michael_gourlay

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

Thanks
1 reply

{{ DiscussionBoard.errors[5731207].message }}

michael_gourlay

12 years ago

Just a clarification, don't remove the commas between the columns, just if any of your titles or urls have commas, it probably wont work. I'll send you the php code shortly.

Thanks
1 reply

{{ DiscussionBoard.errors[5734451].message }}

realworldincome

12 years ago

Originally Posted by michael_gourlay

Just a clarification, don't remove the commas between the columns, just if any of your titles or urls have commas, it probably wont work. I'll send you the php code shortly.

Thanks for clarifying, Michael.

Problem Resolved! Many thanks!

Thanks

{{ DiscussionBoard.errors[5734547].message }}

Hyperlink to Page Title conversion

Trending Topics

Any thoughts on golf's latest phenom...Scottie Scheffler??

best high quality crypto traffic.

Should i Index Author pages?

What's the Best IM-Related Skill to Learn Today That Can Help in the Future?

Some interesting stats about brand comms