WordPress Data Importer Tool

How many times have you wished to have a genie that would do your task in a snap of a finger? I badly wished it while working on my blog.

I’ve been spending too much time over the last couple of months working on WordPress blogs for posting hundreds of new articles. The amount of time I used to spend after each post e.g. adding new post, content, content formatting, adding media, setting featured image, entering tags and categories and finally publishing was taking approximately 5-7 minutes considering the server response time. On an average basis, I could post maximum 15-20 articles per day. Now as long as the number of posts were 20 per day its fine…

But problem arises in a scenario of an active blogger who need to upload higher number of posts this task will become melancholic, tiring, repetitive, time consuming and nonproductive.

That’s exactly the situation I had to confront with when recently, I needed to import a fairly large amount of data into WordPress blog. There were approximately 10,000 of records to import (that’s huge…) and I could not imagine adding all the items manually one-by-one into WordPress blog.

Though the market is flooded with plugins for import functionality, none of them pleased me particularly with those I have experienced so far. While some of the plugins were useful but the main features were blocked due to limited editions (not all features were available in free version).

And my pursuit towards perfection prompted me to write my own tool. So I created my own Genie

Now I got you hitched on to this post… So Continue to read on to know more…

How I did it?

A number of interesting techniques have popped in my mind and I guess it would be beneficial to others doing similar activity.

My first Approach was to enter record one by one using WP Data Importer interface and generate database that can be imported directly into WordPress. Though productivity and ease of task had improved to some extent, being a perfectionist I wasn’t quite impressed.

Then I came up with the idea to incorporate import feature to enhance this tool. I have decided to support both Comma-Separated Value (CSV) and Excel files for importing data.

I have used .NET Framework which is mostly used by new applications for Windows platform. I was importing CSV and Excel files, so I used .NET out of box interface for interoperability with COM object model of Microsoft Excel Application. The interface reads data from Excel one-by-one and loads it into application memory. Once all the records are processed successfully, I finally write them into output file which you can import directly into the WordPress blog.

What are the benefits?

  1. Easy to setup and use
  2. Hassle Free Data Import
  3. Dramatically reduce manual data entry
  4. Imports post title, body, excerpt, tags, date, categories, author etc.
  5. Columns in the CSV and Excel file can be in any order, provided that they have correct headings
  6. Validate records for duplicate rather than inserting a duplicate
  7. Support for adding new record manually
  8. Maintain history of data validations.

What after data import?

Once we have the data imported, you will see a notification in status-bar showing the number of records successfully imported in the system.

It also updates the total counter with the number of new records imported. The total count appears in the status-bar.

So that’s it?

Wait it’s not over yet…

Imported records will be displayed on the screen and there is also a feature for filtering records.

The data imported by WP Data Importer are stored in a database (XML) in a format supported by WordPress. You can view the location of the database on screen. The path to this file resides in a configuration file in the application directory so you can configure it easily.

I can then easily import this database (XML) into WordPress blog by simply log into your WordPress blog dashboard, then go to Tools → Import → WordPress, and browse the database (XML) file.

That’s it. YES we are done with importing thousands of records into WordPress.

I have prepared a screen-shot tutorial to show how it works at the end of this article. Keep reading till the end.

Who can benefit from this tool?

The import in my case happens a few times a week, and range in size from less than a hundred up to a few hundred thousand new records.

In case you frequently import data and hate spending time on the melancholic process of publishing data manually into WordPress then this tool is right for you!

Who is this tutorial for?

Someone who quickly wants to setup their website and have thousands of records to import from external sources.

Someone who frequently imports large amount of data that ranges in size less than a hundred up to a few hundred thousand.

This tool can import thousands of records within a few seconds and all you need to do is import the file produced by this tool directly into WordPress website.

So what’s next?

After putting enormous amount of effort in WP Data Importer, I’ve decided to add support for importing data for the WordPress plugins. Currently, WordPress does not provide support for importing content for plugins.

The requirement came in while I was preparing recipes content for WordPress recipe blog.

Again, I have ended up with a fairly large amount of recipe content.

The plugin that I am using to showcase my recipes on WordPress recipe blog is ZipList recipe.

So my next challenges were,

  1. Interface design for adding recipe content while adding a new post from WP Data Importer application
  2. Approach for importing recipes from CSV or Excel file and Trick to publish them into WordPress blog. (Interesting job)

The basis parameters or fields or elements of the recipe are as below:

  1. Recipe Title
  2. Ingredients
  3. Summary
  4. Notes
  5. Instructions
  6. Prep Time
  7. Cook Time
  8. Rating
  9. Yield
  10. Serving

As I was growing this tool, it ran out of the space on smaller screen resolution upon adding more controls for the above recipe parameters.

Now the question was how to display too much data on the screen. As I was building a Windows application, adding scrollbar was not at all a good option as it would not go with my entire design.

So my idea was to build a component that can be easily retrofit into the existing design and add a feature to expand and collapse its content. This means recipe fields will become visible only when I want.

To make the entire tool more user-friendly, I have gone one step further – now when I resize the window, my components would auto size so it looks good no matter what screen resolution I am using.

For importing recipe content (recipe ingredients, instructions, preparation time, cooking time, serving, yield etc.) along with post content (post title, author, categories, tags etc.), I have reuse the same functionality of reading CSV and Excel files. It’s just that I had to read additional recipe parameters (recipe ingredients, instructions, preparation time, cooking time, serving, yield etc.) along with other post fields (post title, body, excerpt, tags, date, categories, author etc.)

