Retailers who order completely new ranges of products in on a regular basis (e.g. clothing & fashion retailers) may find the Import Manager to be a much quicker way to get their product data into the system than entering it ‘by hand’, particularly if your suppliers already have spreadsheets of product data they can provide you with.

(Bear in mind that if you’re importing product data from a spreadsheet, the system will still need to be given the same information that you’d normally give it when entering product data ‘by hand’. So, you’ll need to put a column into your spreadsheet to represent each of the ‘required’ fields on the product creating pages. Any supplier-provided spreadsheets will still need some additions before they’re ready for Cybertill.)

(There’ll be more information on exactly what columns are required as a bare minimum later in this manual.)

It can also be used to import details of your customers in from a spreadsheet.

At the time of writing, Data Import Manager is a module that is not included as part of the standard Cybertill package and it needs to be purchased separately. If you’re interested in trying it out, please contact your Account Manager for price and availability information.

About this manual

This manual presumes that the user already has some experience of creating products ‘by hand’ in Cybertill, of Microsoft Excel (or a similar spreadsheet program), and of basic computer use in general.

At various points, this manual will suggest that you use certain ‘example’ import files that can be found on our filestore.

As most users will wish to use this function to import details of new products into the system, this manual will initially focus on setting up that type of import job. (Later on in the manual, instruction will be given on how to amend the details of existing products via an import.)

We will begin with instruction on how to set up an import for products with no styles (e.g. no sizes or colours).

After that, we will deal with importing products that have styles.

The Import Manager is a complicated module, and there isn’t one single ‘correct’ way to use it. You may find that once you’ve got to grips with it, you’re able to successfully import datafiles with quite different layouts to the ones described in this manual.

If that happens… that’s absolutely fine by us! If it’s working for you, great; that it works is much more important than exactly how it works.

Nevertheless, we would recommend that if you’re new to the Import Manager, you should begin by setting up an import exactly as instructed in this manual. The imports described here are tried and tested, and if you set them up exactly as instructed here, they should be ‘safe’ and produce consistently good results.

Also, even if all of the products you sell come in different styles (e.g. sizes / colours), we’d suggest you begin by importing some ‘test’ products without styles, just so you get a feel for how the import routines work. (Walk before you can run.)

We would not recommend you attempt to learn how to use Data Import Manager through trial and error, i.e. without any reference to this manual or any training. An incorrect or ‘careless’ import can potentially add a great deal of unwanted ‘junk’ data to your system, which can be extremely difficult to sort out and maybe even have permanent consequences!

Accessing the Data Import Manager/Module

Once you’ve purchased the Import Manager, you can find it near the bottom of the Admin menu:

Click that to open it up.

You’ll be taken to a screen listing all the import jobs that have been set up on your system to date. It’ll look something like this:

(Of course, if this is the first time you’ve ever been into this screen, it might look a lot emptier than in the above screenshot!)

To start setting up an import, click the New link at the bottom.

Creating a new import job

What is a “New Import Job”?

Firstly, let’s make something clear: You don’t have to create a “new import job” here every single time you want to load in some new product data.

Think of “import jobs” as being import routines or a set of importing rules, rather than individual imports. You’ll be able to run the same “import job” over and over again, for different lots of data.

For example, if you wish to use the Import Manager to add new products to your system regularly, you should really create just one import job called something generic like “New Products”.

You’ll see as we go along that for each import job, you’re able to specify what type of data should be imported, how many columns the spreadsheet you’re importing should contain, what those columns represent and so forth. You’ll then be able to run that import job over and over again for different lots of data– as long as the spreadsheets you’re importing contain the same columns, in the same order.

Creating a new import job

Once you’ve clicked the New link, the screen will change as follows:

You’ll need to fill in most of these fields accordingly. Here’s an explanation of each:

Name*

Name your import job here. (As previously mentioned, something generic like “New Products” might be appropriate.)

Description

If you want to add an extended description of what this import job does, enter it here. It’ll then be visible to anyone viewing the Import Manager screens. (This field isn’t compulsory, so you can leave it blank.)

Process Type

Here, you’re supposed to choose what type of import you wish to set up. Your choices are:

Product

Choose this option if you want this job to be for either importing new product details, or for adjusting the details of existing products.

(If this is your first time in the Import Manager, choose this.)

Customer

Choose this option if you want this job to be for importing either details of new customers, or for importing changes to existing customers.

Supplier

This option is for importing details of new suppliers into your system.

Process Method

If you’re going to be importing product data, then you’ll have to choose here whether this import job is for uploading new products or for editing existing ones. You’ve got two choices:

Update Existing Data Only

Add New Data Only

Add and Update Data

Simply choose the appropriate one. (If this is your first time in the Import Manager, choose “Add New Data Only”; let’s focus on adding new products for now, rather than editing existing ones.)

Source Filename

You’ll need to type a filename here for all of the datafiles you’re going to be importing in through this job.

This is a kind of safety measure. It should help ensure that you don’t accidentally upload the wrong data into the system.

For example, if you enter a filename of productimport.csv into this cell, then when you come to run this import, the system will only allow you to import files that are called productimport.csv. (So, were you to accidentally try to import a file called priceupdate.csv, the system simply wouldn’t let you.)

