Import / Export: Basic module

Version

5.5.0 or newer

Table of contents

With the help of the Import / Export plugin you can import large amounts of files via CSV or XML. 

Installation

The Import/Export module can be installed like any other plugin. After installation, please reload the backend. This will make the plugin visible in the menu structure. If necessary, empty the cache and reload the page once again. 
You will find the plugin under the Content dropdown menu in your backend.

Plugin configuration

The plugin configuration offers the following settings:

  • With import/export to proceed when an error occurs: Should an error occur, this option defines the default action – either abort the entire import/export or simply skip the line causing the error.
  • The Import image mode determines whether the importer should import the same item image several times or only once.
  • The option Use comma as decimal separator sets, whether a comma or dot is used as a decimal separation character.

The backend module

The new Import/Export backend module has 4 areas: Import, export, protocol and profiles.

Import/export manager

This area makes it possible to import and export different data and allows you to manage your most recent changes.

Export

In the export tab you can export data from the system in either csv or xml format. You choose your previously created profile and the export-format in here.

Differing from the export type (articles, orders, customers, categories) there might be additional options appearing, which limits the exported data more accurately. 
The different filter-options are explained in detail in the further articles for the different profile types.

After you set the configurations to your liking you can start the export by clicking on the "Start export" Button. With this a window appears which shows the number of calculated datasets. 
A click on "Start exporting" will export those datasets in a file that you can download by clicking "download" after the export is finalized. If you need the file later on you can always download it in the tab "Protocol".

Import

In the tab "Import" you can import existing xml or csv files. The needed file can either be pulled into the given zone via Drag'n'Drop or be selected by using the "Choose" button from your locale computer. 
Afterwards you only have to select which profile should be used for this file. With this you have to mind for the column names/ xml-node-names to exactly match the selected node-names in the profile.

Now you can start the import by clicking on "Start import". This also opens a window, which calculates the number of datasets in the file where you can start the actual import by clicking on "Start importing". 
To avoid overwhelming the servers and causing a timeout, large imports can be performed in incremental steps. If an import is cancelled, it can be easily retrieved and completed by visiting the Protocol tab.

Protocol

Here you will find all previous imports/exports (operations). This allows you to easily reimport, download or delete files at any given time. You also have the option of viewing or continuing broken imports.

Together with the “Logs”, this view acts as an invaluable tool for managing all current and archived operations in order to optimize your imports and detect the source of any errors.

Profile

The Profile tab is the cornerstone of the new Import/Export module. The profile defines the “appearance” of an import or export, also the connection(mapping) to the different database columns.

The Import/Export module already contains many useful profiles e.g. for the item creation, customer imports or order exports.
Those default profiles you can hide by activating the checkbox "Hide default profiles", so you just get shown your individual profiles.

You can set up completely individual profiles which are made for your own needs. 
First click on "Add profile" to create a new profile. Now you are requested to choose a name and a default profile, which your own profile should be based on.

If you want to create for example an item profile you might choose "Articles minimal" for "Based on", the name can be selected as you wish like "Articles - Shopware". 
After a click on "Save" a profile is created, that contains all columns of the base profile. To this you can add or delete columns freely.

On the left site of the profile window you now see a tree that shows the xml data structure. 
If you choose a column (like: ordernumber), you can edit the name of the node in the xml/csv on the right and maybe name it "itemnumber", just like the way the column, or xml-node is supposed to be named later on. 
Below you can choose which shopware-column the node is connected to (in this case "orderNumber").

Here you can choose from all columns the active data type offers - because you selected "article" as a type before you can choose from all available item fields. 
Which fields are available for the different profile types you can see in detail in the further wiki-articles for the different profile types.

Configuration of the Profile

Use the Extend datasetNew column and New attribute (xml) buttons to create additional data fields for a profile.

A column is a data field that can be used to contain information such as "Item name". 
To create a new column node, first assign a name under "Node name" then assign a dataset from the "Shopware column".

Nodes in XML can also contain so-called "Attributes", which are data fields that can be defined within the node itself.


<myColumn myAttribute="hello">
    Universal
</myColumn>

In this example, we created a node called "myColumn" and an attribute called "myAttribute". Ultimately the column and attribute can both contain data, however this depends on your requirements and the extensions you use.

Finally a dataset is a special kind of element for data fields. Items can have multiple datasets (so-called "ToMany relations"), which may include, for example: prices, categories or properties.

A dataset has a node name (i.e. "MyPrice"), adapter ("category" for categories; "price" for prices) and a "parent key" (i.e. the price associated with the actual item). A dataset node itself actually contains no data, but with additional columns and attributes added to it, the data fields of the corresponding adapters can be selected (such as the pseudoPrice of the relevant price or name of the relevant category).

Ultimately datasets can be understood as a "lower profile" as they incorporate a item profile price, category or image.

In the database the dataset is like a join, which means a connection to another table. 
For the price-dataset this would be a connection between the main article-table (s_articles) and the price-table (s_articles_prices).

