10.3. Importing data

The Data Tool can import from CactuShop v6 or v7 (CactuShop is an older classic ASP shopping cart also produced by Cactusoft), from a CSV or Excel spreadsheet, or from another Kartris database.

The basic principle of data imports is to have a source and destination, and copy information from one to the other. In this way, imports and upgrades do not damage or change the original database.

The import dialogs will typically walk you through step-by-step, and give information if there are problems.

10.3.1. Formatting spreadsheet/CSV data for import

The Data Tool requires CSV or spreadsheet data to be suitably formatted for import. A sample spreadsheet with some correctly formatted data is included in the data tool zip, or you can download it below.

Downloadable Content: "Example Excel Spreadsheet for importing with the Kartris data tool"

This download is available at: https://tome.host:443/Downloads/2071__fs_1519051679_8531.xls/

10.3.1.1. Categories

Above: the category fields section of the data tool import spreadsheet

The spreadsheet can contain categories nested up to FIVE levels deep. The first column, Cat5_Name1, holds a top level category name. The Cat4_Name1 field on the same line would hold a subcategory of this, and so on down to Cat1_Name1.

Since each product must have at least one parent category, every row of your spreadsheet should have a Cat5_Name1 value, but not necessarily any subcategories.

10.3.1.2. Products

Above: the product fields section of the data tool import spreadsheet

The P_Name1 field (product name) holds the name of the product. Product name is used by the data tool as the key for a product, therefore you should not have multiple products with the same name in a data tool spreadsheet (if you do, they will be assumed to be the same product). However, the product name will appear on multiple rows if a product has multiple versions.

10.3.1.3. Product images

The P_Image field in the spreadsheet is very flexible in accepting the locations of images.

You can enter a single image name, or you can add multiple image locations, separated with either a comma or semi-colon.
image1.jpg
image1.jpg,image2.jpg,image3.jpg
image1.jpg;image2.jpg;image3.jpg
   
   
If you enter local paths to images (e.g. just an image name, or a folder plus image name), then the data tool will look for these within the source images folder you specify. So in the case above, if you selected a folder on the C: drive called 'source', it would look for those JPG files in C:\source.

You can also get the data tool to retrieve web images, if you have a full URL. In this case, the data tool will still need a 'source' folder, but will retrieve the web image, copy it to this source folder and then process it and position it in the destination folder. For example:
http://www.site.xyz/images/image1.jpg
http://www.site.xyz/images/image1.jpg,http://www.site.xyz/images/image2.jpg
http://www.site.xyz/images/image1.jpg;http://www.site.xyz/images/image2.jpg
   
   
If you leave the image field blank, then no image will be created in the new 'Images' folder system. However, this will not delete images that are already on the site in that location.

10.3.1.4. Versions

Above: the version fields section of the data tool import spreadsheet

The V_CodeNumber (the SKU) is the unique key for versions and must be unique.

V_Price and V_Weight should all be numerical values with no currency or weight units.

 

V_Type can be 'c' for combination, 'b' for base version (the base of an options product) or 'v' for version. The field is not required, if blank, 'v' will be assumed.

10.3.1.5. Tax rates

The T_TaxRate field for EU sites, and others using a VAT model (UK, NZ) should hold the % rate of VAT that applies. The T_TaxRate2 field should have zero.

 

For Canadian sites, it will hold the GST value, while the T_TaxRate2 field would hold the PST value.

10.3.1.6. Supplier

The supplier field should hold the name of the supplier.

10.3.1.7. Attributes

Attributes for each item can be formatted within a single field, using the following structure:

Energy {{4500kcal}} || Animal {{Cow}}

You can have multiple attributes. Each attribute contains a name, with the corresponding value after it (space separator) enclosed in double curly brackets.

Each name-value pair should be separated from the next with two vertical pipes, and spaces.

10.3.1.8. Options

Options products can be imported into Kartris using the data tool and a spreadsheet, but the formatting is a little more complex. First, you must format the options available on the second worksheet of the spreadsheet. Each OptionGroup_Name is unique, so if you have multiple different sets called 'Colour' for example, you should rename these to avoid them all merging during import.

Because options requires a second worksheet, it's not possible to use CSV format. You must use an Excel spreadsheet format.

Above: the options group data on the second worksheet

The options for each product should then be formatted within the regular data page of the spreadsheet in name-value pairs, similar to the format used for attributes above.

Above: the formatting of option data at the end of the main worksheet

Both the names and values (for example, "Size" and "Large") must match up with options detailed in the second worksheet, the OptionGroup_Name and Option_Name respectively.

10.3.2. Importing from CactuShop

The Kartris data tool can import product and order/customer data from CactuShop databases, versions 6.0 and above. If you have data in an older v5 CactuShop database, you can use the CactuShop data tool to upgrade/import this to a new CactuShop v6 database, then use the Kartris data tool to import data from this database to Kartris.

The Kartris data tool will preserve the product, category and version database IDs from CactuShop in the new Kartris site. This is extremely valuable for search engine purposes as it ensures that requests to old CactuShop URLs can be mapped by Kartris to the equivalent new page URL. Your existing pages in search engines will therefore not return a 404 to visitors or search engine spiders; both will get a 301-redirect (permanent redirect) and land on the same product in the new Kartris site. Search engines will update their indexes relatively quickly with the new URLs, but old links that users may have posted in forums and so on will continue to drive traffic to the correct page in your new site.

