Adding and editing data by using CSV import

By using CSV import, you can modify the already existing data as well as add new data to your online store. CSV import is usually used to transfer data between various ecommerce systems.

There are two ways of creating a CSV import file:

manually, e.g. with spreadsheet software such as LibreOffice
by exporting data from your store to a CSV file and modifying it accordingly. Afterwards, the file can be imported back to your online store.

Manually creating the import file

When creating a new CSV file, check the identifiers of the data you'd like to add or edit. To do so, see the reference table of CSV identifiers.

When editing a CSV file, you may want to use spreadsheet software. This type of software usually automatically adds field separators and, where necessary, quotation marks to cells.

To edit the already existing data with CSV, check the unique ID numbers of the contents you'd like to edit (e.g. products and campaigns).

When importing new data, there is no need to add any ID numbers to the CSV file as the system creates them automatically for all new contents.

To check a content's ID number:

In the admin panel, open the content (e.g. product or campaign) you'd like to edit by using CSV.
In the browser's address bar, look for the value of the id variable.
In the reference table of CSV identifiers, check the identifier corresponding to the content's ID number (e.g. ProductID in case of products).

If you haven't created a new CSV file already, do it now. In spreadsheet software, default tables can be saved in the CSV format.

In the CSV table, create a separate column for the ID numbers with the corresponding identifier in the heading row:
```
ProductID;   # The first row contains the CSV identifiers of the content's ID number
1;                 # Under the heading row, in the same column
2;                 # provide the ID numbers of the contents to be edited in separate rows.
3;
4;
```
In the example below, the rows starting with the # character are comments, and they don't have to be included in the actual CSV file.

Create a separate column for all types of data you'd like to edit in the import. For the necessary identifiers, see the reference table.

ProductID;             ProductName;                               ProductPrice;
1;                           Glove;                                            5.95;
2;                           Sock;                                             14.95;
3;                           Smartphone;                                  249.95;
4;                           "Bicycle; every cool kid's ride";    149.95;    # To use the field separator inside a cell
                                                                  # surround the cell with quotation marks.

Only one type of data can be edited or added in a single CSV file. So, it is not possible to edit e.g. product and customer data during the same import.

Creating the import file by using CSV export (recommended when using import to edit contents)

Especially when editing already existing online store contents, you may want to create the file to be imported by means of CSV export.

CSV export enables you to download a CSV file from your online store with all the desired data ready for editing. In such a case, you won't need to check contents' identifiers or ID numbers, as all this information will be available in the exported CSV file.

In your online store's admin panel, go to Reports > CSV Export. Select the data type whose identifiers you'd like to check. After selection, click Preview.
You'll see a list of the available data for the selected data type.
Select the desired data, and click Preview once again.
The preview of the selected data opens.
If the preview contains all the desired data, download the CSV file to your computer by clicking Download.
Before downloading the file you can still customize the export's content by selecting other data for export.
Open the downloaded file in spreadsheet software of your choice (e.g. LibreOffice), and make all necessary changes to the included data.
Save the file before importing it back to your online store.

Importing the CSV file to your online store

After checking the identifiers of the data you'd like to add or edit and creating the file for import, you can start the import.

