Creating click-able hyper-links in CSV files programmatically


If you are programmatically creating CSV files intended for Microsoft Excel users, and cannot figure out how to make URL data in the CSV files behave like click-able hyper-links for your users, this tip is for you.

There is a special format in which URLs should be formatted, that is used by a number of spreadsheet applications to interpret URLs as click-able hyper-links. The format is this:

=HYPERLINK("URL")

The URL must be enclosed in quotes for the format to be interpreted properly (without which, at least, Microsoft Excel complains).

6 thoughts on “Creating click-able hyper-links in CSV files programmatically

  1. Pingback: Creating click-able hyper-links in CSV files programmatically | Tea Break

  2. I’m trying to export our contact list, on excel, the problem is that when I do that the client name that has link to their address is only saved as it is, I wish to export as well the contents on the link (name)….how can I do that…pls help.

  3. What worked for me in Excel 2003:
    Output to your CSV the statement:
    CELLVALUE=”=HYPERLINK(“+QM+URLCONTENTS+QM+”;”+QM+”URLDISPLAYNAME”+QM+”)”

    where:
    CELLVALUE is the output written to the CSV
    QM is the ASCII value of [“] -> (ASCII 34)
    URLCONTENTS is the full URL to the page you want to link to.
    URLDISPLAYNAME is the text you see in the Excel cell.

    You can also use relative paths and set a base location in Excel.
    File/Properties, Tab Summary, Field Hyperlink Base. Use as fieldvalue something like “http://www.SITENAME.com/SUB_LOCATION/../SUB_LOCATION” that sets your starting point so you can click it in Excel.
    Of course, you don’t have to use SUB_LOCATIONs if the sitename itself already will resolve succesfully for your relative path.

    What I couldn’t find is how to make the links automatically underlined in Excel. From other tips found on the internet:
    Format manually all linkcells as underlined and darkblue (for example) and then the standard functionality appears with already visited links turning into another color.

    Bob

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s