(If this is your first time in the Import Manager, enter productimport.csv for now.)

Note that the system is only capable of importing spreadsheet files saved in the CSV format. It will not accept Excel workbooks or worksheets.

(It is possible to save an Excel worksheet as a CSV spreadsheet.

See the FAQs section at the end if you’re not sure how to do it.)

Source format

You have three choices here:

csvc

csvt

xml

If this is your first time using the Import Manager, select csvc here.

Use Headers

Here, you’ll need to say whether the spreadsheet files you wish to upload have headers at the top or not (i.e. whether the first row of your spreadsheet file contains column headers, instead of actual data).

If this is your first run through of the Import Manager, we’d recommend that you tick this box. (It’s slightly easier to set up an import job for spreadsheets with column headers.)

Once you’ve filled in all of the above, click the Save button at the bottom.

You should now see a New Job Saved message at the top of the page:

Now click on the Back to list link at the bottom of the screen to go back a page. You should see a new entry on the list of import jobs, looking something like this:

Should you wish to edit any of the details you’ve just entered, click the Edit Job link to the right. (It’ll take you into exactly the same page as before.)

Your next step is to give the system some information about what sort of data the files you’re going to import into it will contain. You’ll need to show it a sample datafile.

Uploading a ‘sample’ datafile

Why do we need to show the system a ‘sample’ datafile?

The system needs to know roughly what it should be getting!

You need to let it know how many columns there will be in your datafiles, and also what kind of data each column will contain (i.e. numbers or text).

Think of this as another safety feature, a mechanism to stop any ‘duff’ data you import from getting into your system and clogging it up.

Imagine that sometime in the future, you try to upload a spreadsheet that somehow got a little muddled up; perhaps some product names have somehow got into the ‘price’ column, and vice versa.

Fortunately, this safety feature should make such cases not a problem (or at least, nowhere near as big a problem as it could be). You’ll see that in this next section, you can set rules for the system to follow along the lines of “Column 3 must contain numbers with decimal places”, or “Column 7 must contain a round number with no decimal places”.

If you set rules like this, your system will be able to spot immediately when such mix-ups have occurred and warn you about it.

(Jumping forward a little: Should this happen to you, your system will ask you if you want to carry on with the import regardless. If you do, any “problem” rows in your spreadsheet will be ignored, but all the ‘good’ rows will be imported like normal. There’ll be more info on this kind of thing later.)

I don’t have a ‘sample’ datafile prepared!

Not to worry – there’s one in Cybertill’s filestore you can use for the moment.

If you go to the filestore and look in the same folder you obtained this manual from, you should be able to find a file called productimport.csv somewhere in the list. Save that to your hard drive, and use that for the time being.

(Feel free to take a look at it in Excel. You’ll probably notice that many of the columns in it match the fields you have to fill in when creating a new product from Admin \ Products & Categories.)

How to upload a ‘sample’ datafile and set rules about its contents

Go to Admin \ Data Import Manager (if you’re not there already) and click the Edit Source link to the right of your job.

If it’s the first time you’ve ever done it, you’ll be taken to a rather short, empty page:

Note the [Browse…] button at the bottom. Click that. Another window will open:

In this window, find your productimport.csv sample datafile and double-click on it. The window will disappear, and you’ll now see the file’s path and name appear in the Select sample source datafile: box back in Cybertill, like so:

Click the [Upload Datafile] button at the right to continue.

The screen will now change to look something like this:

The system is now asking you what type of data every column of your datafile should contain.

For each row here, you’ll need to choose something from the Select drop-down boxes on the right.

You’ve got a number of choices here, but let’s just focus on the three that you’re most likely to use by far for now:

String

Choose this if the column in question is going to contain letters instead of, or as well as, numbers. (For example, “Product Name”. “Item Reference” or “Brand Name” columns should be set as strings, as all three usually contain letters.)

Integer – Without Leading Zeros

An ‘integer’ is simply a round number, i.e. a number with no decimal places after it. Choose this option if the column in question should always contain round numbers only, i.e. no decimal places, and no letters. (For example, many retailers find it helpful to set the “Box qty” or “Current Stock Level” columns to ‘integer’.)

Decimal – Without Leading Zeros

Choose this if the column in question should always contain decimal numbers only (any “Price” columns, for example).

The other options are of limited use to most users, so don’t worry about them for now. (They may be described in future revisions of this manual.)

Readers using the productimport.csv file from our filestore are recommended to fill this page in as depicted below:

This setup should help to ensure optimum import quality.

Once you’ve filled in all of the drop-downs, click the [Save Source Format] button at the bottom left:

You should see a “success” message at the top of the screen:

Once your columns are all set, the next step is to map the data.

Mapping the data from your sample datafile

What does ‘mapping’ mean here?

Well, the system now knows what kind of data each column in your datafile contains (letters, numbers etc).

However, it doesn’t yet know what each column represents.

For example: Although it might seem obvious to you and me, your system doesn’t yet have the slightest clue that the contents of the “Product Name” column represent the names of all your new products. All it knows so far is that ‘column X should contain strings’ (i.e. letters and/or numbers are allowed in that column).

