WordPress Import Export Tricks: XLS, CSV, and Plugins

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:

wordpress import export tools

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:

wordpress export

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.

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.

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 CSV Import

Maybe you need to import content from a CSV file? You could use the (new) WP Ultimate CSV Importer Plugin, CSV Importer, and CSV to Post.

WordPress Import for Specific Plugins

There are plugins that allow import of content specific to plugins.

Such as:

CMS Migration to WordPress Plugins

There are plugins that help you migrate content from another content management system to WordPress, such as:

You might have a need to import things that aren’t content inside WordPress.

Importing Users:
Import Users from CSV
Members Import
CSV User Import
Simple Import Users

WordPress Category Import:
Batch Category Import
Category Import

Social Media WordPress Importers

Some plugins allow you to import content from Twitter, Facebook, Picasa, and other sources.

Special Mention:

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 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 conent, and inserts them to the databaes as WordPress posts. They are assigned to a specfic 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:

 
error_reporting(E_ALL|E_STRICT);
 
//include the files we need to work with WordPress in an independant script
require_once("../wp-config.php");
$wp->init(); $wp->parse_request(); $wp->query_posts();
$wp->register_globals(); $wp->send_headers();
global $wpdb;
global $current_user;
$current_user = wp_get_current_user();

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:

 
//include the following files, you may or may not need them all
//update the paths for where you put them on your server
require '../Classes/PHPExcel.php';
require_once '../Classes/PHPExcel/IOFactory.php';
require_once '../Classes/PHPExcel/Calculation/TextData.php'; 
require_once '../Classes/PHPExcel/Style/NumberFormat.php'; 
require_once('Excel5.php');
require_once('Date.php');

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.

 
//dir the XLS files are located in for parsing
$filedir = "../XLSdir/*";  
 
// Open the directory with XLS files, and proceed to read its contents  
foreach(glob($filedir) as $file)  
{  
    echo "<h3><b>Now we are parsing this XLS file</b>:  " . $file . "</h3>";  
 
	//location of the XLS file we are going to parse and work with
	$inputFileName = $file;

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.

 
	//loading the XLS file with the PHPExcel library
	$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
 
	//setup foreach to work through all the rows and columns 
	$worksheet = null;
	foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {

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).

 
		//only parse data when we find the worksheet that contains it
		$worksheetTitle = $worksheet->getTitle();
		if ($worksheetTitle == "content")
		{
			$worksheetTitle = $worksheet->getTitle();
			$highestRow = $worksheet->getHighestRow(); // e.g. 10
			$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
			$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

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.

 
			// create array to hold value of each row
			$val=array();
 
			for ($row = 3; $row <= $highestRow; $row++) {
 
				$title = "";
				$content = "";
				$author = "";
				$date = "";
				$custom_field1 = "";
				$custom_field2 = "";
				for ($col = 0; $col < $highestColumnIndex; $col++) {
 
					$cell = $worksheet->getCellByColumnAndRow($col, $row);
					$value = $cell->getValue();
					switch ($col)
					{	// assign a var to each call form each column so we can work with them
					case 0:
						$title = $value;
					break;
					case 1:
						$content = $value;
					break;
					case 2:
						$author = $value;
					break;
					case 3:
						$date = $value;
					break;
					case 4:
						$custom_field1 = $value;
					break;
					case 5:
						$custom_field2 = $value;
					}
				}
 
				if (!$code || strlen(trim($code)) == 0)
					break;
 

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:

 
				// get the date from cell B:1
				$date = $worksheet->getCellByColumnAndRow(1,1);	
				//the Excel formatted date coming back from the cell is an object, convert it to a string
				$dateint = intval($date->__toString());
				//now we have to convert that string to an integer
				$dateintVal = (int) $dateint;
				//now we format that date in mm/dd/yyyy format to make the post titles
				$newdate = PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "MM/DD/YYYY");
				//WordPress requires Y-m-d H:i:s for post_date so we have to reformat for that
				$wpPostDate	= PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "YYYY/MM/DD");

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.

 
				// details for the post we're about to insert
				$my_post = array(
				  'post_title'  => $title,
				  'post_date' 	=> $wpPostDate,
				  'post_content' => $content,
				  'post_status' => 'publish',
				  'post_author' => 1
				);
 
				// Inserts and publishes a new post into the database
				$postid = wp_insert_post( $my_post );
 
				// add the custom fields for each post
				add_post_meta($postid, 'custom_field1', $custom_field1);
				add_post_meta($postid, 'custom_field2', $custom_field2);
				add_post_meta($postid, 'date', $newdate);
 
				// set the newly created post to the CSO Code category
				wp_set_object_terms($postid, 'products', 'category');
 
				echo "<i><b>Successfully inserted post " . $postid . " to the WordPress DB</i></b><br /><br />";
			}

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.

 
			//move the files to an /XLS-archive folder just in case needed in the future
			//then delete the copy in the /XLS folder so it's not parsed again in the future
			if (copy( "../XLSdir/" . basename($file),"../XLS-archive/" . basename($file) )) {
			  unlink( "../XLSdir/" . basename($file) );
			}			
 
			break;

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.

 
error_reporting(E_ALL|E_STRICT);
 