Now, the main problem was how these recipe parameters can be published into WordPress since WordPress does not support them.

It’s my out of box thinking that prompted me to write a module. The purpose of the module was to read recipes one-by-one process & validate its content and generate the insert scripts.

Once all records are processed and data (Xml) produced by WP Data Importer is imported into WordPress blog. These scripts need be run in one-shot against the MySQL database table (recipe table).

That’s it.

You can see the screenshot tutorial below.

 

TOPICS

  1. Prerequisites
  2. Dashboard
  3. Configure field delimiter
  4. Import Data
  5. Map WordPress fields to CSV or Excel columns
  6. Adding post/record from UI
  7. Support for Featured Image
  8. Filter View
  9. Support to configure WordPress custom fields
  10. Support for ZipList Recipe Plugin
  11. Map CSV or Excel columns to ZipList plugin table columns
  12. SQL Insert Script Generation for ZipList plugin

If you have any questions or corrections, please leave a comment at the end.

Pre-requites

Before you start using this tool, you need to generate WordPress eXtended RSS or WXR file.

It’s very easy to export content from your WordPress blog. Simply log into your WordPress.com blog dashboard, then go to Tools > Export.

tools-export

Choose what you want to export. In our case, you choose All Content, and click Download Export File button.

Place the exported file into a desired folder and map the file’s path in tool’s configuration file.

For example,

Dashboard

This is the main screen that greets you at the start.

Configure field delimiter

You can configure field delimiter depending on the file you’re importing.

To change field delimiter, go to Tools > Field Delimiter

Import Data

Importing data is pretty easy.

This tool allows you to import data from CSV, Excel and TXT files.

To import data, Click on Import button. This will open a dialog box to prompt the user to select a file. After user has selected a file, it will read the first line from the file considering it as a header row. The field delimiter value configured above is used to separate fields in an import file.

It will then prompt you to map the import file fields to WordPress fields.

Map WordPress fields to CSV or Excel columns

The import file field names that match with the WordPress default fields will be selected by default. You can also manually map each field with the correct WordPress field.

Sometimes you may want to import all your records with a particular post status. You can do so by selecting the status from the dropdown list.

Once you have mapped all the required fields, click on Save button. This will trigger an import process. Once all records are imported successfully, it will display notification at the bottom of the grid.

You can see in screens above that it has processed 50,000 records in just 7 seconds.

Adding post/record from UI

There is also a provision to add new record from UI.

To add new record into an existing list, enter the details as shown in the screen below.

Click on Save button.

Support for Featured Image

You can also add featured image to the post while adding from UI. To add featured image, click on Browse Image picture box. It will then prompt a user to select a picture to be set as post featured image.

Filter

To easily view and analyze data imported, you can use filters. You can use search textbox above grid to find a particular record. You can even filter records by post type using Filter dropdown.

Support to configure WordPress custom fields

The tool has default WordPress fields already setup for you.

You can add custom fields here in case you want to import them from CSV or Excel files.

To add custom field, click on Add Field button. This will open a form to add custom fields as below.

You can add multiple fields in one go and then add them to the list by clicking Ok button. The custom fields will be added to the existing list of columns as shown below.

Next, when you import a file and if those fields exist in a file then you would be able to import them as well.

Importing Content into WordPress

It’s very easy to import content into WordPress. Simply log into your WordPress.com blog dashboard, then go to Tools > Import.

tools-import

Choose WordPress platform, browse the output file generated by this tool and follow the instructions.

Importers April 2013

That’s it.

Your WordPress blog has all you want.

Support for ZipList Recipe Plugin

If you’re a food blogger you will probably want to use a recipe plugin. The reason I have added a support for ZipList is that I had to import recipes for one of the food blog.

There are a few good recipe plugins around but the one I ended up choosing is ZipList.

ZipList is a user friendly plugin for adding recipes to any of your posts and pages.

ZipList plugin is SEO friendly which means you recipes are more likely to appear in search engines. It makes it easy for you to style your recipes. All you need to do is enter the ingredients and the cooking instructions. There are other optional parameters that you can configure for use.

It also has bonus feature and that is the Save Recipe option which allows your readers to quickly save your recipe to a universal recipe and shopping list directly from your site which also links back to your blog.

It also has feature to print the recipe.

Map CSV or Excel columns to ZipList plugin table columns

To import recipe content along with post, ensure your import file has recipe details (recipe ingredients, instructions, preparation time, cooking time, serving, yield etc.)

Click on Import button. This will open a dialog box to prompt the user to select a file. After user has selected a file, it will then prompt you to map the file fields to WordPress fields.

The field names that match with WordPress default fields will be selected by default. You can also manually map the file field with the correct WordPress field.

To import recipe content along with post, you will need to map file fields (recipe fields) with ZipList plugin table columns. To map columns, click on DB Map button. This will open a dialog for mapping as shown in screen below.

All you need to do is define below things:

  1. ZipList recipe database name (by default added)
  2. ZipList recipe table name (by default added)
  3. If clause to perform check if recipe already exists in database (by default added)
  4. Add return value (by default added)
  5. Map file fields with ZipList recipe plugin table columns.

Once you have mapped all the required fields, click on Save button.

SQL Insert Script Generation for ZipList plugin

The tool will generate MySQL insert scripts for ZipList plugin which you can run on your WordPress database but after you import the output generated by this tool into your WordPress blog.