The same principle applies for all the “Price” columns too. You haven’t yet done anything to tie the “Store Price” column in your spreadsheet to Cybertill’s Store Price field. If you’ve set things up correctly so far, Cybertill should know by now that your “Price” column ought to contain numbers to two decimal places. But, it doesn’t yet know what it’s supposed to do with those numbers.

Simply entering column headers of “Name” or “Price” into your spreadsheet isn’t enough. The system has not been designed to automatically work out which column is supposed to be which bit of product info. (Any such ‘automated mapping’ processes would have a relatively high margin for error, and when you’re importing potentially thousands of products, you want that margin for error to be as small as possible!)

So, you’ll need to tell the system exactly what piece of product data each column in your spreadsheet is supposed to be. It already knows whether the columns should contain letters or numbers, but that’s all; you need to give it some further guidance.

The ‘mapping’ process

Go back into Admin \ Data Import Manager if you’re not there already, and click the Map Data link at the right of your import job:

Click that, and you’ll be taken to a new screen that’ll look something like this:

In the Target Field Data column at the left of the screen, you’ve got a list of all sorts of different data fields – some of which you should recognize from the product editing screens from Admin \ Products & Categories. (There’ll be plenty that you won’t recognize too.)

Note that in the Source Field Data column, you’ve got a separate drop-down box in each row.

Were you to click into any of those boxes, you’d see a list of all the column headers from your sample datafile.

The idea here is that you should work your way down through the Target Field Data column on the left, looking for all the bits of data you want to import.

Whenever you find one, click into its drop-down box at the right, and choose the appropriate column from your sample datafile.

(So, for example, if you’ve got a column in your spreadsheet entitled “PRODUCT NAME”, you should probably find the Product Name row in this page, then choose the “PRODUCT NAME” option in the appropriate drop-down. Repeat until you’ve ‘mapped’ all the columns to the appropriate places, then click Save Map at the bottom when you’re done.)

Can I ‘map’ the same column to more than one field?

Yes, that’s quite possible – and in some circumstances, very useful too.

For example, let’s say that your import datafile only contains one column for selling prices, entitled simply “PRICE”.

Now, let’s say you want your RRP, Store Price and Web Price to be exactly the same.

You could map the “PRICE” column to all three RRP, Store and Web fields.

What do all the rows represent?

What rows appear here will vary, depending on the Process Type you chose way back when creating the new import job (see page 18).

This section will go into detail about each of the rows available for imports of type “Products”. The other import types will be tackled in a future revision of this manual.

There will be some rows in here that you’ll have never heard of before (and probably never will outside of this page). They’ll only be of use to those with an extremely in-depth knowledge of the system – and even then, only in certain very specialized circumstances.

Consequently, a fair proportion of the rows on this page should be left well alone, unless you know exactly what you’re doing.

Here is a breakdown of every row on the Map Data page, along with instruction on what it represents, wherever it is relevant. (All rows that are for ‘experts only’ will be marked accordingly.)

All the rows which must be mapped to a column in your datafile in order for a new product import to work effectively (or at all, in some cases!) will be clearly indicated with a (COMPULSORY!) message. (You will probably notice that many of the indicated fields are compulsory when you’re entering product details ‘by hand’ in Admin

\ Products and Categories too.)

Have a read through and map them as you see fit. (If this is all a bit much for you, skip past this table; there’s a section after it that demonstrates exactly how you should fill in this screen if you’re using the example import template.)

