Blog Relations
Import Excel into WordPress
I’ve just uploaded data from a spreadsheet with hundreds of posts into WordPress. It nearly drove me crazy in the process, but I think it saved time in the end. I could see this sort of data import being useful for something like an e-commerce site.
I have actually done this once before, but it didn’t seem any easier the second time. If only for the sake of my own remembering, here’s how it’s done.
First of all you need to add a plug-in of sorts to WordPress. I say “of sorts” because it’s more of a hack than a plug in proper. Many thanks to Site 2nd for this though. You can download Site 2nd’s modified CSV Import Script here.
Site 2nd has modified this script from one by Zac Preble. You can read Zac’s instructions here. But you are also going to need a few Excel tricks. So here’s some full instructions. I hope they make sense to you.
First of all, upload the modified CSV Import Script into /wp-admin/import/ (NOT into your plugins folder). Now in your WordPress admin , look under Tools, Import. You will see a lot of options for importing data including one that wasn’t there before – CSV, or Comma Separated (I’m not sure what the “V” is for !).
Now in Excel you need to prepare or modify your data. You are going to need some columns. The first are going to be as follows:
wp_title wp_post_date wp_category wp_content wp_tags
If you need custom fields, you can add some columns on the end with any names you like Apples, Pears, Oranges, etc … these will be the keys for your custom fields.
Fill your data under these columns. If you are familiar with WordPress it should be fairly obvious what goes where. Just one thing. The date has to be in this format, and it has to contain a time.
2009/01/30 12:00:00
It’s a bit of a B***er but Excel doesn’t have an option to covert dates into the this format. I had to change them all by hand.
Tags should be comma separated. In the data I received, they were all under separate columns. I had to create the tags under the wp_tag column by copying them from the other columns. I did this using an Excel formula. It’s like this:
=A2 &”,” & B2 &”,” & C2
Let’s break that down.
= means, well, equals. It tells Excel that this is a formula.
A2 is the first cell that I want to copy over into the column called wp_tags.
&”,” puts a comma after the first tag
& B2 copies over cell B2
Then, to make this formula work the whole way down the column called wp_tags, you have to grab the bottom right corner of the cell where you wrote the formula, and drag it all the way down. In the next row the cells A2 B2 C2 should be converted to A3 B3 C3 etc.
Now we are going to have to do something similar at the end so we can grab all our data and dump it into a text file with the extension CSV. This is the file that we will actually upload into WordPress.
I did this by creating a final column and copying all the other columns that I needed, over into it, and separating the data not with a comma, but with a Pipe | as per Zac’s instructions.
The formula I used looked like this:
=A2 &”|” & B2 &”|” & C2 &”|” & D2 &”|” & E2 &”|” & G2 &”|” & H2 &”|” & I2 &”|” & J2 &”|” & K2 &”|” & L2 &”|” & M2 &”|” & N2 &”|” & O2 &”|” & P2 &”|” & q2 &”|” & r2 &”|” &CHAR(10)
You should now recognise that the way to copy a cell is write:
A2
and for subsequent cells:
& B2
etc.
To add in a pipe separator write:
&”|”
At the end, you need to encode it all correctly for text with:
&CHAR(10)
Drag this formula all the way down the column as before. Now block and copy the whole column into a text file and save it with the extension .CSV. You will find that all your data is nicely dumped without any trace of spreadsheet formula. You just have a lot of data separated by pipes |.
You may fine some inverted commas in between the lines of your data entries. Just get rid of those with a quick search and replace in your text editor.
This isn’t all yet, though. Your CSV file needs a header. This will also be pipe separated and the same as in your spread sheet so it will begin
wp_title|wp_post_date|wp_category|wp_content|wp_tags|
And then have some more headings if you need custom fields
wp_title|wp_post_date|wp_category|wp_content|wp_tags|apples|pears|
Just put the header in first row of your text file above all the post entries.
Now you are ready to upload. But just try this out the first time with a couple of entries. If it uploads incorrectly, you don’t want to spend an afternoon deleting hundreds of posts. That’s when it gets really frustrating.
Go to WordPress, Tools, Import, CSV, and upload the file. The wheel should spin. It show the files it is uploading. It should wish you a nice day. You should then find the imported posts and see that all the data, including tags and categories has been entered correctly. I have to say, I didn’t get it right first time. Or second time,…. actually I lost count of how many times I tried. But I got there in the end. So it can be done. I hope you have swifter luck.
This info could come in handy one day Hugh. Thanks for posting up all the details.
In case you’re still wondering, CSV stands for comma separated values. Technically the file you create above contains pipe separated values.
To format dates into the required (US-centric) format do the following in Excel:
– select all the cells containing dates
– under the Format menu choose Cells…
– on the Number tab choose the Custom category
– paste (or type) the following into the Type box:
yyyy/mm/dd hh:mm:ss
Hi Adam,
Many thanks for sharing the Excel tip. I had searched for that without luck.
Curious when you up load can you control the content formatting.
For formatting, I haven’t tried to import with HTML included, but I believe you can. You can also use custom fields for formatting.
[...] Import Excel into WordPress – Blog Relations. No [...]
Hi Guy very informative article Adam, thanks alot.
As I try to figure it out , I though I would ask how I can add the comments which go with the articles?
I hopoe that makes sense.
Thanks
HI Sam, I don’t think you can add comments with a spread sheet. But you can look at some of the built in WordPress import helpers, in your dashboard, under tools, import. One these might bring in comments from whatever blog format that you are using.
“modified CSV Import Script here.” goes to http://www.site2nd.org/wordpress/csv-import-to-wordpress/ and “Error 404 – Not Found”
Where is the mod to be obtained??
Hugh, it’s a really cool plugin, but the link is dead on Site 2nd. Please upload or send me the modified plugin with tag support, because I can’t find only the original plugin without tag support. I really need this tool! Thanks
Finally I’ve found it on the linked page. There was 404 error, but from google cache here is the link to the modified plugin:
http://www.site2nd.org/wp-content/uploads/2008/05/csv1.zip
Hugh, Thanks for the information. It was very informative.
Best,
Dan
Dear Zoli, Thank you. I’m sorry I couldn’t find it.
Hi
Nice post, can I be cheeky and add a shout out for my plugin. It basically does what you describe above but all from a WYSIWYG interface.
It is intended for datafeed imports into wordpress, but you can use it for any CSV, Pipe or tab separated file. You upload it (or give it a URL of the file it it is on the internet) and it will detect the fields in the file, you can then use the fields to build up a template for the posts in wordpress meaning you do not need to format the spreadsheet before you import it.
have a look at http://www.digitalquill.co.uk/datafeedplugin/ I am about to release a new version which allows you to filter the import based on key words, so only import records containing a key word.
Thanks
Matt Houldsworth
The V in CSV is Values
Thanks a lot for your post. I have a blog with a few authors. I’m wondering is it possible to include the posts’ authors in a CSV file?
Thanks for this great work, the hack is fantastic.
[...] ??????? ? ???? ????? ??? ??? ?????? [????]. ????? ? ?????????? ???????? ??????? ???????? [...]
hi there,
how are you using this? all I get is WordPress database error: [Table 'wpress.wp_categories' doesn't exist]
SELECT cat_name FROM wp_categories.
If your a professional with money, need a fast solution with amazing flexability then the new Easy CSV Importer is the best. Comes with a price but you won’t believe the sheer number of functions it has to get every post just right. I think ECI is easily the best plugin for importing any excel or csv data.
http://www.webtechglobal.co.uk/services/wordpress-support/premium-plugins/easy-csv-importer
I just found a wordpress plugin and imported 16K+ records from csv files. check out at my site
Hast du noch mehr zu dem Thema geschrieben? Gleich mal das blog durchsuchen..
Methodenmann
Hey,
If you have a text editor which supports PERL regular expressions (such as UltraEdit), you can copy the column and do the following regular expression replace:
(\w*) (\d{1,2}) (20\d{2}), (\d{1,2}:\d{2}) UTC
With:
$3-$1-$2 $4:$500
Then you need to add in the leading zeros. For dates (make sure there is a space at the end of this one):
(20\d{2})-(\w*)-(\d{0,1})
With:
$1-$2-0$3
And for times (there’s a space at the beginning):
(\d{1}):
With
0$1:
Then you just need to replace -Jan- with -01-, -Feb- with -02- .etc.
Bit easier for large amounts of rows!
Andi