Editing exported data
You can find further information on importing, exporting and editing exported data in the article Import and export of data – overview.
You can edit exported data from your shop with an external program. Usually you edit the export files in the file format CSV.
Editing exported files with an external program can create errors. Please read this article carefully before editing a file and importing it in your shop.
We recommend the freely available program OpenOffice Calc for editing CSV files. Microsoft Excel can change the formatting of the files, which can damage them.
How are CSV files structured?
CSV files always have the same structure, independently from 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 OpenOffice 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.
Please note the following concerning CSV files:
- If you change an existing line and then import the file back to your shop, the corresponding values in the database of your site will be replaced by the values from the import file.
- You should delete columns only in exceptional cases, as this can quickly 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 site will not be changed.
- Under no circumstances you should change the headings in the first line. All other lines may be changed by you.
- Do not add new columns.
- 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 which are not relevant. These columns are still there, but are no longer displayed.
- You cannot delete data records using export files. You must always do this in the administration area of your shop.
How do I edit CSV files with Calc (OpenOffice)?
1. Export a CSV file in the administration area of your site and save it on your computer. For more information about this, read Import and export of data – overview.
2. Create a backup copy of the file.
3. Start OpenOffice Calc or open a new OpenOffice spreadsheet.
4. Open the exported file in Calc.
5. Now you need to make some adjustments to the formatting of the file.
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.
This value should remain "1".
Select Separated by and then the separator that you chose when exporting the file in your shop’s administration area. For Text delimiter, the quotation marks should remain selected.
6. Choose Ok.
7. The file has been opened. You can now edit it.
8. When you are done, save the file.
The file has been saved on your computer and you can import it back in your shop. For more information on this, read Import of data.
How do I add a new data record?
Importing and exporting files is especially useful to add many new data records without having to enter each of them in your shop’s administration area.
Each data record (e.g. each product) is displayed in an own line. The best way to add a new record is to copy one of the existing lines and to add it at the end of the table. If possible, choose a data record that is similar to the one you want to add. Then you already have 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 to check if a data record already exists or not.
What do I need to know about editing certain kinds of data?
- Please note that during the import of products the stock level is also imported. In order to avoid that, simply delete that column from the file.
- For adding new categories, the following columns are mandatory:
- If there is no entry in the column "[Parent]", the elements can be found directly below the start page.
Category product assignment
- This file only contains information on which products are assigned to which categories.
- The products and categories mentioned in this file need to exist in your shop. If you add new products or categories within the file and then import it, those are not added.
- This file not only includes the product types, but also the attributes and their values.
- In the columns "[BillingAddress.Country]" and "[ShippingAddress.Country]" the countries are defined by country codes (e.g. DE for Germany and GB for the UK). You can find a list of all country codes here.