Creating a WordPress Import File with Excel

WordPress Import Files Using Excel

Disclaimer: I absolutely do not advocate splogging of any kind. This is simply a way to get your existing data into a WordPress blog. This same method could be used for chapters in book, recipes, or whatever other kind of data you may have. Rather than taking the monumental effort of manually creating a WordPress site based on your work, you can use a little creativity to come up with a fast, easy solution that is surprisingly robust (this article creates a WordPress blog website with over 31,000 posts).

I’m a big fan of native file formats – as they say, “when in rome”…. Yeah. Anyway, this is a quick article to illustrate how to set up WordPress Import using an Excel spreadsheet. I’ll try to cover this from point A to point Z and even provide the download files and set up a sample URL so you can see the results.

The WordPress 2.8 Import File screen lists fifteen different file formats including: Blogger, Blogroll, Blogware, Bunny’s Technorati Tags, Categories and Tags Converter, DotClear, GreyMatter, Jerome’s Keywords, LiveJournal, Movable Type and TypePad, RSS, Simple Tagging, Textpattern, Ultimate, and WordPress. I would be lying if I claimed to know what any of these formats actually are, so I’m going to go the easy route and export a file and use it as a template.

The WordPress export file saves out as a .wxr file. This format is called the WordPress eXtended RSS file format and contains posts, pages, comments, custom fields, categories, and tags. After performing a WordPress Blog Export from another blog, I can see that what I’m primarily concerned with is the “item” section which corresponds to a post. I’ll chop out the “item” section and save it as its own file. This will be a template that I’ll use later on from VBA. (I cut a couple elements from the bottom as well which I doubted were required by the schema – flying fast and loose on this one. You’d want to make sure your file conforms with any applicable XSD or DTD, but I’m fairly confident this will work.)

I’m inserting some placeholders in the template sample WordPress Import file. These placeholders will be search and replaced as we create the actual import file(s). Here is the XML snippet of the template and here is the file available to download (coming soon).

<item>
    <title>[propertitle]</title>
    <link>[link]</link>
    <pubDate>Fri, 12 Jun 2009</pubDate>
    <dc:creator><![CDATA[admin]]></dc:creator>
    <category><![CDATA[[category]]]></category>
    <category domain=”category” nicename=”[category]“><![CDATA[[category]]]></category>
    <guid isPermaLink=”false”>http://www.biblewack.com/[testament]/[category]/[hyphen]/</guid>
    <description></description>
    <content:encoded><![CDATA[[content]
<p align=”center”><span style=”font-size: x-small;”>
<a title=”[backtitle]” href=”[backlink]“>&lt;&lt; [backtitle]</a> || <a title=”[nexttitle]” href=”[nextlink]“>[nexttitle] &gt;&gt;</a></span>
]]>
    </content:encoded>
    <excerpt:encoded><![CDATA[[content]]]></excerpt:encoded>
    <wp:post_id>[postid]</wp:post_id>
    <wp:post_date>2009-06-12</wp:post_date>
    <wp:post_date_gmt>2009-06-12</wp:post_date_gmt>
    <wp:comment_status>open</wp:comment_status>
    <wp:ping_status>open</wp:ping_status>
    <wp:post_name>[hyphen]</wp:post_name>
    <wp:status>publish</wp:status>
    <wp:post_parent>0</wp:post_parent>
    <wp:menu_order>0</wp:menu_order>
    <wp:post_type>post</wp:post_type>
    <wp:post_password></wp:post_password>
</item>

Programming the WordPress Upload

So, I wanted to create a big WordPress site for this example – really big. I struggled to think of what kind of freely diseminable data I could come up with to create a massive WordPress Import sample. I found a good source of data with the Bible. Free to distribute and in nice text file format. Each line numbered (think “unique identifier”) and each line with a hard return after it. A perfect data structure for import into Excel (and eventual import into WordPress). I’ll digress from the WordPress Import briefly to illustrate how I sliced and diced the data to get it ready.

1. Import into Excel without delimiters. 1 column
2. Chop out Line Numbers with For-Next Loop and instr(function). 2 columns
3. Derive Chapter Name with Lookup function on alpha characters in previously created column. 3 columns
4. Assign Old Testament or New Testament with manual drag in Excel. 4 columns
5. Figure out what the URLs will be and make URL column. 5 columns