//include the files we need to work with WordPress in an independant script
require_once("../wp-config.php");
$wp->init(); $wp->parse_request(); $wp->query_posts();
$wp->register_globals(); $wp->send_headers();
global $wpdb;
global $current_user;
$current_user = wp_get_current_user();
 
//include the following files, you may or may not need them all
//update the paths for where you put them on your server
require '../Classes/PHPExcel.php';
require_once '../Classes/PHPExcel/IOFactory.php';
require_once '../Classes/PHPExcel/Calculation/TextData.php'; 
require_once '../Classes/PHPExcel/Style/NumberFormat.php'; 
require_once('Excel5.php');
require_once('Date.php');
 
//dir the XLS files are located in for parsing
$filedir = "../XLSdir/*";  
 
// Open the directory with XLS files, and proceed to read its contents  
foreach(glob($filedir) as $file)  
{  
    echo "<h3><b>Now we are parsing this XLS file</b>:  " . $file . "</h3>";  
 
	//location of the XLS file we are going to parse and work with
	$inputFileName = $file;
 
	//loading the XLS file with the PHPExcel library
	$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
 
	//setup foreach to work through all the rows and columns 
	$worksheet = null;
	foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
		//only parse data when we find the worksheet that contains it
		$worksheetTitle = $worksheet->getTitle();
		if ($worksheetTitle == "cso rpt")
		{
			$worksheetTitle = $worksheet->getTitle();
			$highestRow = $worksheet->getHighestRow(); // e.g. 10
			$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
			$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
 
			// create array to hold value of each row
			$val=array();
 
			for ($row = 3; $row <= $highestRow; $row++) {
 
				$title = "";
				$content = "";
				$author = "";
				$date = "";
				$custom_field1 = "";
				$custom_field2 = "";
				for ($col = 0; $col < $highestColumnIndex; $col++) {
 
					$cell = $worksheet->getCellByColumnAndRow($col, $row);
					$value = $cell->getValue();
					switch ($col)
					{	// assign a var to each call form each column so we can work with them
					case 0:
						$title = $value;
					break;
					case 1:
						$content = $value;
					break;
					case 2:
						$author = $value;
					break;
					case 3:
						$date = $value;
					break;
					case 4:
						$custom_field1 = $value;
					break;
					case 5:
						$custom_field2 = $value;
					}
				}
 
				if (!$code || strlen(trim($code)) == 0)
					break;
 
				// get the date from cell B:1
				$date = $worksheet->getCellByColumnAndRow(1,1);	
				//the Excel formatted date coming back from the cell is an object, convert it to a string
				$dateint = intval($date->__toString());
				//now we have to convert that string to an integer
				$dateintVal = (int) $dateint;
				//now we format that date in mm/dd/yyyy format to make the post titles
				$newdate = PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "MM/DD/YYYY");
				//WordPress requires Y-m-d H:i:s for post_date so we have to reformat for that
				$wpPostDate	= PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "YYYY/MM/DD");
 
				// details for the post we're about to insert
				$my_post = array(
				  'post_title'  => $title,
				  'post_date' 	=> $wpPostDate,
				  'post_content' => $content,
				  'post_status' => 'publish',
				  'post_author' => 1
				);
 
				// Inserts and publishes a new post into the database
				$postid = wp_insert_post( $my_post );
 
				// add the custom fields for each post
				add_post_meta($postid, 'custom_field1', $custom_field1);
				add_post_meta($postid, 'custom_field2', $custom_field2);
				add_post_meta($postid, 'date', $newdate);
 
				// set the newly created post to the CSO Code category
				wp_set_object_terms($postid, 'products', 'category');
 
				echo "<i><b>Successfully inserted post " . $postid . " to the WordPress DB</i></b><br /><br />";
			}
 
			//move the files to an /XLS-archive folder just in case needed in the future
			//then delete the copy in the /XLS folder so it's not parsed again in the future
			if (copy( "../XLSdir/" . basename($file),"../XLS-archive/" . basename($file) )) {
			  unlink( "../XLSdir/" . basename($file) );
			}			
 
			break;
		}
	}
} 
 