Name	Explanation
Cybertill item record ID	Experts only.
Item Reference (Unique Key)	This row represents item reference codes (hereafter referred to as “itemRefs”). If your spreadsheet contains a column for these, map it to here. When importing new products, it is not compulsory to have a column for itemRefs. If there is no such column, or if there is a column but it contains no data, the system will randomly generate an itemRef for each item.
Primary Barcode (Unique)	This row represents barcodes. If your spreadsheet contains a column of barcodes, map that to here. When importing new products, it is not compulsory to have a column for barcodes. If there is no such column, the system will randomly generate a barcode for each item as is appropriate. Note that if your spreadsheet does contain a column for barcodes, but you do not enter any data in it, the import will not work. (So, if your spreadsheet contains a “Barcode” column, you must use it.)
Manufacturer Number	This row represents Manufacturer Refs/ Numbers. If your supplier has a reference number for the item in question, and if you have a column for it on your spreadsheet, map it to here. It is not compulsory to enter one of these when importing new products. If there is no such column in your spreadsheet, or if there is a column but it contains no data, the system will randomly generate a manufacturer reference.
Item Name (Style Option) (COMPULSORY!)	This field is for the item name (not the product name, which is a completely separate field; there’s a subtle difference between them). Unless you know exactly what you’re doing, users are recommended to map any “name” columns to the Product Name row further down the page, and to leave this row alone.
Alternative Barcode	If your items have more than one barcode number, you can import “secondary” or “alternate” barcodes by creating a column for the “alternates” and mapping it to here. (If there’s more than one alternate barcode you want to import, enter them all into this one column, and separate them with the pipe character - \| )
Coverage Area Sq Meter	Experts only.
Commission Band ID	Experts only.
Default Item Price RRP (RECOMMENDED)	Recommended retail price. Not compulsory; if no column is included for it, it will be set to £0.00
Default Item Price Store (RECOMMENDED)	Store price (i.e. how much you sell it for to retail customers). Not compulsory; if no column is included for it, it will be set to £0.00
Default Item Price Staff (RECOMMENDED)	Staff price. Not compulsory; if no column is included for it, it will be set to £0.00
Default Item Price Web (RECOMMENDED)	Web price. Not compulsory; if no column is included for it, it will be set to £0.00
Default Item Price Trade (RECOMMENDED)	Trade price. (This will only be of use to retailers who do trade sales, and it should be entered ex-VAT.) Not compulsory; if no column is included for it, it will be set to £0.00
Item VAT Rate (COMPULSORY!)	A field for recording the VAT Code of an item. (This field is subtly different to the “Product VAT Rate” field from earlier.) To find out what VAT Codes are set up in your system, go to Admin \ VAT Setup, and take a look in the Code column.
Item Discontinued	Experts only.
Item Available to sell	Experts only.
Purchasing Box Quantity	This is the same “purchase box quantity” as can be found in the product creation page. (If this row is not mapped, or is mapped to a column that is empty, this will be set to ‘1’ for everything.)
Sales Box Quantity	This is the same “sales box quantity” as can be found in the product creation page. (If this row is not mapped, or is mapped to a column that is empty, this will be set to ‘1’ for everything.)
Location Item Price RRP	If you wanted to import a branchspecific RRP against a product (i.e. if one of your stores had different RRPs for its products than all your other stores), map it to here.
Location Item Price Store	Same as above, with store prices.
Location Item Price Staff	Same as above, with staff prices.
Location Item Price Trade	Same as above, with trade prices.
Cybertill product record ID (unique)	Experts only.
Source product id/ref	Experts only.
Product Name (COMPULSORY!)	The product’s name should be mapped to this field.
Product Type	Experts only.
Manufacturer Year	This row represents a product’s year of manufacture.
Product Short Description	This is the product’s Short Description.
Product Long Description	This is the product’s Long Description.
Minor / Major (1= Min 2= Min/SN 3=Maj/SN 4=Maj/SN/StockNo)	If there are some products for which you record serial number of each unit you sell, map a column for it to this row, and enter a number between 1 and 4 into that column as is appropriate. (See left for what each of those numbers represents; you should recognize each of the four options from the product creation page.) If this row is not mapped, or is mapped to a column that is empty, all imported products will be set to “Minor / no track”.
Stock Ref	The product’s Stock Ref should be mapped to this row. (“Stock Refs” are simply another reporting filter. The majority of retailers don’t use them.)
Season (RECOMMENDED)	The product’s Season should be mapped to this row. To find out what Stock Refs are available in your system, go to Admin \ Stock Seasons. You must specify whether the appropriate column in your spreadsheet contains season names or abbreviated ref codes here. Make sure you don’t misspell a season in your import datafile. If you try to import a product with an ‘unrecognized’ season ref, it may be permanently added to your system as a new season. If you’re careless, you could fill your system up with a lot of ‘junk’ seasons!
Brand (RECOMMENDED)	The product’s Brand should be mapped to this row. (To find out what brands are available in your system, go to Admin \ Brands.) You must specify whether the appropriate column in your spreadsheet contains season names or abbreviated ref codes here. Make sure you don’t misspell a season in your import datafile. If you try to import a product with an ‘unrecognized’ season ref, it may be permanently added to your system as a new season. If you’re careless, you could fill your system up with a lot of ‘junk’ seasons!
Product VAT Rate (COMPULSORY!)	A field for recording the VAT Code of a product. To find out what VAT Codes are set up in your system, go to Admin \ VAT Setup, and take a look in the Code column.
Product Available to Sell	Experts only.
Allow Discount (1=yes,0=no)	You can specify whether any products you import are ‘discountable’ or not by setting up a column containing “1” or “0” in each cell, then mapping to here. (If this row is not mapped, or is mapped to a column that is empty, this will be set to “1” – meaning “Yes” – for everything.)
Category ID List	Experts only.
Category Ref List	Experts only.
Category Name List (COMPULSORY!)	Here, you should map a column containing the path of the category you want each item to go into. Subcategories should be demarked with a “pipe” character - \|. For example, if you want to import a product into Clothes \ Mens \ Jackets, fill in this column like so: Clothes\|Mens\|Jackets
Supplier list price	Experts only.
Cost (RECOMMENDED)	Enter the cost price for each unit (or box, if they come in boxes) of this item from your supplier, in the supplier’s currency. Not compulsory; if no column is included for it, it will be set to £0.00
Supplier cost reference	Experts only.
Supplier (RECOMMENDED)	To tell the system which suppliers you buy your products from, you must have a column in your spreadsheet for Supplier Ref, and map that to this field. (To see your supplier’s refs, go to the Suppliers menu and check the Ref column.) Note that you can’t add new suppliers at the same time as you import new products. Your suppliers should already be loaded into Cybertill first.
Supplier name	Don’t bother with this one; entering the Supplier Ref (see above) will suffice.
Set as Default Supplier (COMPULSORY!)	For best results, you should map this field to a column in your import that contains the number 1 in every single cell. (Long story short: This will ensure the supplier is marked as the “default supplier” – i.e. the main supplier, the supplier you buy this product from more often than not. It will also ensure your supplier cost prices are saved correctly.)
Stock	Use this field if you want to import item stock levels into your system. (Note that for this to work, you’d need to specify a location; see below.)
Minimal stock level	Use this field if you want to import item minimum stock levels into your system. (Again, you’ll need to specify a location for this to work; see below.)
Maximal stock level	Use this field if you want to import item maximum stock levels into your system. (Again, you’ll need to specify a location for this to work; see below.)
Location	If you’re importing stock levels and/or branch specific prices, you’ll need to specify the location they’re intended for in your spreadsheet, and the appropriate column to here.
Style Types	We’ll come to this row a little later….
Style Values	And this one… (These rows are to do with importing products with styles, e.g. size and colour. For the moment, though, let’s focus on importing products without styles.)

