Edit exported data

Data from your shop is exported in the file format CSV. You can edit exported data from your shop with an external program.

Importing export files that were edited with an external program can lead to errors in your shop. Read this article carefully before editing a file and importing it into your shop.
We recommend the free program LibreOffice Calc for editing CSV files. Microsoft Excel can change the formatting of the files, which may damage them.

How CSV files are structured

CSV files always have the same structure, regardless of the type of data they contain. The first line contains the column names. Starting with the second line, the actual content starts. Each record (for example, each product) is shown in a new line.

If you open a CSV file using an external application such as LibreOffice Calc, the data is automatically sorted into columns. For product export files, these are, for example, the product name or the list price. The rows contain the individual data records, for example individual products.

Some things to keep in mind when working with CSV files:

  • If you change something in an existing line, the corresponding values in your shop will be replaced by the values from the import file.
  • Delete columns only in rare cases, as this can easily lead to errors. If you delete a column and import the file back to your shop, the values of the deleted column in the database of your shop will not be changed.
  • Be sure to never change the headings in the first line of the file, as these are needed to identify the products and values in your shop. You can make changes to all the other lines.
  • Do not add any new columns to the file.
  • You can add new lines (and thus new objects, such as products).
  • You may change the order of columns.
  • Because some export files contain many columns, they can be quite confusing. With Calc and similar programs, you can hide columns that are not relevant to you. These columns remain in the file, but are no longer displayed.
  • You cannot delete any data using the Export and import feature. You'll need to do this manually in your shop's administration area.

How to edit CSV files with LibreOffice Calc

  1. Export a CSV file in the administration area of your site and save it on your computer. Learn more about how to export data in the article Exporting data.
  2. Create a backup copy of the file.
  3. Start LibreOffice Calc or open a new LibreOffice Calc spreadsheet.
  4. Open the exported file in LibreOffice Calc.
  5. Make some adjustments to the formatting of the file:
    Option Description
    Character set When you exported the file from your shop’s administration area, you chose a certain Encoding – e.g. “Standard (UTF-8)”. For Character set, select the matching option.
    From row This value should remain “1”.
    Separator options Select Separated by and then the separator that you chose when exporting the file in your shop’s administration area. For String delimiter, the quotation marks should remain selected.
  6. Select Ok.
  7. Make edits to the file as desired.
  8. When you are done, save the file.

The file has been saved on your computer and is ready to be imported back into your shop. For further information, refer to Import of data.

How to add new data to the shop

Importing and exporting files is especially useful when adding large amounts of new data, for example when adding many new products to your shop, as you won't have to enter add product manually.

Each data record is displayed in one line. The best way to add a new record (e.g. a new product or a new customer) is to copy one of the existing lines and to paste it at the end of the table. If possible, choose a data record that is similar to the one you want to add, as this will give you a good blueprint for the new record.

The most important column is the identifier „[Alias]“: Each data record (each line) needs to have an individual identifier. This identifier is used by your shop system to check if a data record already exists or not.

Edit different types of data

You can add different types of data into your shop, incl. products, categories, customers, category product assignments, product types, bulk prices, cross-selling data and customer-specific prices.

Products
Note that during the import of products, the stock level is also imported. To avoid that, simply delete the stock level column from the file.

Categories
When adding new categories, the following columns are mandatory: [Alias] and [Class]

If there is no entry in the column “[Parent]“, the categories will be nested directly below the home page.

Category product assignment
This file contains information on which products are assigned to which categories.

You can only use this file to update existing products and categories in your shop. If you add new products or categories within the file and then import it, those are not added. You'll need to select the Products or Categories option from the dropdown menu for create an export file that you can use to add new products or categories.

Product types
This file not only includes your product types, but also the attributes and their values.

Customers
In the columns “[BillingAddress.Country]“ and “[ShippingAddress.Country]“, the countries are defined by country codes (e.g. DE for Germany and GB for the United Kingdom). For a list of all country codes, you can refer to this list of country codes.

Was this article helpful?

3 out of 6 found this helpful