WordPress – Auto create Post into the MySQL database from Excel data file

Posted by Shek on October 5, 2011 under WordPress | Read the First Comment | Total View: 28,690 views

In the post, I would like to show you how to create post for wordpress without having to use the wordpress admin console. This will be done by creating the posts into the wordpress MySQL database


To do so, the whole procedure is divided into 3 steps:

  1. Create an Excel contains all the desired posts.
  2. Import the excel into a MySQL table.
  3. Create the posts between the MySQL table in step 2 and the WordPress post table.

Create an Excel contains all the desired posts
For a much convenient way, create an excel with 2 columns:

  1. Post title
  2. Post content

Input all of the post into the Excel (there is no need to enter the column titles), see below:
Create WordPress post with excel

Import the excel into a MySQL table
As we now has all the posts in an excel file, we then create a MySQL table within the wordpress database:
A table ‘allpost’ with 2 fields:

1
2
3
4
CREATE TABLE 'allpost' (
'post_title' VARCHAR( 50 ) NOT NULL ,
'post_content' VARCHAR( 200 ) NOT NULL
)

Then we import the excel into the MySQL table with phpmyadmin,
please refer to my past post: Import Excel into MySQL with phpmyadmin on how to do so.

Create the posts into the WordPress post table
Now we have all the posts in a MySQL table ‘allpost’, we then use the wordpress build in function to create post:

1
wp_insert_post();

The syntax of it is:

1
2
3
4
5
6
7
8
$thepost = array(
     'post_title' => 'Post title',
     'post_content' => 'Post content',
     'post_status' => 'publish',
     'post_author' => 1,
     'post_category' => array(1,2)
  );
wp_insert_post( $thepost );

The code above is to create one single post, and by replacing ‘Post title’ with the real title, ‘Post content’ with the real content, and for
the ‘post_category’ => array(1,2), the number has to replace with the category number, to find out what the category number is, the easiest way is to install a plugin “Simply Show IDs“, all the category number are separated with a comma “,” and it is possible to add more categories.

As we need to create all the posts from the MySQL table ‘allpost’ which we made from the excel, we need to use a while loop, and by doing so we create a createpost.php and here is the code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//the line below is connect to the wordpress database
require('./wp-blog-header.php');
 
//select all from table allpost and loop through one by one record
$result = mysql_query("SELECT * FROM allpost") or die(mysql_error());  
while($line = mysql_fetch_array( $result )) {
//create post
$thepost = array(
     'post_title' => $line['post_title'],
     'post_content' => $line['post_content'],
     'post_status' => 'publish',
     'post_author' => 1,
     'post_category' => array(1,2)
  );
wp_insert_post( $thepost );
}

Put the createpost.php along with the wordpress files, run the file, the posts will be created.

For further reference on wp_insert_post(), please visit:
WordPress.org Function Reference/wp insert post

Not what you want? Try a Search

  • Tjenester said,

    Hi
    can you tell me how to add post to wordpress database manual with an external php file insert without using wordpress admin

Add A Comment

*