I was asked to take a spreadsheet of WordPress posts and add a category to each post. This is easily done if the category is the same for each of the posts. I thought about re-using a wp-cli bash command I had used previously to add one category to a list of post ID’s.
for x in $(cat posts_list.txt) ; do wp post --url=https://wpmulti.site/blog-slug/ term add $x category category-slug ; done
Doing that would have required splitting the posts into separate .txt files for each category, which was not feasible with the number of posts and categories in the spreadsheet.
The spreadsheet included the post ID, title, and the text name of the category.
Clean up the data
The first step is to properly format and sanitize the spreadsheet, to change the category names to the correct corresponding slug. This was relatively simple in Excel, by changing the case of the category names to lowercase, removing all special characters such as ampersands and commas, and replacing spaces with hyphens. I didn’t need the titles at all, so I deleted that column. In the first row, I added column headers: post_id and post_category.
Note: be sure you have entered the categories in WordPress before you run the import, and make sure the slugs match what is in the import spreadsheet. Otherwise, you will have to edit the category names after the import from the newly created category slugs.
Export the spreadsheet to CSV.
Parse a CSV file and add categories to WordPress posts by post id and category slug
Here’s a simple BASH command to parse a CSV and run
wp post term add category, using while and IFS (Internal Field Separator):
while IFS="," read post_id post_category ; do wp post --url=https://wpmulti.site/blog-slug/ term add $post_id category $post_category ; done < "post-add-category.csv"