The image storage in CactuShop worked a bit differently to the structure used in Kartris. Therefore, when importing data from CactuShop to Kartris, you can select the 'Uploads' folder within the CactuShop web, and the 'Images' folder of your new Kartris site (or you can create a local 'Images' folder on your desktop, and copy it to the Kartris site later), and Kartris will copy and file the images correctly into this folder to work with Kartris. Because Kartris does not need smaller thumbnail images, only larger ones (as it thumbnails dynamically where required), it will look first for large images to import for a product, and only go down to normal sized ones if no large image is found.

Order IDs are also maintained, and historical orders from CactuShop can be imported with customer data into Kartris for a seamless upgrade. In fact, orders placed in CactuShop and still awaiting processing in the back end will be upgraded and can be processed and closed off with Kartris after upgrading. Customer accounts will retain the same username and password, and will retain historical order history.

10.3.3. Data tool caveats

In order to import bulk data from a spreadsheet, the Data Tool needs to make some assumptions.

 

  • The SKU (version code) for every item must be unique. You cannot have two items in Kartris with the same SKU; if Kartris finds duplicate SKUs, it will either ignore or replace the existing item with the same SKU (depending on settings).
  • All products must have unique names, because products don't have any SKU (these belong to versions). It is possible to have a product with multiple versions, therefore each line in this case will repeat the product name, but have a different SKU and version name. The Data Tool will assume that any row with a repeated product name is the same product.
  • All categories must have unique names - the reasoning is similar to the point above regarding products.

Because of the reasons given above, at present the Data Tool cannot handle cases where multiple products have the same name, or where a products and/or categories have multiple parents.

10.3.4. Importing from other databases

If you need to import data from a third-party database, such as that from another shopping cart package, the best approach is to produce a query/script in that cart which can output product data in a flat-file format. You can then manipulate this in Excel into the required spreadsheet layout for important by the data tool.

10.3.5. Exporting and reimporting data

You may find that for large updates, you wish to export all your product data, make changes in a spreadsheet, and then import it. We provide a saved export in the back end (DB admin, exports section) for this, which has been updated in v2.9005 to match the format the data tool requires for import.

The images fields will be output as blank; you can run in data without these and it won't wipe images, as the images are stored in the 'Images' folder on the site, and the data tool will only update the database content.

10.3.6. Import options

The Data Tool can be used both to populate a brand new store with product data and to update an existing store. In cases where you are updating a store, there are three options as to how you handle existing data already in the store:

 

  • Add imported data to existing data and ignore duplicates - do this if your site database data is the 'master' version, and you've made corrections and changes to it, or are just confident existing data is already perfect

  • Add imported data to existing data and update duplicates - in this case, any duplicates in the destination will be updated from the source data

  • Reset database and import - this will clear existing data, and then import the data so you retain no previous data
Depending on where you are importing data from (CactuShop, Kartris or spreadsheet), there are also a variety of filters to control what data is updated.

10.3.7. Post import processing

Very often whether you've imported from a data file or from an older CactuShop database, you will want to run some additional processing on the order. For example, you may want to change the product display type for all items, or fix other problems you might find in imported data.

Fortunately the Data Tool has a built in method for doing this. Within the KDT_Resources folder, there is an .sql file called 'kartrisSQL_Update.sql'. This SQL gets run against the database after an import is complete. This mainly fixes null values and other such issues, but you can add your own SQL code to this if required.

This SQL file gets run after both data file or CactuShop data imports, both using the GUI or in command line mode.

10.3.8. Command line support

The Data Tool is also accessible via a command line interface, which means you can use the pre-written functionality to handle imports to a site on an automated or scripted basis. For example, you may have stock or other data deposited in spreadsheet format to a particular folder on a periodic basis (e.g. nightly) which you want to update the web site with.

Using a .vbs file (or some other scripting system), you can call the Data Tool, pass parameters and so on, without having to manually operate it or write your own SQL code to update Kartris.
PARAMETERS:

/MODE:[ignore|update|deleteall]   -> what to do with existing product data

/FILE:[csv_or_xls_file]

/DATASHEET:[worksheet_name_of_data_in_xls_file] (XLS only)
/OPTIONSSHEET:[options_sheet_name_for_Kartris_v1.4] (XLS only)

/DBSERVER:[servername]
/USEWINDOWSAUTH (optional)
/DBUSER:[username]
/DBPASS:[password]
/DBNAME:[databasename]

/HELP (optional)

/OUTPUT:[logfile(default)|display] (optional)
/SOURCEIMAGESFOLDER:[folderpath] (optional, can use full image path in spreadsheet)
/KARTRISIMAGESFOLDER:[folderpath] (optional)
/TEST (optional) (no processing - just check if parameters are all correct)

/HASH:[hashsalt_value_from_web_config_of_site]
For example (single line, though some browsers may wrap it below):
C:\Data Tool\KartrisDataTool.exe /mode:update /file:"c:\user\desktop\exampledata.xls" /dbserver:localhost\sqlexpress /usewindowsauth /dbname:kartrisSQL

If you have spaces in the names or paths of files submitted through command line arguments, you will need to enclose the path in double quotes. Alternatively, try to position files close to the root in a folder without spaces in the name.