Want to get list of all blog post urls and post titles of your WordPress blog in Excel sheet format? And don’t know how to retrieve published post links?. Then you are at right place.
You need not go to each and every blog post, page and custom post type to get URLS since it is time-consuming. All you require is a piece of PHP code to do the job. I often retrieve published links for “Powerful Interlinking“ blog posts.
Read: Ultimate Guide to Get High Web Traffic from Specific Country
I used this method to get all links published in “Computer Tips & Tricks“ blog and carried out link building for driving traffic to my blog. Internal link building will increase page views and reduce bounce rates.
Read:
5 Tips to Increase Backlinks
Top 100 Dofollow Forums To Increase Backlinks
WordPress is a Powerful tool for publishing your content, driving traffic and get good reputation from people who love your blog niche.
Export List of All WordPress Published Post URLS
In this step-by-step tutorial I will guide you how to export all published post, pages and custom posts of your WordPress blog.
- First we need to create a new file named geekyard.php. (Note: I used TextWrangler – Text Editor tool)
- Copy and paste the below PHP inside geekyard.php and save it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | <?php include "wp-load.php"; $posts = new WP_Query('post_type=any&posts_per_page=-1&post_status=publish'); $posts = $posts->posts; /* global $wpdb; $posts = $wpdb->get_results(" SELECT ID,post_type,post_title FROM {$wpdb->posts} WHERE post_status<>'auto-draft' AND post_type NOT IN ('revision','nav_menu_item') "); */ header('Content-type:text/plain'); foreach($posts as $post) { switch ($post->post_type) { case 'revision': case 'nav_menu_item': break; case 'page': $permalink = get_page_link($post->ID); break; case 'post': $permalink = get_permalink($post->ID); break; case 'attachment': $permalink = get_attachment_link($post->ID); break; default: $permalink = get_post_permalink($post->ID); break; } echo "\n{$post->post_type}\t{$permalink}\t{$post->post_title}"; } |
- Now login to your site’s FTP account using FileZilla or any other FTP software.
- Once logged in, upload the newly created file into your site’s root directory (Public_html).
- Now open your browser and enter the URL http://yourdomain.com/geekyard.php in the address bar and hit enter.
- Now the uploaded file will be executed and you will get a list of all the post URLs in your WordPress site.
- Save the text file directly from browser.
How to Export WordPress URLS in Excel Sheet
With the exported text file from browser its very difficult to work with. So, now let’s see how to import this php file in Excel and make use of it. We are here going to see “Delimited (i.e Characters such as commas or tabs separate each field” method in Excel sheet.
Text to Columns feature is very useful to split a list to multiple columns in Excel. This method is talking about how to split data by specified delimiter with Text to Column feature in Excel. Please do as follows:
- Launch Microsoft Excel and open the exported file.(i.e Php file saved from browser).
- When you open the php file it will automatically launch “Text Import Wizard”.
- Step 1 of 3: Select “Delimited” type and click Next.
- Step 2 of 3: Select “Delimited tab” and click Next.
- Step 3 of 3: Select “General” in Column data format and click Finish.
- That’s it!!! Your text file datas will appear Columns in Excel sheet as shown below.
- Save this in .xml file format for future use.
Using this excel you Give Life to Your Older Blog Posts by creating strong post interlinks for future as well as existing blog post.
Thanks man, works like a charm