Importing Products Using a Spreadsheet (CSV)

Follow

You can import products to your store with a .csv file. This knowledge base article describes the steps to follow to prepare the .csv file and then import it.

Preparing the Spreadsheet (CSV) file

To import products using Tools > Data Import > Product you need to create a spreadsheet.

The contents of this sheet are based on column headers that are accepted by Spark Pay online stores.

Browse to Tools > Data Import > Product:



On this screen you will see a link: click to download the Sample File (used as a reference for uploading). Click the link to download a CSV file that you can use to import your product data.

When you open the CSV file it will contain a single row. This row is explained under View Field Definitions (see image below).

Field Definitions will let you know what data should be placed under which column and in what format.

Note:

  • Some columns have a character limit notably ItemName (100 chars), ShortDescription (100 chars), CustomMetaKeywords (250 chars), CustomMetaDescription (250 chars), etc.
  • If you are importing product data for the first time, it is helpful to setup a few products manually and export the data to familiarize yourself with the data formatting in the Spark Pay online store . Please review the knowledge base article 'How Do I Add Products to My Store' to review how you can manually add products to your store.

The sample file you download will contain lots of column headers that you may not need. You may safely ignore or delete those columns to make editing easier.

You can do your editing in an XL sheet (.XLS, or .XLSX) and do a Save As to CSV when you are finished.

For example: Take a look at the following XL sheet:

This sheet only contains a few columns as an example:

  • ItemNumber
    Typically the SKU number for the product. In case the product does not have one you can generate your own ItemNumber.
  • ItemName
    The name of the product as seen by your customers. This is a required field when adding a new product.
  • CategoryList
    A list of categories under which the product is placed. Multiple category branches are separated using the | symbol, for e.g.:
    Bags > Backpack | Accessories > Bags | Gifts. This is a required field when adding a new product. If a category/category path is defined here that does not already exist, it will be created on validation during Import.
  • ProductStatus
    The default status for product. (Product Statuses are defined under Catalog > Product Statuses). A product status must be created before it can be used.
  • Bullets
    A description of the product. You can use simple HTML formatting commands like <b>, <ul>, <li>, <p>, etc. This is a required field when adding a new product.
  • BasePrice
    The price of the product as seen and paid by customers (discounts are applied on the base price). This is a required field when adding a new product.
  • Manufacturer
    Name of the product manufacturer. You can edit manufacturers from Catalog > Manufacturers / Brands.
  • ItemPrimaryImageUrl
    Path to the main image associated with the product (for e.g. /images/product.jpg).
  • ItemThumbnailUrl
    Path to the thumbnail image for the product (for e.g. /images/product_th.jpg).
  • ItemImages
    This column is used when you are specifying multiple images for a product. If you take the above two images then you would get a path like this:
    /images/product.jpg~True~~~0~/images/product_th.jpg

    How the ItemImages column works

    Take a look at the following screen from the Photos tab in the Product Editor.


    The ItemImages column works as follows:
    <1. image name with path>~<2. is primary (TRUE or FALSE)>~<3. alternate text>~<4. description/caption text>~<5. sort order>~<6. thumbnail image with path>~<7. zoomify path>

    So following the parameters in the above image results in:
    /images/WPS50829.jpg~True~AlternateText~ Description~1~/images/WPS50829.jpg~

    Now, if there are more images associated with the same product then the additional images are listed as follows:
    /images/WPS50829B.jpg~True~AlternateText~ Description~2~/images/WPS50829-2.jpg~
    /images/WPS50829C.jpg~True~AlternateText~ Description~2~/images/WPS50829-3.jpg~
    /images/WPS50829D.jpg~True~AlternateText~ Description~2~/images/WPS50829-4.jpg~

    In the ItemImages column all 4 rows are combined (no line breaks) and separated using the | (pipe) symbol as follows:
    /images/WPS50829.jpg~True~AlternateText~Description~1~//images/WPS50829B.jpg~|

    You do not need to populate all the parameters. So instead of:
    /images/WPS50829.jpg~True~AlternateText~Description~1~//images/WPS50829B.jpg~

    You may also use:
    /images/WPS50829.jpg~True~~~1~/images/WPS50829.jpg~

    and
    /images/WPS50829.jpg~True~~~1~/images/WPS50829.jpg~|/images/WPS50829-2.jpg~False~~~2~/images/WPS50829-3.jpg~

    Note:

    • The Is Primary flag can have a value of True or False
    • For products with multile images only 1 (one) image can have the Is Primary flag set to True
    • The Sort Order for the image with Is Primary set to True is ignored
    • The Sort Order is optional. You may leave it at 0 for all your images
    • Multiple product images in the ItemImages column are separated using the | (pipe) symbol
    • When the ItemImages column is populated, data in the other image columns is ignored
  • CustomPageTitle
    The page title that you want to use. Appears as the title in the browser window. Useful for SEO purposes.
  • Hide
    A boolean field that can contain TRUE or FALSE values.
    Tip:
    When importing products you can set this value to TRUE so you can test imported products before making them visible to your customers.

Note: The ItemId field is the Spark Pay online stores internal product number. It is autogenerated and should not be included when adding new products. It is useful for identifying products in the database and can be used for updating records. The ItemId is different from the ItemNumber.

When you are finished making changes to the XL sheet you can save it as .CSV file.

csv.jpg

Now that you have a CSV file it is time to upload it.

Importing data using the CSV file

Browse to Tools > Data Import > Product Import:

Click the folder icon that says "Import From" under Import File

 

When the File Explorer opens, go to the Upload Files tab and follow these steps:

After uploading the file you will see it under "Import File" under "Insert From". Click "Next".




When you click on Click to Load the next screen will show a list of fields as follows:

  • On the left you will see Your Column. These are the headers as you have used in the CSV file you just uploaded.
  • On the right is Our Column. These are the headers that Spark Pay online stores is expecting from your file.

If you have used the sample data file, the two columns should match perfectly. If you have used your own headers, you can map the fields by selecting the fields from the drop down that your fields should map to. Click on the "Next" button in the upper right hand corner to continue.





Note: When importing data using CSV files you do not need to use all the headers. When a file is created with missing columns there is simply a no-match scenario on this screen.

You will have the option of Validating your data. This will show any errors that you will encounter before uploading so that you can fix these issues before proceeding. Once you have validated your data, you are ready to Import.

Click "Import" 

When the data upload is completed successfully you will see the following dialog box:

In the above image:

    • 3 products processed
      Means there were 3 entries (rows of data) in the CSV file that was used. The number will change depending on how many records you imported.
    • 3 items successfully uploaded
      Means 3 entries were successfully entered/updated to your store database.
    • 0 items not uploaded
      Means 0 entries were not entered/updated to your store database. In case there were problems with data that could not be checked by the Validate link they will show up here. This should not happen if all the data was correct.
Have more questions? Submit a request

Comments