Update 3/4/15: The other day Nat Dunn, Founder and CIO of Webucator, reached out and asked if his company could turn this blog post into a free training video. Webucator did a bang-up job, so if video tutorials are your thing, check it out!
I was building a QA spreadsheet the other day, where I needed to generate a number of randomly-generated WordPress posts with the posts’ titles, URLs, admin URLs, and publication date to be shared with the client via Google Drive. This is a straight-forward enough task in SQL, with something like this:
|
1 2 3 4 5 6 7 8 |
SELECT p.post_title, CONCAT('http://example.com?p=', p.ID) AS public_url, CONCAT('http://example.com/wp-admin/post.php?post=', p.ID, '&action=edit') AS admin_url, DATE_FORMAT(p.post_date, '%Y-%m-%d') as post_date FROM wp_posts p WHERE p.post_status = 'publish' AND p.post_type = 'post' ORDER BY rand() LIMIT 50 |
This gives us a pretty simple output:
|
1 2 3 4 5 |
---------------------------------------------------------------------------------------------------------------- | post_title | public_url | admin_url | post_date | | -------------------------------------------------------------------------------------------------------------- | Hello World! | http://example.com?p=1 | http://example.com/wp-admin/post.php?post=1&action=edit | 2015-02-18 | ----------------------------------------------------------------------------------------------------------------- |
However, my PM wanted to be able to give the client a spreadsheet that looks more like this (where “Hello World!” and “Admin” are links to the public and admin URLs, respectively):
|
1 2 3 4 5 |
----------------------------------------- | post_title | admin_url | post_date | | --------------------------------------- | Hello World! | Admin | 2015-02-19 | ----------------------------------------- |
Unfortunately, CONCAT-ing together an HTML link did nothing, but then I found this post on CompuTips Blog outlining how to paste hyperlinked cells from CSV files. The expected format is:
|
1 |
=HYPERLINK("http://example.com","Anchor Text") |
To revisit our original query, we would rewrite it as:
|
1 2 3 4 5 6 7 |
SELECT CONCAT('=HYPERLINK("http://example.com?p=', p.ID, '","', p.post_title, '")') AS post_title, CONCAT('=HYPERLINK("http://example.com/wp-admin/post.php?post=', p.ID, '&action=edit","Admin"') AS admin_url, DATE_FORMAT(p.post_date, '%Y-%m-%d') as post_date FROM wp_posts p WHERE p.post_status = 'publish' AND p.post_type = 'post' ORDER BY rand() LIMIT 50 |
The results from that query should paste nicely into Google Drive, Excel, Libre Office, and other popular spreadsheet applications. As a quick aside, if your post titles contain double-quotes you might get the infamous #ERROR in the post_title column once pasted; handling this is just a matter of quick manual clean-up after pasting fortunately, as pointed out in the Webucator video at the top of this post, it’s a simple matter of using MySQL’s REPLACE function:
|
1 2 3 4 5 6 7 |
SELECT CONCAT('=HYPERLINK("http://example.com?p=', p.ID, '","', REPLACE(p.post_title, '"', '""'), '")') AS post_title, CONCAT('=HYPERLINK("http://example.com/wp-admin/post.php?post=', p.ID, '&action=edit","Admin"') AS admin_url, DATE_FORMAT(p.post_date, '%Y-%m-%d') as post_date FROM wp_posts p WHERE p.post_status = 'publish' AND p.post_type = 'post' ORDER BY rand() LIMIT 50 |
See? Much better! Thanks again to Webucator’s instructor-led training services for putting together the video and improving upon the code!
Henrik C
I used this fetching data from SQLServer, but in Excel I had to run TextToColumns on the column to have it treat it as a hyperlink otherwisely it was just a string.
The solution was to generate the URL and reference it in a dedicated link column: =HYPERLINK([@[My_url]];”Click here”). I then hid the column containing the raw URL. Putting the link-column up against the data-block makes it part of it and copies the hyperlink formula into the rows needed.
Will Drotar
Agreed, article instructions do not work as intended.
Creating a string that begins with ‘=HYPERLINK’ will work if the Excel spreadsheet lives in Excel Online and it’s put into place using MS Graph API. But does not work as is using ODBC in Excel.