Search
Close this search box.
Search
Close this search box.

WP All Import

How to use WP All Import

In order to use WP All Import, you must have a csv file that contains your products.

You will usually want to transfer the fields below:

  • SKU
  • Title
  • Description
  • Price
  • Featured image
  • Category name (or categories with a parent/child relationship)
  • Attributes
  • Length, Width, Height
  • Brand name
  • Post status (publish, draft, trash)

If your products have hierarchical (parent/child) categories, then in your category csv field, you should have this format:

Sports > Golf > Clubs > Putters

For attributes, give a latin name (if you use non latin, then you may exceed 28 max character length) and translate it with loco translate.

For attributes, the csv column cell should have this format (let’s say that we have a size attribute):

L | XL

Testing the import

If you have a large spreadsheet and want to run a fast import do these:

  1. Go to Import Settings.
  2. Click the Configured Advanced tab.
  3. Check the Import only specified records.
  4. Insert the value 1.
  5. Save Import configuration.
  6. Run the import (as many times as you like)
  7. When you are finished with the tests, just uncheck the Import only specified records checkbox and resave the configuration.

How to use WP All Import with Google Spreadsheets

Step 1: Publish your spreadsheet to csv

To do so:

  1. Open your google spreadsheet
  2. From the menu, press File > Share > Publish to the web
  3. At the pop-up, below link, select the table you want to add
  4. Right next to it (at the Web Page dropdown), select Comma Separated Values
  5. Press publish
  6. Copy the url

Step 2: Import to WP All Import

To do so:

  1. Go to All Import > New import.
  2. Press Download from URL, paste the CSV url from step 1.6 and press download
  3. Select Existing items and choose WooCommerce Products from the dropdown
  4. Press Continue to Step 2
  5. Add Filtering Options if you want (eg. price greater than zero).
  6. Press Continue to Step 3
  7. Drag from the right section the values you want to grab from the csv and drop them to the correct field on the left
  8. Save settings as a template
  9. Press Continue to Step 4
  10. Drag and drop the sku value to the _sku field
  11. Warning! On the Update existing posts with changed data in your file tick the Choose which data to update. Otherwise, it will erase the existing values.
  12. Click Configure Advanced Settings and in the Friendly name, give a name (eg. products).

Things to notice on step 7:

If you have a product with hierarhical categories, then at Taxonomies, Categories, Tags tab tick the Product categories, select the Products have hierarhical… option and tick the An element in my file contains the entire hierarchy (i.e. you have an element with a value = Sports > Golf > Clubs > Putters) checkbox and drag and drop the corresponding item to the categories field.

If you want to use content data, bear in mind that you cannnot keep the text formatting (eg. bold words).

Here is a list of the data you want to tick on step 11:

  • Title
  • Content
  • Attributes – Update all attributes
  • Custom Fields. Here you will find custom fields like length,width and prices. Make sure you tick it in order to synchronize the dimensions.
  • Taxonomies – Remove existing taxonomies, add new taxonomies

Here are some custom fields:

  • _price
  • _sale_price
  • _regular_price
  • _stock_status

Stock level in google spreadsheet

  1. Create a column Stock status.
  2. Select all column cells (except for header) and click Data > Data validation.
  3. In criteria select List of items, paste instock,outofstock,onbackorder and click save.
  4. Create a column Manage stock.
  5. Select all column cells (except for header) and click Data > Data validation.
  6. In criteria select List of items, paste yes,no and click save.
  7. Create a column Stock.
  8. Go to WP All Import, edit your import, and click WooCommerce Addon.
  9. Click inventory and add your columns in manage stock, stock and stock status.

Seo title and description

  1. Create a column SEO Title.
  2. Create a column Meta Description.
  3. Go to WP All Import, edit your import, click custom field, add _yoast_wpseo_title and enter {seotitle[1]} %%sep%% %%sitename%%.
  4. Click custom field, add _yoast_wpseo_metadesc and drag your column.

Images

  1. Create a column Product Images.
  2. Go to WP All Import, edit your import, and select Use images currently in Media Library.
  3. Check Set the first image to the Featured Image (_thumbnail_id).
  4. Upload the desired images through WordPress to your library.
  5. Enter the image filenames in the column.
  6. Run import.

How to protect your Google spreadsheet from errors

Protect first row from changing

In order to protect your client (the spreadsheet administrator user) from accidentally changing the first row of the spreadsheet, you should do the following:

  1. Mark the first row (the one that contains the SKU, TITLE, etc.)
  2. Go to Data > Protected Sheets and Ranges…
  3. Enter a description (eg. Protect first row)
  4. Click set permissions.
  5. Detick the users which you do not want to be able to edit this row.
  6. Click save.

Set predefined values for a cell

Some cells, may accept only a value from a list of values (eg. for the size cell, acceptable values could be S, M, L, XL).

In order to protect your client (the spreadsheet administrator user) from accidentally entering a wrong value in a cell, you can create an in-cell dropdown list, you should do the following:

  1. Select the cell or cells where you want to create a drop-down list.
  2. Click Data > Data validation.
  3. Next to “Criteria,” choose List of items and set your values like this Value 1,Value 2.
  4. If you want people to only enter items from the list, choose Reject input next to On invalid data.
  5. Click Save. The cells will show a drop-down list.

Do not allow duplicates in the SKU column

Your SKUs will be unique. In order to prevent user from enetering a duplicate sku in column A, add this validation.

  1. Select all cells from column A (except for first row)
  2. Click Data > Data Validation
  3. In Criteria, select Custom formula
  4. In the rule right next to it, add the code below
=COUNTIF($A$1:$A$10000, INDIRECT(ADDRESS(ROW(),COLUMN(),)))=1

How import users

To do so, you will have to download and activate the User Import Add-On.

Fill all fields in User Account Info.

In Other User Info, fill at least these:

  • Role
  • Nickname
  • Display name
  • Registered
  • Nicename

For billing and shipping fields, in custom fields, click see detected fields and leave everything except for those referreing to billing and shipping, eg.

  • billing_first_name
  • billing_last_name
  • …

How to import orders

In order to import orders, first you have to ensure that you make the match to your current products that belong to the order.

If you really want to add all orders, then you should 

  • Go to order items
  • Tick Manually import product order data and do not try to match to existing products
  • Fill all the fields

This way you will add orders even for products that do not exist on your site anymore.

How to import coupons

Create a new import. You will need to fill 

  • Title
  • Content
  • Publish date

You will also need these custom fields:

  • coupon_amount
  • date_expires

How to export product images from one site to another

  • Go to the first site and in WP all export, export only sku and image url
  • Go to the second site and setup an

How to transfer your content from another site to WordPress

First, get a csv file that contains all the posts (or products) of your previous site (eg. OpenCart, Joomla, Drupal etc.). So find an appropriate plugin, install it and export the csv file.

Now, process the csv file, in order to be compatible with WP All Import.

One rule, is that each row should have a column that has unique values (eg. the SKU code of a product).

In OpenOffice, by using CTRL+F, you can use regular expressions in order to delete unwanted sections (eg. shortcodes). Make sure you click more options and tick Current selection only and Regular expressions. Here are some useful expressions.

Search for Replace with Result
\[myshortcode.*myshortcode\]  space  Remove shortcodes
[:space:]+  @@ 1st step to remove multiple spaces
@@ space 2nd step to remove multiple spaces
\.\B space Remove last dot

In MySQL, you can fix possible errors, in the product’s content text with the replace function. For example, to fix possible image url errors, you can use:

update wp_posts set post_content = replace(post_content,'http://www.mywebsite.com/image/http://www.mywebsite.com','http://www.mywebsite.com');

In order to remove non-breaking spaces you can use:

update wp_posts set post_content = replace(post_content,'&nbsp;', '<p></p>');

How to use WP All Import with WPML

If you have a multilingual store, then you probably have the WPML plugin installed. Download and install WPML All Import plugin. This is the bridge between WPML and WP All Import.

You can find more details about this plugin to the official documentation page.

Let’s say that you have eg. 2 languages, English (default) and Greek. Here are the steps:

  1. Create your 2 csv files. One csv per language.
  2. Go to All Import > New import.
  3. Press Upload a file and choose your english csv.
  4. Select New Items > WooCommerce Products and press Continue to step 2
  5. On step 3, on the WPML Add-on tab, make sure that you select the English (default) language. Also, on the Automatic Record Matching to Translate, select Import data in main language (English).
  6. Map the unique identifier to the correct element in your CSV or XML file. 
  7. Finish the steps, and let the plugin import your english products.
  8. Now for the other language, first make sure that you first create the english product categories before importing the products.
  9. Repeat steps 2 to 4.
  10. On step 3, on the WPML Add-on tab, make sure that you select the other language (Greek).  Also, on the Automatic Record Matching to Translate, select Define parent import, select the english CSV and set the unique key (in my case it was the SKU column, common in both language.
  11. Finish the steps, and let the plugin import your greek product translations.

How to export product images from one site to another (when products have the same skus)

  1. Go to the site where you want to get the images.
  2. Create an export with only SKU and Media > Images > URL.
  3. Download the CSV.
  4. Go to the site where you want to insert images.
  5. Create an import with the file that you created in step 3.
  6. Choose Update products.

How to activate WP All Import for users with not administrator privileges (eg. for a shop manager)

  1. Download and activate Capability Manager Enhanced.
  2. Go to Users / Role capabilities.
  3. Select Shop manager and press load.
  4. Tick the manage options checkbox and press Save changes.

How to change the imported prices

First create the desired function and insert in WP All import.

<?php
function change_price($price) {
    if ( !empty( $price ) ) {
        $price = 1.36 * $price;
    }
    return $price;
}
?>

Then go to the price field in your import and add this one:

[change_price({price[1]})]

Check here for the official documentation.

How to set stock according to a custom string

First create the desired function and insert in WP All import.

<?php
function set_stock($string) {
$stock_status = 'instock';
if ($string == 'Not Available') {
$stock_status = 'outofstock';
} else if ($string == 'Low stock') {
$stock_status = 'lowstock';
}
return $stock_status;
}
?>

Then go to the Stock status field in your import and add this one:

[set_stock({availability[1]})]

How to speed up import process

Disable the do_action to Speed Up Imports

Every time WP All Import creates a new post it uses wp_insert_post. The problem with this function is that it wasn’t designed for creating a lot of posts at once – it was designed for creating one post at a time. It can be slow, and part of the reason for the slowness is the do_action calls.

▸ Solution

In Manage Imports ▸ Import Settings ▸ Advanced Options you can enable the Increase speed by disabling do_action calls in wp_insert_post during import feature.

This will prevent other software in your WordPress site from interacting with posts as they are imported, so other plugins that are supposed to automatically do things to new posts may not work for imported posts.

Migrating from Shopify to WooCommerce

  1. Go to shopify > Products > Export all products to CSV.
  2. Use wp all import and insert them (works for simple products only, not variable)
  3. Repeat the process for customers and orders as well.
  4. Install ExIm ‑ Export / Import data app in shopify.
  5. Go to Shopify > Blog articles export articles.
  6. A zip will be downloaded. Get the larger file and upload it in Convert JSON to CSV.
  7. Download the CSV and use wp all import to import your posts.
  8. Match articlestitle, articlesbody, articlesimagesrc, articles publishedat.

Migrating from OpenCart to WooCommerce

  1. In OpenCart, install Export/Import Tool.
  2. Go to Tools / Export / Import.
  3. Select Products and click Export.
  4. Open Google drive, create a spreadsheet and import the file from step 3.

In your file you will see that the image will be in the format

catalog/products/randomfolder/productimage.jpg

In order to do the import correct insert in wpml

https://mysite.com/image/{image_name[1]}

To get the attributes:

  1. Select the ProductAttributes sheet.
  2. Select all (without first row), right click, view more cell actions and click define range.
  3. Give a name eg. ProductAttributes.
  4. Click done.
  5. Insert something like =QUERY(ProductAttributes, CONCATENATE(“select D where A=”, A2,” and C=’Size'” ), 0)

To get the categories:

  1. In OpenCart, install Export/Import Tool.
  2. Go to Tools / Export / Import.
  3. Select Categories and click Export.
  4. Open the previous spreadsheet and import this file.
  5. Open categories, select all (without first row), right click, view more cell actions and click define range.
  6. Give a name eg. AllCategories.
  7. Click done.
  8. Go to products table and insert a column to the right.
  9. Insert =QUERY(AllCategories, CONCATENATE(“select C where A=”, D2), 0) in first cell.
  10. Drag the bottom right to the end of rows to generate all categories in wp all import format.

Warning!

After having calculated the values, make sure you copy all cells to a new sheet and paste special (values only)!

This is important, so that the file is lightweight and you do not crash wp all import!

To get blog posts:

  1. Find your theme’s RSS feed. For example, for journal is https://www.mywebsite.com/index.php?route=journal2/blog/feed
  2. Use wp all import for new posts and use this url.

How to import fields when you have XML with attributes

Let’s say that you have an XML with this format:

<Row TableName="STOCKITEMS">
<Field Name="CODE" Value="00-03" DataType="ftInteger" XMLID="3"/>
<Field Name="DESCR" Value="BINGO-ΝΕΧΤ QES ΥΠΕΡ.ΑΜΙΧ."/>
<Field Name="VATAPERCENT" Value="24.00"/>
<Field Name="WHSPRICE" Value=""/>
<Field Name="RTLPRICE" Value=""/>
</Row>

To get the description:

[FOREACH({Field[2]})]{@Value}[ENDFOREACH]

How to export the csv file using MySQL

Open your MySQL database using PHPMyAdmin and use something like that:

SELECT *
FROM `products_table`
WHERE `language_id` = 4
ORDER BY `product_id` ASC
INTO OUTFILE 'C:/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

How to use cron jobs for imports

  1. Click Settings right next to your import.
  2. Click Scheduling options.
  3. Click Manual scheduling.
  4. Copy Trigger and Processing URL.
  5. Open your cpanel.
  6. Click Cron Jobs.
  7. If you want to run your import every 24 hours, you should run the trigger URL every 24 hours. 
  8. The processing URL should be run every two minutes because it may not finish your import in one run.

For step 7, use the following cron:

Minute Hour Day Month Weekday
0 0 * * * 
wget -q -O - "https://site.com/wp-load.php?import_key=nNikq7dYr&import_id=1&action=trigger" >/dev/null 2>&1

For step 8, use the following cron:

Minute Hour Day Month Weekday
*/2 * * * * 
wget -q -O - "https://site.com/wp-load.php?import_key=nNikq7dYr&import_id=1&action=processing" >/dev/null 2>&1
WordPress Speed Optimization Guide