The dataset only sets up a connection between those tables, which means it takes care that both tables "know each other", but do not give any actual data. 
Only after the dataset has been connected correctly the fields from the price-table can be exported and imported because the importer knows which item has which prices.

XML / CSV

This module supports CSV and XML file formats. Although profile creation is very similar to XML format, every profile can always be exported or imported in CSV. 
In this case, the module will use a flat CSV format with column names based on your nodes.

If you do not require an XML profile, rather wish to work exclusively with CSV, you can disregard the nodes and create a flat profile. Following the “Items – Shopware” example above, the exported CSV would look like this:

In comparison, an XML export would look like this:

Edit files

CSV

To edit or create csv-files you should use a program like the free Open Office, which allows you to create files in a valid UTF-8 format. 
If you edit a file in Excel, which currently does not support a valid UTF-8 format, you might get problems with special characters.

In case you have a file that is not a valid UTF-8 you can correct this with a program like Notepad ++. 
For this you open the not correctly formatted file in Notepad ++ and open an additional empty file, for which you manually set the coding to UTF-8. 
Now you can copy the whole text of your original file with ctrl-a and ctrl-c and put it the new correctly coded file with ctrl-v. 
This new file you now save as a .csv file and import the now correctly coded file in the system.

XML

For the editing of xml-files you can use any program you want. The only important thing is that the structure and format of the file stays correct.

Conversions

In the Profile tab you can perform Conversions, which allow you to independently manipulate the content of a field during an import or export.

For instance, this makes it possible for you to define the “active” flag of an exported item with values other than “0” and “1”, such as “activated” or “deactivated”. 
The reverse is also possible for imports, so that the “activated” string is recognized as “1” and “deactivated” string as “0”.

Replacements and other string operations can also be performed – even mathematical calculations are supported.

For the Smarty engine: the names of the fields that apply for the current profile will appear in the conversions window after a new entry is added. The available selections are prepared for Smarty use and designated with a $ sign.

Shorten names to 30 characters


{$name|truncate:30}

Increase price by a factor of 10


{math equation="x*10" x=$price}

Requests from other fields, strings


{if $attributeAttr8 < 10}
    4  
{else}
    $attributeAttr9
{/if}

“activate” and “deactivate” instead of the active flag output


{if $active == 0}deactivate{else}activate{/if}

Console tools & cronjobs

The Import/export module allows you to perform imports and exports directly from the command line. Furthermore, imports can also be automated using cronjobs simply by placing them in a dedicated directory.

Currently it is not possible to export images via cli!

Console commands

Profile view

For the import and export via console you need existing profiles.

With the command:


php bin/console sw:importexport:profiles

you can view a list of all existing profiles.

Export

Exports can be executed from the Shopware directory using this command:


php bin/console sw:importexport:export

For this you have to give the following parameters:

  • -p/--profile: profile name, such as "Items - Shopware"
  • filepath: name of the exported dataset, such as: "out.xml"

The export format is automatically determined by the extension of the specified file, so a name ending in “.xml” will export to XML format; a name ending in “.csv” will export to CSV.

Additional parameters could be:

  • -f/--format: enforce an export format (CSV, XML)
  • -x/--exportVariants: include item variants in your export (items)
  • -o/--offset: Offset
  • -l/--limit: Limit

Import

To import an existing file into the system you can use this command:


php bin/console  sw:importexport:import

The following parameters are necessary:

  • -p/--profile: profile name, such as "Items - Shopware"
  • filepath: name of the dataset to be imported

Example:


php bin/console sw:importexport:export -p "Articles - Shopware" out.xml

, while the file path is always referring to the current folder. If you are in the shopware main folder and pick "out.xml" the file has to be in the same folder, which means the shopware main folder.

Optional:

  • -f/--format: enforce an import format (CSV, XML)

Cronjobs

The new Import/export module allows you to deposit CSV or XML files in the directory files/import_cron/
When a standard Shopware cronjob is activated, the system will import the files into the directory once per day. 
The duration between two passes can of course be adjusted in the basic settings (see: Cronjobs).

So that the profiles for the imports can be correctly assigned, the profile name must be present in the filename between the two points. 
For the profile name "Items - Shopware" the command could look like this:


export.Items - Shopware.24.09.2014.csv

or


Items - Shopware.csv

Imports can be revisited later in the backend module under Protocol. Imports with an error will be stored in the directory files/import_export/ with a "broken-" prefix.

You can trigger cronjobs directly via the console by using a command such as:


php shopware.php SwagImportExport/cron

You may also push cronjobs using wget or by calling a browser:


http://www.my-domain.com/backend/SwagImportExportCron/cron

data exports are only possible using the Shopware console. Imports can only be carried out via cronjobs!

Importing and exporting profiles

In the import/export-module you have the possibility to import created profiles, which you previously exported as a JSON file. 
For this you can use the button "Import profile" in the tab profiles. 
A profile can be exported with the blue export button in the profiles line.