If you can match all the COMPULSORY rows up to a column in your spreadsheet, then you’re nearly ready to go.

Don’t forget to click Save Map at the bottom of the page to record your settings!

For those of you who are using the productimport.csv sample file available from our filestore, you should map the data as follows:

Map all the rows as described above, and you’ll have a very solid import job set up.

Now, at long last, the set up of your import job is finished, and it’s ready to go.

Performing an import

A brief word of warning

If this is the first time you’ve ever tried to import data, we very strongly recommend you start by importing a small file, with no more than two or three rows in it.

There’s good reason for this. If there is anything wrong with either the way you’ve set up your import, or with the data you’re importing, then at the end of it, your new products might not look quite the way you envisioned them.

Consequently, if you jump straight in at the deep end and try to import hundreds of products in one go, there’s a reasonable chance that you’re going to have hundreds of products in your system that haven’t gone in correctly.

There’s no way you can quickly “undo” this, so these cases can be enormously time-consuming and frustrating to resolve.

Whenever you set up any import routines, it’s good practice to test it with a very small sample of data first, then a not-so-small sample (maybe ten or twenty rows), before trying something larger.

(After all, it will only take a few minutes to import a datafile. You don’t lose much time by cutting and pasting the first few rows into a ‘test’ file and importing that first. It’s a small price to pay for knowing your imports will be ‘safe’.)

Bearing that in mind… let’s try an import out.

Checking the import file

Before you actually import any spreadsheets, it is good practice to ask Cybertill to check it for any obvious problems first. It can then tell you which rows of your spreadsheet it’s having trouble with, so you can then correct those problems (and therefore get your data a little bit closer to perfect) before you actually import it.

Let’s go through the motions of running a test.

Go back to Admin \ Data Import Manager.

To the right of your import job, there should be a Run option.

Click that. You’ll be taken to a page like this:

Note in the above screenshot, the user is being prompted for a file called productimport.csv. In your case, it will prompt you for a file called whatever you specified way back when you were creating the new job (see page 8 for a recap).

Click the [Browse…] button, find the file you want to import and double-click on it. You’ll then see its path and filename appear in that box.

Do not tick the “For Real?” box. You should only tick this box if you want to actually import the data. If you want to test the data, leave it unticked.

Click [Upload Datafile] at the bottom left.

You might have to wait for a few moments while Cybertill checks the file over, depending on how large it is.

Eventually, you’ll see a screen that looks something like this:

This shows the results of Cybertill’s checks. If there are any problems with the file you’re trying to import, you’ll see messages telling you exactly which rows the problems are on.

In the previous example, you can see the following info at the top right:

Records: 4 Created: 3 Updated: 0 Errors: 1

The Records figure tells you how many rows of data Cybertill could find in your spreadsheet.

The Created figure refers to how many new items would be created in your system, were you to actually import this data.

The Updated figure refers to how many already-existing items’ / products’ data would be updated, were you to import this data.

The Errors figure refers to how many rows in your spreadsheet Cybertill has detected a problem with.

So, in this example, the user has tested a spreadsheet with 4 items on it. Cybertill can see no problems with 3 of those items, and it would be quite happy to import those. But, there’s something wrong with 1 of them.

It also says exactly what and where that problem is:

This is telling us that the ItemRef code for the item on third line of the spreadsheet (“AZUMI”) already exists – i.e. there is already an item with that ItemRef on the system. So, Cybertill will not be able to import the item on the third line.

It’s good practice to test your spreadsheet file like this, correct any mistakes that Cybertill finds, then test it again, and repeat the process until you’ve weeded out all the errors.

Once it’s 100% correct – or at least it’s as correct as you can realistically get it – you’ll want to move on to actually performing the import.

Performing the import

To actually do the import:

Go back to Admin \ Data Import Manager, find the import job, and click Run at the right.

On the next screen, click Browse, find the file you want to import, and double-click it.

This time, tick the “For Real?” box to the right (indicated in red above).

Lastly, click Upload Datafile.

You might need to wait a short while for Cybertill to process the data. (The more data, the longer it’ll take.)

After a short while, you should be presented with a “complete” screen that looks something like this:

The yellow row shows some statistics about the import you’ve just performed. The Records, Created, Uploaded and Errors figures mean the same things as they did in the test.

Your products should now be in the system.

If this is the first import you’ve ever done, go back into Admin \ Products and Categories and take a look at them. Make sure the names are correct, the prices are in the right place, the brands, suppliers, seasons etc. are all in right.

