Samples Product Information Home Page Download Pricing

Exporting Data from CleverCat

In this series of tutorials, I demonstrate creating a database from scratch and uploading data from a local database to a mySQL database on a web server. However, if you already have an existing databse, you can simply map that database to CleverCat's templates using the instructions below. Even if you have an existing online database, there is an advantage to recording at least a few items in the local database. The HTML templates which CleverCat creates are very similar to its printed ones and with a few products in the local database, you can get a preview of what your catalog will look like before uploading.

On this form, the first thing you need to do is set the folder into which the files will be created. The letter case you use for the folder is not important as this is on your local hard drive. However, every other field on this form will refer to a folder, file or field on your webserver and letter case will be important. If you indicate that your image files will be in the Photo folder, this is different from the photo folder.


After setting the folders, click the PHP tab. The values you enter on this tab will be used to connect your templates to the mySQL database as well as create the required columns. Server/Host, Database Name, User, Password and mySQL Table are all required to connect to your MySQL database and are the same values you used earlier when using phpMyAdmin.
If the database name and user were prefixed with the domain name in phpMyAdmin, you'll need to enter the full text here spelled exactly as it was there. For my example, you'll see that some of my connection values are prefixed by mySite_.


Earlier, I indicated that CleverCat creates its own hidden Index column in the local database and, if you followed along with the tutorial, you would have created a Primary Index in the new table. Does a table need two indexes? No, but there is a reason why you might want to create another one.
The Primary Index in the mySQL table is auto-incrementing, starting at number one. The index in CleverCat is also auto-incrementing but, as you compile your data, you will likely delete records or move them around and this means that the local index numbers are not likely to match the Primary index numbers. It's not necessary that they match but, if we later develop features that pass data between the two databases, having a common index between them will make it possible for them to synchronize.
So, for this tutorial, I'm going to upload the local index by creating a column for it. I do this by entering a name in the Unique ID field. For my example, I'm giving it the name invID.


If you don't want the second, matching index, just enter the name of the index you already created in the mySQL table. This is a good time to mention that you shouldn't use certain names for columns. Certain words such as PRIMARY, SELECT and ORDER are reserved. For a complete list of reserved words, follow this link: MySQL Reserved Words

Category is a required field. Subcategory is optional and I'll be leaving it blank in this example. Enter a name in the Photo 1, Photo 2 and Image Title fields if you intend to use them.

The Print Order and Detail Order columns are also optional but useful. I'm going to come back to these in a few paragraphs because the values you enter in the data fields may affect your decision on these fields.


At the top of the next column of fields is a category selection box. If you change the category, you will see that the labels change to match those in the selected category. In my example, I've selected the Baseball Gloves category so that you can follow along and see those same column header names in earlier screenshots.
These labels are for reference only. The names you enter do not have to match the labels although most users will probably do this because their data will be similar across categories.
In my example, I've matched several of the labels and column names but, for the column name next to the Size label, I've set it to the generic name data3. For the category, Baseball Gloves, Size is a relevant name but the column names I enter will be used for all categories and Size does not make sense for the others.

You can also see that it is good practice to use the same column for the same purpose throughout your data.
In this screenshot of a spreadsheet, I have left one column blank in the BBQ category so that the Price field is in the same column for all categories.
This structure is not required as the different templates can be setup to print the price correctly no matter what column it is in. However, if you were to try to create a price sheet template which showed data in all categories, it would not be possible unless the price were in a common column.

If you have viewed the template design tutorials, you'll know that there is one column which has an additional formatting option that other columns do not have. This is the Notes column and is typically used for long text descriptions and I've entered comments in that field so that it will also be created.
Lastly, you don't have to create an online column for every column in your local database, only those that will be printed. Equally, you can create columns and upload data which you do not intend to print. For example, you could upload a column which records the quantity of items and use this only for your own inventory control purposes.

Returning to the Print and Detail Order fields.

For my example, I'm going to upload the same numbers that are used to sort a printed catalog, so I enter order1, in the Print Order field and order2 in the Detail Order field.

I could also sort the online data alphabetically by the product description. I would do this by entering Description, an existing column name, in the Print Order field.

Note that Detail Order is only required if your data is organized into tables. If used together with Print Order, this would make Inv. Group redundant.

The names that you enter on this tab will be used to create columns in the mySQL table but, using a name in the Print Order field which is already used in a Product Description field does not create a second column. The Description field names take precedence and a single column named Description will be created as a text type field.
In the previous chapter on phpMyAdmin, field Types were described. If you enter a unique name in the Print Order column, then an int type (integer) field will be created. If you intend to use both the Print and Detail Order columns, both should be integer type fields.

The field, Inv. Group, is not used in the table creation phase but you can enter a value now if you want because it controls how data will eventually be displayed.
Inv. Group serves the same function in an online template as it does in a printed template. It groups related products together to create tables.

In a printed catalog, the Sorting/Grouping function automatically creates the Print and Detail Order numbers and these numbers create and sort the tables. In an online template, these numbers perform the same function but you can also just group the products alphabetically, unsorted, on any Product Description field by entering a name in the Inv. Group field.

A Print Order selection and an Inv. Group selection is also a valid combination. In this case, the data would be grouped by Description and sorted by order1.
Inv. Group and Detail Order together would also be valid, though uncommon, as the Detail Table would be sorted but the product groups would be left unsorted.

If you enter a name in all three fields, the Inv. Group selection will be ignored. This is because Print Order and Detail Order are assumed to be numeric fields which both group and sort the data.


After entering the names of all the columns you want to create, click the Create Files button. This will create the folder dbEditor in the Local Output Folder. A number of other files will be created in this folder as well inluding: dbParam.php and dbImport.csv.
dbParam.php is a configuration file and dbImport.csv is a CSV spreadsheet containing all the records in the local database. Note that only the categories selected for printing are included in the CSV file.


Before proceeding to the next chapter, open the CSV file in Excel. The names you entered on the PHP form should be displayed in the column headers and the data below should match up - that is, the category names should be under category, the photo filenames under photo and so on.
However, the data in the CSV file may appear compressed, fragmented or use more columns than you specified. This is especially true if your product data contains commas. CSV stands for Comma Separated Values and is a standard exported file format from many applications, including many accounting and inventory control softwares.
Unfortunately, the CSV format can be a problem if your descriptions require columns. For this reason, CleverCat uses the | (pipe) character instead of commas as the column separator. This allows you to safely use commas in your product descriptions but will cause the CSV file to look compacted when opened in Excel.
If you are going to be uploading a spreadsheet created by another software which uses the comma as a separator, you will need to edit one of the configuration files. Contact us for more information.


Return to Tutorial Selection or Continue to the next chapter: Uploading Your Data