First, from the version drop-down menu at the top, select the desired store version.
The file will be imported to the store version selected in the admin panel. To import contents to multiple store versions, you'll have to select the desired store version before each import.
In the admin panel, go to Reports > CSV Import.
In the Type list, select the type of the edited data.
If you used an exported CSV file as the basis for your import, select the same data type as during the export.
Select the field separator used in the file (comma or semicolon).
Make sure that you have consistently used only one type of field separator in your CSV file.
If you need to use the character used as the field separator inside a cell, surround the cell with quotation marks (").
Select the CSV file that contains the edited or new contents.
Click Send.
A preview of the uploaded CSV file opens.
At this stage, you can still make adjustments to the import.
You can select which rows should be imported: all rows or only the new rows. You can also choose to update only the already existing data.
If MyCashflow doesn't recognize a column's identifier, the column won't be displayed in the preview, and its data won't be saved in the store.
Save the data to be imported by clicking Continue.
A summary of the CSV import opens.

Examples on creating CSV import files

If you'd like to create the CSV import file yourself, see the examples below that illustrate how different types of data can be processed with CSV.

See also the reference table of CSV identifiers, which explains the functioning of the most important identifiers used for import and lists their allowed values.

Editing product data

ProductID;    ProductPrice;   ProductBrandID;
1;                   23.95;               2;
2;                   12.95;               2;
3;                   11.95;               2;
4;                   54.95;               2;
5;                   16.95;               2;

The example illustrates how to update the prices of 5 products as well as how to change the brand of all products.

ProductID: each processed product is listed in a separate row and identified by means of the product's unique ProductID identifier. All data indicated in the row applies to the product whose ID has been indicated in the first column.

ProductPrice: in the second column, a new price is assigned to the product. You may want to use dot as the decimal separator instead of comma, which may also be used as the field separator in the CSV file.

ProductBrandID: in the third column, the products are assigned a new brand. This is done by indicating the unique ProductBrandID identifier of the desired brand in the column.

Adding new products

ProductName;   ProductVisibility;   ProductPrice;   ProductTaxRate;   ProductWeight;   ProductParcelType;   ProductCategoryNames;
Mittens;             1;                            14.95;              24;                         0.2;                      LETTER;              /Clothes/Gloves;
Winter mittens;  1;                            15.95;              24;                         0.2;                      LETTER;              /Clothes/Gloves;
Fabric gloves;    1;                            5.95;                24;                         0.1;                      LETTER;              /Clothes/Gloves

The sample file is used to add to the online store three new products with prices, weights and package sizes set. The products are also set to display in the same product category. Additionally, the products are set to become visible right after the import. Note that when adding new products, you shouldn't use the ProductID identifier as it is created for new products automatically.

ProductName: in the first column, the product is given a name.

ProductVisibility: the product is set to become visible immediately by setting the identifier's value to 1. In such a case, the product will be visible in the store's default version.

ProductPrice & ProductTaxRate: the product is given the price and the tax rate. The sample online store operates in Finland, so the most usual tax rate of 24% is applied to the price.

ProductWeight: products cannot be shipped until their weight has been set.

ProductParcelType: products cannot be shipped until their package size has been set. For package sizes' allowed values, see the CSV identifier table.

ProductCategoryNames: the products are also added to the product category of Gloves, which is a subcategory of Clothes. Product categories are indicated under the identifier as a path. Make sure that the product category paths are spelled correctly – otherwise a new product category will be automatically added to the store with the incorrectly spelled value as its name.

To create a new product category during import, use the ProductCategoryNamesAdd identifier. In such a case, indicate the product categories as paths and separate them with commas.

Creating product variations of the already existing products

This example illustrates how to create 5 new variations of one product. Products and their variations cannot be created during the same import. First, you must add main products to your online store, and, during the next import, you can create their variations.

The import file used for creating new product variations

ProductID;    VariationName;    VariationPrice;    VariationCode;    ProductVisibility;
1;            Green;           12.95;             1146;             1;
1;            Blue;          13.95;             1147;             1;
1;            Red;         14.95;             1148;             1;
1;            Yellow;        15.95;             1149;             1;
1;            Crimson;         14.95;             1150;             1;

ProductID: this column is used for defining the product to which new variations should be added. In the example, five new color variations are added to the product whose ProductID is 1.

VariationName and VariationPrice: new variations are assigned names and prices, so that their sales can start right after the import.

VariationCode: each variation is assigned a separate product code for the sake of stock management and potential integrations with financial management systems.

ProductVisibility: finally, the main product itself is set as visible in the online store's default version.

Attaching product images to product variations

CSV import enables you to attach images to multiple variations as well as to provide their captions in one go. The images to be attached can be located in your online store's file directory's /tuotekuvat folder or at an external URL address.

You can only provide captions for images located in the file directory.

Below you can see a working sample file that adds

images without captions to the first three variations from the store's file directory
a captioned image to the fourth variation from the store's file directory and
an image without caption to the fifth variation from an external address.

VariationID;  VariationImage;   VariationAddImageFromURL;                   VariationImageWithCaption
46;           nv_musta-23.png;  ;
47;           nv_lime-23.png;   ;
48;           nv_harmaa-23.png; ;
49;           ;                 ;                                           nv_luonnonvalkoinen-23.png:Valkea paita
50;           ;                 https://www.example.com/nv_oranssi-23.png;

When you attach product images to variations in this way, the images will be displayed on the product page provided that the variation images' Interface tags have been added to the product template (themes/shop/THEME/product.html).

If necessary, see detailed instructions on how to implement variation images in the theme.

If you're not familiar with modifying your store theme's HTML/CSS/JavaScript files, contact the designer of your online store's theme or our customer service.

Creating product categories and adding products to product categories

This example illustrates how to create new product categories in the online store by using CSV import and add the already existing products to product categories.

Creating product categories by using CSV import

CategoryName;   CategoryParentPath; CategoryVisibility; CategoryType;
Tools;       /;                  1;                  etusivu;
Hammers; /Tools; 1; tuotelista;
Screwdrivers; /Tools; 1; tuotelista;

The sample file above illustrates how to create three product categories. The first – Tools – is set to be displayed on the top level in the product category menu, whereas the two other categories are set to be its subcategories by entering the /Tools value in the field CategoryParentPath.

In the CategoryVisibility field, all the product categories are set as visible in the active version.

Finally, in the CategoryType field, the first product category's display type is set to display window (even though the value's name in the CSV tools is etusivu) and the other categories' to tuotelista (product list).

