Importing Data from a Spreadsheet such as Microsoft Excel

You can import data from a spreadsheet, such as Excel, into CatBase and then publish it using Adobe InDesign, QuarkXPress, etc.

Overview of the Process

There are just a few steps to importing Excel data into your CatBase database:

Check the format of the spreadsheet
Save it as a delimited text file
Import the data into CatBase
FAQ

1. Check the Format of the Spreadsheet

You're going to map the columns in your spreadsheet to the tables and fields in your CatBase database:

A spreadsheet "sheet" is equivalent to a CatBase Table
A column in the spreadsheet is equivalent to a field in the database
A row in the spreadsheet is equivalent to a record in the database

Let's look at a simple example. Here's a small section of a product information spreadsheet:

Note the following points:

The first row contains the column headings; these equate to CatBase field names
Each row contains the details for ONE product
Each product's details are confined to ONE row

Problems to look out for

Text fields containing Returns:: If you have any text fields that contain Return characters (paragraph breaks) these will cause a problem when the spreadsheet is saved as a text file. That's because a Return character is the usual record delimiter in a delimited text file, so when you try to import the file into CatBase, it will think that each Return character signifies the end of one record and the start of a new one. The solution is to replace all Returns with another character - for example, a tilde (~). Make sure it's a character that will not be used in regular text! Then, in the Preferences tab of your Import Style, you can tell CatBase to convert that character back into Returns. For example:

Missing Key Field:: There will always be one column which contains the key field. This is the field that identifies each record; usually a unique field such as a record number, ID, or (as in our example, above), a Product Code. This field must be included and populated for each row in the spreadsheet.
Missing essential data:: Often a spreadsheet will contain an important item of data as a kind of subheading, with the assumption being made that all the following rows should have the same data applied to them. Typically, this happens with category headings - for example:
: This won't work! When you import the data into CatBase, it will have no way of knowing which Category to put each product into.

Save it as a delimited text file

Once you're confident that the format of the spreadsheet is correct, choose Save As ... from the File menu. The Save As dialog will have an option to choose the format of the file; in Excel it will look something like this:

Click on the popup menu and choose either CSV (comma-separated) or Tab-Delimited Text File.
Make sure you know where the file is going to be saved!
If you see messages warning you about losing formatting etc. just click OK.

Import the data into CatBase

First, make sure that you are aware of which field you are going to map the key field to. If it is a unique number, such as an ID number, you might choose the Recordnumber field in CatBase. But be aware that this field MUST be unique, and it must be a number! The key field can also be the key text field that has been specified in Table and Field Setup - for example, the product name, company name, last name, etc. (To check on this, click on the Setup button on the main window and choose Table and Field Setup. The key field will be highlighted in green. If you want to change its properties, such as its name, double-click on it.)
On the main CatBase window, click on the Import Data button
If you don't already have any import styles set up, CatBase will ask if you want to create a new one; you do! If you already have one or more Import Styles set up, choose Add a new Import Style from the pop-up list of options
Enter a name for your Import Style
Choose either Comma-delimited or Tab-delimited from the pop-up Format/source menu, depending on how you saved your text file
Choose the table that you are going to import the data into from the pop-up Import into table menu
Click on the Select button (next to the Import File field), locate your text file, and double-click on it
CatBase will open the text file and import a few records. If it does not find fields with names that match your spreadsheet column headers, it will ask you to either ignore that column, choose a field, or create a new field.
You must make sure that the key field is mapped to the appropriate CatBase key field.
If you are importing Category names or numbers, you must map them to the MainCategoryName or MainCategoryNumber field, otherwise the records will not be linked correctly to the categories.

For more detailed information about importing data, please see the Importing Data Tutorial.

Frequently Asked Questions

Q: What if I want to put one record into multiple categories?: The easiest way to do this is to have multiple rows for the product - one for each category. You must then remember to select the Update Existing Records option in the Updates tab in your Import Style:
Note that we've selected the Update Selected Fields Only checkbox and the only field selected for updating is the MainCategoryName. This tells CatBase that we just want to add an additional Category to an existing product (if the product doesn't already exist, it will be added, along with all its details).

Options