Today in this WordPress tutorial we’re going to talk about importing and exporting content into WordPress using conventional built-in tools (in WP), plugins for specific use cases, and last (but not least) some advanced PHP coding techniques (if you want to roll your own). In other words this article is for basic, intermediate, and advanced website owners.
WordPress is a content management system (CMS) that gives nearly anyone the ability to maintain a website without knowledge of code. On the other hand, with 60+ million WP websites online – more and more people are learning to code to customize their own websites. While there are currently more than 20,000 people making their living full time from WordPress in some fashion (according to the official survey earlier this year), there are hundreds of thousands tinkering with their own code. People learn to fix their own house, do their own home improvements, it only makes sense that more people each day will delve into learning more technology.
WordPress is equally capable of running a website with a few dozen pages, up to hundreds of thousands. I have personally created big and small WP websites, from a simple one page sales letter, to e-commerce with 10,000+ posts. When working with larger websites, sometimes you have to work with lots of data, content, and media. There are many ways to deal with that media.
Today we’ll talk about simple, intermediate, and advanced ways of dealing with the importing and exporting of content and media inside WordPress.
WordPress Default Import / Export
WordPress has an import / export tool you can find under “tools” in wp-admin:
If you haven’t used it before – you might not be aware of how easy it is to export your posts, pages, or custom posts types:
You can also export pages, comments, custom fields, categories, and tags – and then import them under “Tools -> Import”. Clicking import will trigger installation of the official WordPress Importer Plugin. This makes it easy to export your content from one site to another, which is handy if you’re creating a dev or staging site, or moving somebody from one place to another, etc.
How to export and import on WordPress
For the most part, WordPress export and import works great – but it has issues once a website gets to a certain size. Most shared webhosting accounts (and even VPS and dedicated servers) have a PHP upload limit set to 32MB or less. Sometimes you can edit this to 64 or 128MB in your cpanel, or directly in FTP using the php.ini file. Export usually goes well (unless your db is really big), it’s on import that the php upload_limit can be an issue. If you get past the upload limit, the next issue is “execution time” (will the PHP limits of your server allow the import to finish before it times out).
The general rule of thumb is, your database should be less MB than your php upload limit, and on most shared webhosts you should be able to upload a 64MB database (if your limit is that high) without much hassle. Beyond that things get a little bit sketchy. I have imported databases of 100-200MB on dedicated servers that had decent resources without much issue. Using the standard WP built-in import / export feature is something nearly anyone can do.
Use WordPress Import / Export Plugins
This brings us to the intermediate option – using plugins to import and export content. There are all kinds of import plugins available in the official plugin repository. There are ones for categories, tags, content, media, and more. There ones to import content from other kinds of CMS sytems, and there are ones that can import content from files or documents (like XLS).
Plugins usually handle things that the standard WordPress importer can’t. I’ll walk you through some of the different types of used over the years that have been helpful to me.
Static HTML to WordPress Import
HTML Import 2 is a plugin that does just what it says. It allows you to import static HTML files into WordPress. If you have an old static website you want to move into WordPress – this plugin can save you a whole hell of a lot of time. The new (and important) options are the ability to import pages into posts, pages, or custom post types, and the ability to import both images and documents. You can set tags, categories, meta info – and the whole 9 yards. Though I haven’t used it, there is an option to fix internal links on imported files to match new permalinks.
WordPress Import for Specific Plugins
There are plugins that allow import of content specific to plugins.
- MarketPress Product Importer
- ClassiPresss Ads Importer Plugin
- Gravity Forms Mass Import: this plugin goes beyond the standards GF import / export of forms, it acutally allows import / export of entries
- Woo Commerce CSV Importer – this is very handy if you build Woo Commerce websites
- Contact Form 7 to Gravity Forms Importer I can’t count the number of times I’ve had to migrate a bunch of Contact Form 7 forms to Gravity forms – massive time saver here
- Shopp importer – handy if you build Shopp e-commerce sites
CMS Migration to WordPress Plugins
There are plugins that help you migrate content from another content management system to WordPress, such as:
- Blogger Importer
- Delicious XML Importer
- Xanga Importer
- Import from Ning
- Canalblog Importer
- Gallery2 Importer
- Blogger Image Import to WordPress
- Posterous Importer
- DotClear2 Importer
- LiveJournal Importer
- Movable Type and TypePad Importer
- Shopify Importer
- Joomla 1.5 to WordPress importer
- FG Joomla to WordPress
You might have a need to import things that aren’t content inside WordPress.
Social Media WordPress Importers
Some plugins allow you to import content from Twitter, Facebook, Picasa, and other sources.
- DsgnWrks Twitter Importer
- Facebook Comments Importer
- Google+ Importer
- Easygram – allows you to import your Instagram images and photos
- Darkroom Facebook Photo Gallery is an awesome plugin that allows you to import photos from multiple Facebook accounts
WP RSS Multi Importer – this plugin unusually allows you to merge multiple RSS feeds. This is handy if you want one single RSS for a half dozen similar categories, or comments from specific pages – it has a lot of uses.
Widget Setings Importer / Exporter – This plugin has a special place in my heart, because it’s something I’ve wished to have for years. Fixing, maintaining, and cleaning WordPress sites, widgets settings have plagued me for a long time. The ability to import and export widgets settings is a huge god-send.
CSV to Sort Table: very unique plugin that imports a CSV file and then makes a sortable table in the content area of the page. This is a massive help if didn’t know how to code it, or didn’t want to embed a reader or document, etc. There’s another one quite like this (in the beginning stages) you might also be interested in called Tablepress.
URL Cloner: is an odd little plugin that allows you to copy content from one URL to another using the ClearRead API. I’ve used it once or twice, it does have it’s uses.
WordPress PHP Excel or CSV Import to MySQL DB
Last we’re going to talk about a “roll your own” technique for importing content directly into the WordPress MySQL database using PHP and coding your own script.
Recently I had a client that regularly updated the content on their website a couple of dozen times per month. The posts always contain the same data, and they’re in the same format. It turns out that they store the data in several Excel files per month. I created a custom PHP script that looks for files in a specific directory in FTP, it looks for XLS (CSV, XLSX, etc.) files, parses the content, and inserts them to the database as WordPress posts. They are assigned to a specific category and published immediately. My method could be adapted any number of ways.
You could use this script to populate a website with content during initial development, or as an ongoing solution to publish content. If you were really adventuresome – you might even create a script that allows people to send email with an attachment, and have the attachment auto-uploaded to the FTP directory, where the script could pick it up and auto-publish the content. Maybe I’ll do this in a future post =).
In a text editor create a new file called “parse_XLS.php”, or name of your choice.
When I’m creating a script for WordPress, I have to include the files and functions to allow me to interact with WP outside of a normal theme page. Although I don’t need all these to make this script work, this is the default of what I start with to begin coding:
You’ll see I turned error reporting on for PHP, and I included the wp-config, and some normal WP functions I might use. Change the path to your wp-config depending on it’s relation to this script on the server. You might consider putting this “outside the root” of www or public_html on your server for security purposes.
I’m going to show you the bits of coded I added section by section, and then the full script at the end. So if you’re following along realize the next portions will not run on their own. =)
I use the PHPExcel library, which will parse and read / write to XLS, XLSX, CSV, etc. This robust PHP library can both read and write to Excel files. You only need to include the files from the package for your specific usage (you don’t need them all, just the ones you require in your script).
These are the ones I use in this example for reading and parsing through an XLS file to insert into the WP DB:
Next I loop through the files in a set directory to find all the XLS spreadsheets for parsing. For my own purposes, I also put a heading in with the filename that’s being parsed – which is useful for debugging later if something goes wrong.
There are many ways to open an Excel file with PHPExcel, I’m using the OO approach here. First I open the input file, and then I setup a foreach to go through the worksheets.
Some spreadsheets will have one worksheet, some multiple sheets. For this project, I only wanted to parse the same worksheet, so I coded it to only parse the worksheet called “content” in any XLS files it finds. If it doesn’t find that worksheet, it does nothing. You can customize this to suit your needs, as well as add limits to rows or column parsing (if you don’t need them all).
In this next section we’re creating an array with the value of each row, and then we’re assigning those cells to values. You’ll see below title, content, author, date – all base needs to publish WP posts. You could (and probably will) add values for other things like categories, tags, etc. I needed to add 2 custom field values from the XLS I had – you’ll see those values below. This loop assigns the array vars to values we an use in the next section.
This section contains something unique you may or may not use. My XLS files contained a date in the B:1 cell in a second header row. The website I was working on required that we use that as the publish date of the post.
One thing I hadn’t considered at the time was I forgot that dates in Excel are formatted from the number of days since the year 1900. So a date like August 6 in Excel comes through as the number 14226 AND it comes through as an object, not an integer or string.
In this section of code I actually get the date from the specific cell, then the date (in object form) is converted to a string, and that’s converted to an integer. I had to format the date twice – once in mm/dd/yyyy form (because we used that in the post title), but then again in the format WordPress requires to insert posts in the DB (Y-m-d). If you’re getting dates from your XLS cells, you’ll thank me for this bit of code:
In this section we actually take the content we looped through from the Excel worksheet and insert it into the the MySQL WordPress database directly, created immediately published posts.
The first section properly maps the post_title, post_date, post_content, post_status, and post_author (replaced with the ID of the author you want to attach the posts to).
The wp_insert_post line actually inserts the post into the WP DB, and you have to actually add the post meta and assign the category after it’s inserted. In this example I assigned 2 custom fields I needed, and then assigned a date for our custom purposes, and last I assigne the category. You don’t have to pass a single category, you can also pass an array. You can also define a taxonomy for the category if you need to, or create tags, etc.
In the last line of this section I have a line for my own purposes to echo the post got inserted and it’s ID number – so I can visually see they got inserted, and the post ID numbers assigned. You could edit the first my_post array to contain whatever attributes you need to insert your posts in wp_insert_post. You can add anything from menu_order, to post_excerpt, post_password, post_parent, and more. If you don’t pass post_type, it’s assumed your inserting as a “post”, but if you pass the post_type var you can insert posts to a page or any custom post type you want. Follow the function link for details.
Last, but not least, once the script loops through all the XLS files in the directory, I actually move them into an archive folder (so they can’t be parsed again). I could delete them, but we wanted to preserve the files in case they were needed in the future. Again – edit and modify to suit the needs of your particular project.
Here’s the final script all put together for parsing XLS files directly into the WordPress database to create and insert new posts. One issue I ran into was, if you have an XLS file with multiple worksheets that exceeds your PHP memory limit while parsing, the script will die. We found in shared hosting usually the memory limit was 128MB, and we had one XLS that as 134MB when parsing – and the script couldn’t do it. So we had to break the worksheets out into separate Excel files for them to parse (then it worked fine). We also added a timeout of zero in the script just in case – you may or may not have to do that. Be cogniscent of the fact that in a shared hosting environment your mileage may vary.
What are your favorite ways of importing and exporting WordPress content and data? Please share your thoughts below.