RO CSVI

How to CSVI?

How to CSVI?

This is the question that most people ask themselves when they get to the point where they want to maintain their inventory. Though updating inventory via the provided user interface is possible, it is far from convenient if you have more than a few dozen products. Many suppliers will give you a list of products in CSV format, surely you do not want to type out that list by hand.

At this point you have 2 options:

  1. Import the list directly into the database using a tool like phpMyAdmin
  2. Import the list using CSV Improved

The downside of the first option is that you have to know the database structure of VirtueMart. This is where CSV Improved steps in. Through the same backend where you manage your shop, you can manage your imports and exports. CSV Improved gives you a friendly user interface for importing and exporting your data.

The alternative to using CSV files is using XLS (Excel) files for import. In case of XLS files the Delimiters section can be skipped because it is not applicable for XLS file.

A friendly warning beforehand, there is a steep learning curve but for those who persist you will be rewarded.

What is CSV?

CSV stands for comma separated values and is a common format to exchange data between different systems. A typical file looks like this:

field1,field2,field3

While this works fine for numeric data or plain text with no special characters, the trouble starts when a field contains product descriptions. According to the standard, a comma cannot be used as this separates each field. This is where the delimiters step in.

Delimiters ... a matter of separation

An example:

field1,field2,field3
tube1,Green, blue and red,Mixed paint

The system will find the first line and see 3 fields however the second line will lead the system to think there are 4 fields. The comma in the color description breaks the system. The name might imply the comma is to be used for separation, actually any kind of character can be used as a delimiter. There are 2 kinds of delimiters:

  • field delimiter
  • text delimiter

The field delimiter

The field delimiter separates the different fields in a CSV file. Though this originally is a comma, other characters can also be used.

The text delimiter

The text delimiter is used to mark the beginning and end of each text field. This makes it easier and more accurate for the system to determine the beginning and ending of each field. Changing the example shows the following result:

"field1","field2","field3"
"tube1","Green, blue and red","Mixed paint"

The data is now easy to distinguish and the system will read it correctly. Now the system can recognize each field correctly as the comma in field2 is enclosed between double quotes.

Matchmaking ... a fine art

With the data clearly separated the system needs to be told what data is going to be imported. The names field1, fied2, tube1, mixed paint etc. do not mean anything. To the system it is only a combination of numeric and alpha-numeric characters.

The way to tell the system what data is going to be imported is by giving each field a name that the system already knows. All the field names that the system knows are collected in the available fields page. Most fields are specific to a certain import or export.

With the list of available fields at hand, the field names can be matched (or not). The first field we have is field1. Looking at the data on the second line, it tells me this is the unique identifier for this product. A search through the list will come up with product_sku as the field name needed here. A first match is made.

"product_sku","field2","field3"
"tube1","Green, blue and red","Mixed paint"

The second field is next. Again going through the list will show that product_desc is the field name best applicable here. Another match has been made.

"product_sku","product_desc","field3"
"tube1","Green, blue and red","Mixed paint"

Repeat this procedure for each field in the file until all field names have been filled. When a field cannot be matched, the field cannot be imported and should be removed from the file. After all fields are matched the example looks like:

"product_sku","product_desc","product_name"
"tube1","Green, blue and red","Mixed paint"

Even though no field is always mandatory, the product_sku field is probably the most needed field as this tells the system what product the data is for.

Templates ... give the answers

With the file ready for import, the system needs a few answers before the file can be imported. Instead of answering the same questions on every import, CSV Improved makes use of templates. Templates contain all the answers and settings needed for an import or export.

First step is to create a new template by clicking New on the template list page. This shows the template settings page, here the different settings can be set that are going to be needed for import.

The import type is the type of import to perform. Since the test file is for adding a new product, the choice here is product import. The name of the template can be anything descriptive. The delimiters will be the ones used in the file. For the test file the field delimiter is set to ^ and the text delimiter is set to ~.

Now the import tab is important because the test file is going to be imported. The first line in the test file contains all the necessary information so the import will use the column headers for configuration. The skip the first should not be checked as we need the first line for the configuration. To be able to update the product in case it already exists overwrite existing data is also checked.

The template can now be saved as all preferences have been set.

Take off ... buckle up

With both the template and file ready, the import can begin. Steps to take to import:

  1. From the control panel click on Import
  2. Select the template to use for import
  3. Select the file to import
  4. Click on import

Now sit back and enjoy.

The end

After the import has been completed a result screen is displayed stating how many records were processed and how they were processed.

That is how to CSV!