If anything does look like it’s gone into the wrong place, delete those ‘erroneous’ products out, then go back and check your mapping and your import file. Make any adjustments you feel necessary, and try again.

Repeat until you’ve got things working perfectly.

A Sample Template

Somewhere in Cybertill’s filestore (probably in the same folder you found this manual), there should be a file called “Data Import Manager Template”.

You can use this to get started. This template contains all of the same columns as the “productimport.csv” file which we’ve been referring to from the start of this manual, as well as some instruction on what each column should contain.

(For the time being, ignore the “Style names” and “Style Values” columns at the far right. We’ll look at styles in the next chapter.)

Try entering some test product data into this template, then save it as a CSV file called “productimport.csv” and importing it into your system.

Importing new products with styles

If you want to import products that come in different styles (e.g. size and colour), you’re going to need a couple of extra columns on your import datafile to account for them.

Also, you’re going to need a separate row in your datafile for every single item (i.e. every single size and colour combination).

Let’s walk through how it works.

Preparing your system and a spreadsheet for importing products with styles

If you’re interested in importing products with styles, chances are that you’ll already be familiar with the Product Styles page in the Admin menu.

(If you’re not, you should probably learn to create products that come in different styles ‘by hand’ first. Try reading the relevant chapters of the manual entitled Getting Started 7 – Product

Creation first.)

The first thing to be aware of: You won’t be able to create any new styles or style values through Data Import Manager. The system will only allow you to import items with styles and style values that already exist in Admin \ Product Styles.

For example:

If you want to import a pair of shoes that comes in sizes 6 to 14, You won’t be able to do so until:

you have created a style called “Shoe Size” (or something to that effect)
within that style, you have created style values for “6”, “7”, “8” etc. all the way up to “14”.

Simplifying it as much as possible: You can’t import products that come in sizes / colours unless you’ve already listed all those sizes and colours into Admin << Product Styles first.

(Another way of putting it: Cybertill won’t let you import a pair of shoes in different sizes unless you create a list of what sizes to use in Admin << Product Styles first.)

Also: You’ll need to ensure your spreadsheet has two columns entitled “Style Types” and “Style Values”. (If you take a look at the sample template from the filestore, you’ll see two such columns at the end…)

Now: If you want to import a product that comes in several sizes and/or colours, for example, to get the best results, in your spreadsheet:

You’ll need to input a separate row for every size / colour of that product,
You’ll need to ensure that each size / colour of the same product has the exact same product name,
You’ll need to specify the sizes / colours in the “Style Types” and “Style Values” columns to the right.

Check the sample template from the filestore again. There’s an example of how it should be done on there, in rows 3 to 6.

Rows 3 to 6 represent different sizes & colours of the same product – which in this case is a product called “Funky Hat”. You can see in this screenshot that there’s four rows have the exact same Product Name. If you go over to the far right of the template:

…you can see which size and colour each row of this “example” data represents.

Let’s examine these columns a little closer.

If a product comes in just one style (e.g. “Size”), then in the Style names column, you should simply write “Size”. Easy enough.

However, if a product comes in more that one style (e.g. “Size” and “Colour”), then in that Style names column, you should write in both the style names, separated with a “pipe” character ( | ).

On most keyboards, you can usually get a “pipe” by holding SHIFT and pressing the backslash key, found at the bottom left:

In the Style values column, you should enter the exact style values (i.e. the exact size / colour) which each specific row of your spreadsheet corresponds to.

Here’s that screenshot again for reference:

Again, if there’s more than one style against that item (e.g. Size & Colour), you’ll need to enter both the size and colour into that one cell, separated with a “pipe”.

(Note that if you’ve specified “Size|Colour” in the Style names column, you’ll need to enter them in that order in the Style values cell. So, for example, if you’ve put “Size|Colour” in one, then “Black|Small” in the other, it won’t work; the system will just tell you “I can’t find a Size called ‘Black’, or a Colour called ‘Small’”.)

Once you’ve put together a spreadsheet for importing products with styles, you should go through the “Create new import job” and “Edit Source” stages in the same ways as described previously in this manual.

You’ll then need to move on into the Map Data screen…

Mapping a spreadsheet for importing products with styles

When you get to the “Map Data” stage of setting up your import, you’ll need to ensure that at the very bottom of the “Map Data” screen, the Style Types and Style Values columns are mapped accordingly:

Once you’ve mapped your sample file accordingly, you should be ready to try an import; return to page 27 and follow the instructions there.

Importing amendments to existing products

We’ve seen a lot on how to import new product info into the system. We haven’t yet seen how you go about importing changes to products which already exist.

Let’s talk a little about that next.

Importing new prices onto existing products

One of the most common such changes users wish to make through the Import Manager is to prices.

Let’s say you wish to import new Store Prices for selected products into Cybertill.

In order to do this, you’ll need to put together a spreadsheet like this:

…or this:

The first column of your spreadsheet must contain either a list of item references or a list of barcodes.

This is your ‘identifier’ data. The system needs a way to identify which items you want to apply new prices to. It can do that using either the itemRef or a barcode.

The second column should contain the new price.

(If you wanted to import new web, trade or staff prices, you could add extra columns for those in too.)