The reason I wanted to figure out the absolute URL for each post is I wanted to include “back” and “next” links in the body of each post so one could traverse throughout the entire book from post to post. This is accomplished simply by looking at the previous row (x-1) and the next row (x+1) while executing the loop.

Here is the entirety of the routine to create the .wrx Import file. You may need to split your file up differently depending on your import limits. You can easily do this by saving out a file every XXXX-th time or whatever you need. The code is very simple to understand. I loop through every data row in the spreadsheet (31102 rows and a header). Variables are assigned to the cell string values. A string variable “S” is assigned the result of our Text2String function. As I’m thinking about it now in retrospect, rather than call that code every time – you could just call it initially and create S once. Then you could assign another variable to the value of S every time rather than going through the readfile process, much more efficient…..Oh well… Lastly, I append the contents of S into a file using Print.

Sub makeWordpressImportFile()

For x = 2 To 31103

sPostid = x
sCategory = Trim(LCase(Sheet1.Cells(x, 2)))
sCategory = Replace(sCategory, ” “, “-”)
sTestament = Trim(LCase(Sheet1.Cells(x, 1)))
sTestament = Replace(sTestament, ” “, “-”)
sBackTitle = Trim(Sheet1.Cells(x – 1, 4))
sNextTitle = Trim(Sheet1.Cells(x + 1, 4))
sBackLink = Trim(Sheet1.Cells(x – 1, 7))
sNextLink = Trim(Sheet1.Cells(x + 1, 7))
sContent = Trim(Sheet1.Cells(x, 8))
sLink = Trim(Sheet1.Cells(x, 7))
sProperTitle = Trim(Sheet1.Cells(x, 4))
sHyphen = Trim(Sheet1.Cells(x, 5))

s = Text2String.Text2String(“c:\scott\item.txt”)
s = Replace(s, “[postid]“, sPostid)
s = Replace(s, “[category]“, sCategory)
s = Replace(s, “[testament]“, sTestament)
s = Replace(s, “[backtitle]“, sBackTitle)
s = Replace(s, “[backlink]“, sBackLink)
s = Replace(s, “[nexttitle]“, sNextTitle)
s = Replace(s, “[nextlink]“, sNextLink)
s = Replace(s, “[content]“, sContent)
s = Replace(s, “[link]“, sLink)
s = Replace(s, “[propertitle]“, sProperTitle)
s = Replace(s, “[hyphen]“, sHyphen)

MySitemap = “c:\scott\bible.txt”
‘set and open file for output
fnum = FreeFile()
Open MySitemap For Append As fnum
Print #fnum, s
Close #fnum

Next x
End Sub

Finally, if you’ve been paying attention, you’ll know that we haven’t yet created a well formed XML file – there is no root node. We’ve essentially concatenated together a bunch of nodes, but there is no root. I doubt very seriously this would import into anything. You could write something to open each file and paste in the root node and matching end node – or you can manually open and save your files out. I chose the latter as I didn’t have many files to work with. I used the following as my root node:

<rss version=”2.0″
    xmlns:excerpt=”http://wordpress.org/export/1.0/excerpt/”
    xmlns:content=”http://purl.org/rss/1.0/modules/content/”
    xmlns:wfw=”http://wellformedweb.org/CommentAPI/”
    xmlns:dc=”http://purl.org/dc/elements/1.1/”
    xmlns:wp=”http://wordpress.org/export/1.0/”
>

…and obviously my closing node was simply “</rss>” which indicated the end of file. Once you have your file(s), all you need to do is import it and WordPress will handle the rest. The categories will be created and the posts will be created. You could obviously fine tune this by working with the dates – future dating posts does work (you can make it look like you’re working when you’re really not!). The sample site that I created is at www.biblewack.com (very hard finding an available URL with “bible” in the title). Good luck.

Popularity: 34% [?]

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • MisterWong
  • Y!GG
  • Webnews
  • Digg
  • del.icio.us
  • StumbleUpon
  • Reddit

6 comments to Creating a WordPress Import File with Excel

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Sponsors