In the next file, you'll see how to add products to the newly created product categories.

Adding products to product categories

ProductID; ProductCategoryNames;
1; /Tools/Hammers;
2; /Tools/Hammers|/Tools/Screwdrivers;
3; /Tools;

The first product is set to display only in the Hammers product category.

The second product is set to display both among hammers, as well as screwdrivers.

The last product is set to display in the top-level category of Tools.

Adding products to campaigns

This example illustrates how to add products to campaigns. Both the campaigns as well as the products already exist in the online store, so the CSV import's only task is to attach the products to the campaign.

When adding products to campaigns with CSV import, select Campaign products as the CSV import type.

The sample below illustrates how products can be added to campaigns using CSV import.

CampaignID;       ProductID;
1;                         3;
1;                         45;
1;                         2;
2;                         3;
2;                         2;

In the above sample, products are added to two different campaigns (CampaignID 1 and 2). To add a product to a campaign, simply indicate the desired product's ProductID and campaign's CampaignID in the same row. Note that a product can be added to multiple campaigns in a single import (see products 2 and 3 in the sample file).

In the example, the products are only added to campaigns. You will still have to define their pricing later (this is done manually in the admin panel). Alternatively, you can indicate the price during the import in the CampaignProductPrice column.

Adding and editing product attributes

You can add new product attributes to your online store according to the following example:

AttributeCode;  AttributeLabel-fi;  AttributeLabel-se;  AttributeLabel-ee;  AttributeLabel-en
length;         Pituus;             Längd;              Pikkus;             Length
width;          Leveys;             Bredd;              Laius;              Width

As opposed to other types of content, product attributes' translations into different languages can be imported to the online store within a single CSV-import.

By default, product filters created from new attributes are hidden after import. Set filters as visible on the admin panel's Products > Product features page.

The structure of the sample file above can also be used for editing attributes. Use the AttributeCode identifier to select the attribute you'd like to edit. You can delete from the file the translation columns whose contents should remain unchanged (if you leave a column empty, the translations into that language are going to be deleted).

Assigning product features to products and variations

You can use the below file to add and edit the features of a product:

ProductID;    ProductFeature-_color;    ProductFeature-_size
1;            red;                      s
2;            blue|red;              s|m

The above example adds red color and S size to product 1. Product 2 is added red and blue colors as well as S and M sizes.

Here's how to assign product features to variations:

VariationID;    ProductFeature-_color;    ProductFeature-_size
13;             red;                      s
14;             blue;                     s

Note that the variation ID is unique throughout the entire online store – no product's variation has the same ID as another product's variation.

You can import product features for products and variations in one file and one import. In this case, leave ProductID blank in the rows where you are handling variations and vice versa:

ProductID;    VariationID;    ProductFeature-_color;    ProductFeature-_size
1;            ;               red;                      s
2;            ;               blue;                     s
;             13;             red;                      s
;             14;             blue;                     s

Updating stock balances

In this example, the stock balances of three products are updated by using CSV import. At the same time, stock management is enabled for the products.

Updating stock balances by using CSV import

ProductID; ProductStock; ProductStockBalance; ProductStockBalanceChange;
1;         1;            2;
2;         1;            23;
3;         1;            ;                    20;

The stock balances of the products 1 and 2 are set to 2 and 23, respectively. In case of the product 3, 20 pieces are added to the stock, which means that its final stock balance will be the sum of the initial stock balance and 20.

Adding customer accounts

In this example, new customer accounts are created in the online store.

Adding customer accounts to the online store

CustomerLoginAllowed; CustomerEmail;         CustomerFirstName; CustomerLastName; CustomerStreetAddress; CustomerPostalCode; CustomerPostOffice; CustomerCountry; CustomerGroupIDs;
1;                    customer@customer.com;         Alex;           Customer;           Street 1;         33100;          Helsinki; Finland;           2|4;
1;                    second@customer.com;            Anthony;     Customer;           Street 2;         33100;           Helsinki; Finland;           2|4;
1;                    third@customer.com;                 Adam;         Customer;           Street 3;         33100;          Helsinki; Finland;           2|4;

In the example, three new customers are added to the online store. Note that customer passwords cannot be set by means of a CSV import, and the customers will have to use the Forgot your password? functionality after the import in order to set a new password to their account.

CustomerLoginAllowed: the value 1 means that the customer can log in to the customer account in the online store. The value 0 disables this function.

CustomerEmail: customer's email

CustomerFirstName: first name

CustomerLastName: surname

CustomerStreetAddress: street address

CustomerPostalCode: postal code

CustomerPostOffice: post office

CustomerCountry: customer's country

CustomerGroupIDs: in this example, all three customers are added to two product categories with the IDs 2 and 4.

USER GUIDE