Once you’ve put together a spreadsheet like this, you should save it as a CSV file with a suitable name (e.g. “newprices.csv”).

Next, let’s set up an import job for this.

Go into Admin \ Data Import Manager, and click the option to create a New import job.

Make sure to fill in the “Process” fields as follows:

Note that the Process method field is set to Update Existing Data Only. This is how you tell Cybertill that your new import job is purely for amending details of existing products, and not for adding new products.

Fill in all of the other fields as you see fit. Make sure that you enter the appropriate filename in the Source filename section:

Once you’ve saved this, go to the Edit Source page, load in your “newprices.csv” file and set each column’s data format as follows:

Next, go to the Map Data page.

Map the rows on this page as follows:

Of course, if you’ve used ItemRefs instead of barcodes in your spreadsheet, you should map that column to the “Item Reference (Unique Key)” row, instead of the “Primary Barcode (Unique)” row.

Once you’ve mapped each column accordingly, you’re ready to go. Simply perform the import, and your new prices should go in.

Not too tricky, is it? Once you’ve seen how to import completely new products into the system, importing price changes to existing ones is comparitively straightforward.

Importing new stock and/or min & max levels onto existing products

You can probably guess by now that if you want to import stock and/or min & max levels into Cybertill, you’ll need a spreadsheet with one column for Barcode or ItemRef, then columns for Stock, Min and Max as required.

What might be less obvious (particularly if you only have a single branch) is that you’ll also need a column in there specifying the branch that info is going in for.

Stock, min and max levels are branch specific in Cybertill. So, if you want to import that info, you’ve got to tell it which branch you’re importing stock levels for.

You will need to do this even if you only have one branch.

It might seem strange to you, given that these stock levels you’re loading in must be meant for your shop. (After all, where else could they go?). However, Cybertill doesn’t think the same way as you do! It’s been designed to work for retailers with many different branches, and consequently it will never just put your new stock levels into whichever branch ‘seems appropriate’. It always needs to be told which branch those stock levels should go into.

(In other words: You can’t just say to it “import these new stock levels”. You have to say to it “import these new stock levels into my ‘Shop’ location”.)

The best way to do this is by adding a column to your spreadsheet for “Location”.

In this column, you’ll either need to write in the appropriate location name (as they appear in your Admin \ Locations Management screen, as illustrated below):

…or, if you’d prefer, you can enter the appropriate branch’s abbreviated location reference. To see a branch’s reference, go to Admin \ Locations Management, then click the Edit link at the right. If you’ve entered a reference, you’ll see it here:

You’d need to put yourself together a spreadsheet looking something like this:

(Just like before, you can have a column for ItemRef instead of

Barcode if that’s preferable.)

If we were then to create a new import job using these settings:

…and if we were to load our spreadsheet into the Edit Source screen and set the data format as follows:

(Note that the above screenshot assumes you are using integer stock levels. If you are using decimal stock levels, feel free to change the data types as appropriate.)

…and lastly, if we were to map the data as follows:

…then we’d have a working stock, min & max import ready to go.

FAQs and Troubleshooting

In Admin \ VAT Setup, there isn’t anything in my “Code” column!

You’d better put something in there then! Unless you’ve added a “code” to each of your VAT rates, you won’t be able to specify in any product import which VAT rate each product should be. Just use the Edit links at the right of the VAT Setup screen to add codes in.

How can I add an extra column / remove a superfluous column from my import job quickly?

You’d need to go back to Admin \ Data Import Manager, click the appropriate Edit Source link, load in a ‘new’ sample file from scratch, set the data type for each column from scratch, then map the data from scratch.

If you wanted to add a new column to a spreadsheet, sadly, you’ll have to do all of that again.

If you wanted to remove a column from a spreadsheet, to save time, consider this alternative: Leave the column on the spreadsheet, then go into Admin \ Data Import Manager, click the Map data link, and ’un-map’ that column from the appropriate row. That way, the column stays in the spreadsheet (meaning you don’t have to configure everything from scratch again), but it won’t link to any product data fields in Cybertill. (In other words, the system will ignore that column and its contents.)

How do I turn my Excel spreadsheet into a CSV spreadsheet?

Here’s how you save a copy of an Excel spreadsheet as a CSV file:

Open your spreadsheet in Excel, then go to the File menu and click Save As.

Click into this box at the bottom of the ‘Save As’ window:

Scroll down through the list of different file types, and you should find an entry called “CSV (Comma delimited) (*.csv)”. Click that. Then, go ahead and save your file.

Why do you recommend I tell Cybertill that the BARCODE column of my spreadsheet contains ‘strings’, rather than ‘integers’?

For most retailers, ‘integer’ will work absolutely fine here. The vast majority of barcode numbers are indeed integers (i.e. round numbers, numbers with no decimal places).

However, there are a few retailers using Cybertill whose barcode numbers have letters in them as well as numbers. The only way the system will accept combinations of letters and numbers is if you mark the column as containing ‘strings’.

I’ve done a really big import of product details, but it’s gone in all wrong! How do I get rid of it and start again?

With some difficulty… There aren’t any ‘undo’ functions you can use to clear it out. The only thing you’ll be able to do is delete each of the offending products one at a time…

If that’s not realistic, you could try creating a “TRASH” category and moving all of the ‘junk’ products into it, then submitting a CAF form (found in our filestore) requesting whether we can delete ‘all products in TRASH’ for you. Be aware that the feasibility of such a request varies depending on circumstance and that we cannot guarantee we will be able to do it.

In future, try uploading a small sample first to check everything’s working okay before you do the whole lot!

When I’m importing new products into the system, can I import stock levels and/or min and max levels at the same time (i.e. as part of the very same import job)?

Yes, but we wouldn’t recommend it. It’s best practice to import the basic product data first (name, prices etc), then load in any stock information afterwards – i.e. do it as two separate jobs. Without going into too much detail, users attempting to do both at the same time usually end up inadvertently setting “branch-specific prices” for their items, which can cause all kinds of price-related grief later down the line. (Strange as it sounds, it can cause problems even if you only have one branch.) So, we’d strongly urge you to do basic product info and stock info as two separate imports.

I’ve just opened one of my CSV files in Excel to make some amendments, and my barcodes look funny; where they once read like “1234567890123”, they now read like “1.23457E+12”! What’s happened?

When you open a CSV file in Excel, Excel does sometimes do strange things to it. That “E+12” is actually a mathematical phrase. Long story short, Excel is attempting to ‘abbreviate’ your barcode numbers, because they are unusually long. It thinks it’s doing you a favour. (It isn’t, unfortunately – this doesn’t help us at all, and it may even hinder us.)

Don’t panic, though, because your computer should still be ‘remembering’ the correct barcode number. Try clicking into one of the affected cells, and see what appears in the “Formula Bar” at the top of the screen. Even though you’ll see the “E+12” number in the cell itself, you should hopefully see the full barcode in the Formula Bar, like in the following screenshot:

See how the ‘abbreviated’ barcode appears in cell B4, but the ‘full’ barcode is still saved in the system? (See the full barcode of

4521681276372 in the Formula Bar at the top of the screen?)

To get things looking the way they’re supposed to again, right-click on the column header (i.e. where the B is in the above screenshot). The whole column should be highlighted, and a menu should appear:

Click on Format Cells. Another window should appear.

In the “Category” panel on the left, click Number.

In the “Decimal places” box on the right, enter 0 (zero).

Click OK at the bottom. You should find that the full, ‘unabbreviated’ barcode numbers appear in the spreadsheet from that moment forth.

Should Excel ever do this to you, it’s very important that you fix your “barcode” column in this manner before you save any changes to your spreadsheet. Otherwise, your computer truly will ‘forget’ the full-length barcode numbers and will only remember the ‘abbreviated’ ones – and those ‘abbreviated’ numbers will mean nothing to Cybertill!

Appendix – ‘sample’ files

In the filestore, you will be able to find two ‘sample’ files that complement this manual:

productimport.csv

Data Import Manager Template.xls

To find them, go to Cybertill’s homepage, scroll to the bottom and click Drivers, Applications and Manuals.

Either Browse or Search for the files in question.

The file named productimport.csv has been referred to throughout this manual, and is an example of a typical spreadsheet of product data suitable for importing.

The file named Data Import Manager Template.xls is similar, but it contains notes at the bottom of each column describing the data it should contain.

Afterword

Please do exercise caution - and do a small import to begin with. (As noted earlier, if an import does not work in the way you want it to, it can be very difficult to “undo” it). If you’re in any doubt whatsoever, please contact our helpdesk on 0844 855 1600 or support@cybertill.co.uk .

Data Import Manager User Manual

Introduction

About the Data Import Manager/Module

About this manual

Accessing the Data Import Manager/Module

Creating a new import job

What is a “New Import Job”?

Creating a new import job

Uploading a ‘sample’ datafile

Why do we need to show the system a ‘sample’ datafile?

I don’t have a ‘sample’ datafile prepared!

How to upload a ‘sample’ datafile and set rules about its contents

Mapping the data from your sample datafile

What does ‘mapping’ mean here?

The ‘mapping’ process

Can I ‘map’ the same column to more than one field?

What do all the rows represent?

Performing an import

A brief word of warning

Checking the import file

Performing the import

A Sample Template

Importing new products with styles

Preparing your system and a spreadsheet for importing products with styles

Mapping a spreadsheet for importing products with styles

Importing amendments to existing products

Importing new prices onto existing products

Importing new stock and/or min & max levels onto existing products

FAQs and Troubleshooting

In Admin \ VAT Setup, there isn’t anything in my “Code” column!

How can I add an extra column / remove a superfluous column from my import job quickly?

How do I turn my Excel spreadsheet into a CSV spreadsheet?

Why do you recommend I tell Cybertill that the BARCODE column of my spreadsheet contains ‘strings’, rather than ‘integers’?

I’ve done a really big import of product details, but it’s gone in all wrong! How do I get rid of it and start again?

When I’m importing new products into the system, can I import stock levels and/or min and max levels at the same time (i.e. as part of the very same import job)?

I’ve just opened one of my CSV files in Excel to make some amendments, and my barcodes look funny; where they once read like “1234567890123”, they now read like “1.23457E+12”! What’s happened?

Appendix – ‘sample’ files

Afterword

Comments