exit;

What are your favorite ways of importing and exporting WordPress content and data? Please share your thoughts below.

This article was authored by:

John Pratt is principal WordPress developer at JTPratt Media, where you can find him working on custom themes and plugins for small business and corporate clients.

John Pratt has authored 12 posts.Visit Website

Showing 14 Comments

  • This is a great tip. I will share for my friend, thanks

    REPLY
  • Nice post, don’t forget Easy CSV Importer. Focuses on packing plenty premium features but in a lightweight straightforward plugin.

    REPLY
  • thanks for sharing! really useful info!
    also you may add one more CMS migration to WordPress: http://wordpress.org/plugins/cms2cms-joomla-to-wp-migration/

    it moves site content automatically and sets up 301 redirect as well!

    REPLY
  • KB

    Thank you so much for this. The custom php script you made saved me a huge amount of time I would’ve spent figuring this out myself. Bless you and your children. haha.

    REPLY
  • Do you know any plugin that can export wordpress page with spesific criteria?
    I want to export only one page include with it’s sub pages. The standard woerpress plugin only give option to export all pages or all posts. Can’t request to export only a spesific page with it’s sub pages.

    REPLY
  • I’m looking for a way to export in CSV format my products only and use same CSV to import to ecwid. Any ideas on this?

    REPLY
  • I couldn’t found nothing here about EXPORTING WordPress content as the title of the article could suggest, but thanks anyway.

    REPLY
  • Also misled by the title – expected to find info on exporting!

    REPLY
  • This is the most comprehensive tutorial about how to import into WordPress. Bookmarked! Thank you.

    REPLY
  • And how to update a post?

    REPLY
  • That is a really good tip especially to those fresh to the blogosphere.
    Short but very accurate information… Many thanks for sharing this one.
    A must read post!

    REPLY
  • I’m looking for something which will import fields from excel in to wordpress, lets say a 6 digit number which relates to a date. This gets stored, say in the SQL. A user of the web site comes along, enters a six digit number and the site is able to parse the date from this and take the user to a specific url based on the date, ie is the date less than 14 days ago(then go here) or more(go here).

    REPLY
  • I need advice on exporting to CSV. I have an eCommerce store and I need to build a custom form for warranty registration after the sale is complete which will then need to be batch uploaded at midnight every day.Can anyone suggest a good plugin to do this with?

    Because this will occur after the sale, am I right to assume it will have to be done outside of woo commerce?

    REPLY
  • Hi. I had a hard time uploading my WordPress XML to my new web site. Three-four tries were marked “Failed” as to all tags, and all posts.

    It finally “worked” — however, the posts are all reversed as to date, with the oldest post stuck to the front of the web site, and the newest to the back.

    How did that happen?

    Can I fix this? Easily?

    Thanks for your time.

    REPLY

Add Your Voice: