Exporting and importing data in WordPress

Several tools can be used to export and import data, some of them are included in WordPress, some of them are offered by hosting. Therefore, it is always useful to know and understand the principles of the database and the files stored on disk (as you will not find the database on the server among the files). A lot of data can be exported from WordPress in this way, that is, at the lowest level through physical files and the database.

Exporting and importing data

Basically, the following file types/formats are used for data transfer by export:

• XML, in which documents have a hierarchical structure and can conceptually be interpreted as a tree structure
• CSV, the first line is usually a description of columns, values are separated by comma or semicolon on the line
• JSON, bracketed key:value or key:value array, can be nested.

PDF is not a data transfer format, you can’t import anything from it, neither can XLS, which is proprietary (closed, proprietary, de facto usable primarily in Excel), ODS is a spreadsheet for LibreOffice, but it is also not used for export. The files are simply supposed to be text files, which all of the above do.

Tools > Export

The first tool we can use for export and import is in the Tools menu. The output of this command is a file in XML format, which is a standard text format related to HTML (the format in which web pages are created). The XML file contains references to so-called namespaces, which define the meaning of the structure within the XML file.

In the case of the export file, it is an extension of the format used for the RSS feed (i.e. the namespace is based on RSS). It is extended to store information about users, user fields, taxonomies, and other links to other data in WordPress.

You can export all data types stored in the _posts table (except revisions), i.e. all articles. If you have various custom post types on your site, then they will automatically appear here and can be exported. By default, of course, these are posts, pages, and attachments; you can also download all or part of the content of the whole site (but this is not possible with custom types).

Among the common article types, there are also various auxiliary data types that are not used by the end-user. These can be elements or auxiliary templates for graphic design, discount coupons for WooCommerce, product variants or maybe transaction records (for example, I’m looking at a site that has a Wholesale for WooCommerce plugin and it stores the requirements to create a user with permissions for wholesale purchases).

Unfortunately, no file data is downloaded by default, so if you export the entire site content, there will be no image files; these must be copied otherwise (manually). The Export media with selected content plugin will also help you.

Exporting and importing blocks for Gutenberg

If you would like to export reusable blocks from Gutenberg, you won’t find the item, but that’s why you subscribe to this newsletter to find out. 😊

The address is /wp-admin/edit.php?post_type=wp_block and blocks can only be exported one at a time. As a matter of principle (they are written in React, which is a JS framework) they are in JSON format. You can select the JSON file type there and import the block (but this is easier to do directly via clipboard when editing the document).

We found the seam, the edge between the old and new WordPress!

Data source processing

After exporting, we usually edit the data somehow before importing it again. Sometimes we need to delete something, link something differently, or merge two different data sources. The key is a unique identifier for each row of data. This is most often the post id. It can be used for searching and creating sessions.

I almost always edit files in LibreOffice Calc. It loads CSV by default and has a wizard to see if the data is loading correctly. But it can also load an XML file (Data > XML source) and create a table from it. And then, of course, save the CSV.

If you want to convert the data to CSV in another way, you can use one of the online tools (I don’t have the courage to do that, the data is too valuable) or try an application. I’ve done it a couple of times, I’ll write about it towards the end of this newsletter.

Tools > Import

The XML file previously exported in this way from WordPress can be uploaded again to (another) WordPress in Tools > Import. This way you can quickly and efficiently transfer the content of your website.

In addition to content from WordPress, you can import content from older blogging systems such as Blogger, which are now more or less obsolete. A bit interesting is the possibility to import RSS feeds from other sites, saved in an XML file… 🙄 However, this is all „90s“, everything is done a bit more modern nowadays and this part is very antiquated.

For a successful import, you must meet the default requirements:

• The plugins that ensure the functionality of custom post types must be active in the system. For example, if you want to import products and you don’t have the WooCommerce plugin active, the data will not be imported and you will see skip record information during the import.
• Don’t be afraid to run the import multiple times if everything doesn’t get done within the process run limit. Existing entries are skipped. Hence, it is not possible to update them.
• A common issue is that the file size set on the hosting is too small. If WordPress offers you a maximum file size of, for example, 2 MB, you can’t control it in WordPress. You need to change the setting directly on the hosting (in the web server section, it is post_max_size). Needless to say, this is what can be charged for.

• I’d like to reiterate that images and other files are not uploaded during import (unless you used the above plugin). These must be transferred to the server manually (usually direct copy via FTP is sufficient) or use their extra export/import – Media type (see image above).

Export and import data using plugins

There are a lot of plugins for exporting and importing data, I have seen the best results with WP All Import & Export plugins, on the other hand small single-purpose import plugins, for example Import and export users and customers, also serve very well. Because usually the main issue is the logic for exporting or importing, not the amount of data or the ability to save it in a given format. In other words – exporting orders from users who have spent a nice hunk of change and have as source in custom fields that they came from Facebook is no longer a trivial task. It’s often better to connect some external analytics tool to the database.

The WP All Import/Export plugin is very well designed and allows you to specify exactly which data to update and which not to update. A big advantage is the possibility to influence the process with a custom function written in PHP, which I used when matching posts and comments according to the id stored in custom (for CPT) and meta fields (for comments). We were porting an old website built on CMS to WordPress and it was necessary to create a custom structure for discussions.

Export and import template settings

Probably every template allows you to export and then import your settings. This option is usually included in the template settings and although it is different for each template, the data transferred are usually the same – it is usually JSON. In other words, it is configuration data converted into a long string, often transcoded to not show what’s hidden in the string. Usually you can just transfer the file via clipboard. (You can see in the picture that the browser – here it’s LibreWolf – can display the JSON nicely.)

For example, the WordFence plugin can transfer its configuration via a text string – a token. Once you have a site set up, you can transfer its configuration to another site and clone the configuration in seconds (except for the license).

Working with the WordPress database

It is important for a technician to be able to navigate the WordPress database and be able to solve some tasks directly there. For example, exporting one site from multisite is quite easy at the database level, of course if you know what you are doing and why. So you should know where the data is stored, how it’s connected to each other and how you can change it. Exporting and importing is just a step away.

Adminer or PHPMyAdmin database managers allow you to export data in CSV format, which you can then load in a spreadsheet (Excel, Calc). I also recommend compressing the export directly with gzip.

Select the corresponding tables, remember to check that you want not only the structure but also the data (the second column of the table). You can see the expected number of data records. Depending on the program you will then use for processing, select comma or semicolon as separator, it plays a role. 

Then you can open the file in LibreOffice Calc, which is a free download. Select that the columns are separated by semicolons/commas, that the values are enclosed in parentheses. You certainly won’t want a tab as a separator, but sometimes it can be a completely different character. All of this can be specified. You can even set the value type for each column – you click on the column (in the picture it has a black background) and select the type from the list above the table. Then the file will open, you can edit its contents and then save it again as a CSV, again you will have similar parameters to choose from (comma, semicolon, quotes).

You can import it using Adminer. You need to go to a specific table and activate the Import link at the very bottom.

The standard import (top left) is used to upload the entire database. If it has only a few megabits, just use the browser. If it has a few hundred megabits, then you need to:

• compress the file using the zip or gzip algorithm,
• upload under adminer.sql[.zip] to the location where Adminer itself is on the server,
• make sure that you have enough time on the server to perform the import job,
• press the button for Import – it will be done directly on the server, not through the browser, so it will be significantly faster.

Adminer has a lot of plugins, for example for exporting to JSON or XML. As you can see, it’s still the same thing by different means.

Data transfer using wp-cli

Working with wp-cli is very convenient if you know the command-line. Each of the plugins nowadays also adds its module to wp-cli. Plugins also work in the WordPress text interface, which is wp-cli.

• wp ai1wm is the interface for the All-in-One WP Migration plugin
• wp all-import for WP All Import
• wp import and wp export correspond to items in the Tools menu
• and so on (wp stands for wp-cli)

Of course there are other commands, but this article was about exporting and importing data.

Did you learn anything new? Write to me. I might have forgotten something.