Importing Data into CatBase

Importing Data

CatBase can import data in various formats: Excel spreadsheets, delimited text files such as tab-delimited or CSV (comma-delimited), dbf data files, and (Data Chameleon Edition) directly from another data source – such as MS Sql Server or MySql – via ODBC.

You can use imported data to update existing data in your database - for example, to update product prices.

Overview of Data Importing

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

Create a new Import Style in which you set various options such as how to map the imported data to your CatBase tables and fields; whether to remove tags (such as HTML tags), or covert them to another tagging format; whether to convert between Windows and Macintosh character sets; if you want to update existing data with imported data, and other options. Import Styles can be saved so that the same setting scan be re-used another time.
Specify your options (e.g., what type of file you are importing or what the data source is)
Import some sample data and make sure it matches your tables and fields
Import the data

1. Create a new Import Style

To create a new Import Style:

Select the table that you want to import data into
Expand the Import topic in the left-hand menu
Click New Import Style
Specify your Options

A new Import Style looks like this:

Enter a name for your Import Style into the Name field. This can be anything you like; something descriptive such as "Product details" for example.

Choose a Format or source from the pop-up Format/source menu. The options are:

Note: the MySql Database and ODBC Data Source options are only available in the Data Chameleon Edition.

Depending on which option you chose, the window may change, giving you different options. These are described in detail in individual tutorials (see the list at the bottom of this page). For the moment we will assume that you have chosen the default option: Tab-delimited, or another delimited text format such as comma-delimited (CSV).

2. Import some Sample Data

First of all, note the check box: First line of import file contains field names.

Usually, when a text file is created from a spreadsheet or a database application, the first line of the file contains the field - or column - names. If that's the case, CatBase can try to match those column names up with the fields in the database. If your file doesn't have the field names in the first line, un-check this check box - otherwise you may lose the first line of data.

Note: If you're importing data from an Excel spreadsheet, the column headers must be the first row in the sheet. Otherwise, CatBase won't recognise them as column headers.

Next, click on the Select button next to the Import Data File field. This will open a dialogue for you to choose the file that you want to import: find your file and double-click on it. CatBase opens the file and starts to read in a few records. It tries to match the columns in the import file to the fields in the CatBase table. The program is quite intelligent about matching the field names – for example, if a column is named "Product-Name" or "productname" in the import file, this would be matched to a field called "Product Name" in your CatBase database. It is not case-sensitive. If it can't find a matching field, it will display a dialogue like this:

If you do not want to import that column, leave Ignore this field selected.
If you have a field that you want to map the column to, select the Use this field radio button and choose the appropriate field from the pop-up menu
To create a new field, select the Create a new field radio button. You'll then see the Field Properties dialogue with some of the information already completed.

Make sure that the appropriate field type is selected. For example, if the field contains numeric data such as a price, select the Real or Integer field type.

It will then display those first few records in the Sample Import Data area - for example:

If yours doesn't look anything like this - perhaps all the data appears in one column instead of being split up into a number of columns - that will probably be because the field and/or record delimiters you have specified in the Import Style do not match the field and/or record delimiters in the actual file. See below for a description of field and record delimiters.

Note the column headed Extra Field in our example. An Extra Field will be ignored by CatBase when it imports the data. If you want to map an Extra Field to an actual field, or to change the mapping of a field, simply click on the column heading and select the appropriate field from the pop-up list.

The column header changes to reflect the selected field name.

Extra fields can be useful, though. You can have any number of Extra Fields, and these will simply be ignored when you import the data. This can be handy when your import file contains data that you don't want to import into your CatBase database.

Now you are ready to ...

3. Import the data

Easy – just click on the Import Now button!

CatBase imports the data, showing a progress thermometer to let you know how it's doing. When it is finished, it will tell you how many records were imported and ask if you want to view the imported data and/or create an ad-hoc set:

If any errors were encountered, it will show you a list of the errors.

Create a Set

Choose this option to save the imported data into an ad-hoc set. This can be useful if you want to easily keep track of which records were imported, and when. For example, you might name your ad-hoc set something like "New products August 2015". You'll then be able to view that selection of records at any time simply by selecting the set name in the Sets topic on the left-hand menu.

See the Joy of Sets tutorial for detailed info about Sets.

No

Just close the dialog and return to the Import Style Sheet.

View List

Display the imported data in a new window (without saving them as a Set).

Field and Record Delimiters

In a delimited text file (also known as a plain text file), specific characters are used to determine what separates fields and records. If you think of fields as columns in a spreadsheet and records as rows, then the field delimiter is the vertical divider and the record delimiter is the horizontal divider. The two most common formats are tab-delimited and comma-delimited (CSV, or Comma-Separated Values). In each of these formats, the record delimiter is a Return character. In a tab delimited file, the field delimiter is a Tab character; in a comma-delimited file, the field delimiter is a comma. Some applications put quote marks around every field, and some put quote marks around each field, if they contain commas, in a CSV file. Some applications (such as Excel) also put quote marks around fields that contain commas in a tab-delimited file.

But with the tab- and comma-delimited formats a problem arises when the data contains text fields which have returns embedded within them (ie, text that's broken down into paragraphs). If your record delimiter is a Return character, CatBase will be tricked into thinking that the end of each paragraph marks the end of a record. To get around this, CatBase allows you to choose your own field and record delimiters. To do this, select Custom from the popup Format/source menu, and then choose the Field and Record Delimiters from the popup menus, or choose Other in the Field and Record Delimiters popups and enter the actual character or the ASCII character codes that you wish to use.

For example, we've set up a special format that uses a pipe character | for the field delimiter and a tilde ~ as the record delimiter:

Using Imported Data to Update Existing Data

You may have data in your CatBase database which you want to add to by importing additional or modified data. For example, new prices or additional data from another source. CatBase gives you lots of options for doing this via the Updates tab on the Import Style window:

Delete all records in the table before importing data

All the existing data in the table will be deleted before the new data is imported. Use with caution! Make a backup of your database before doing this!

Don't update records (only insert new data)

Choose this option if you do not want to modify any existing records - you just want to import all the data and create new records.

Update existing records

Choose this option if you want to update existing records with the data you are importing. In this case you must choose a Key Field to match the data up: normally this will be a unique record number or some other unique value such as a company name, URN, product code, etc. In the event that your key field may not be unique, you can choose a Secondary Key Field. For example, your Key Field might be Last Name and the Secondary Key

Field might be zip code or phone number.

You can then choose whether to update all fields from the import file, or only selected fields.

Other Options and Preferences

The Preferences tab offers a number of additional goodies:

Text Handling

Remove Surrounding Quotes: Some applications, such as Excel, put quote marks around fields if they contain commas when the data is saved as a text file, such as CSV or tab-delimited. Select this option to automatically remove those quote marks.

Trim Spaces: Choose this option to delete any extra spaces at the beginning or end of a field.

Convert commas in real numbers: Useful if you are importing data that originated from a system (such as some European systems) that use commas rather than dots to separate the decimals.

Convert character(s) to Returns: If Return characters that are embedded within text fields have been replaced with a special character string, this is where they can be converted back to Returns.

Character Encoding: The default character encoding is Unicode (UTF-8). However, you can select a different encoding scheme if necessary.

Tag Conversion

Sometimes the text that you are importing may contain formatting tags such as HTML tags. You can choose how to handle these.

Delete all tags: Choose this option to remove all formatting tags (ie, any piece of text that is surrounded by angle brackets, like this: <tag>).

Convert Tags: Choose this option if you want to keep the tags but convert them into a standard tagging system. For example, if your import file contains HTML tags, these can be converted to the standard system (which is, in fact based on the QuarkXPress tagging system). When you

publish the data, the tags will be re-converted, if necessary, to the appropriate tagging system for your chosen publishing destination.

Scripts

If you need some special processing done as data is imported, we can write scripts to accomplish just about anything. For example, perhaps you want to check the existence of an image, perform a calculation on the the prices, or clean up and standardize address details. Please contact us for

more information about scripts.

No. of Errors Allowed

By default, if CatBase encounters 20 or more errors whilst it is importing your data, it will abort the import and show you a list of those errors. Typical errors are non-unique data being imported into a unique field, or missing data from a mandatory field. You can change the number of errors to allow before aborting, or you can choose No Limit to keep going no matter how many errors are found.

Auto-Add new Top-Level Categories

If you are importing Category names into the MainCategoryName field, and it's possible that some of them might not already be included in your Category List, you can select this option and CatBase will create a new top-level Category if it cannot find a match. You can organise the newly added Categories into hierarchies afterwards if you need to. If you do not select this option, and an unknown category is encountered, CatBase will ask you what you want to do about it.

Importing a Spreadsheet

For a detailed Tutorial on how to import data from a spreadsheet, such as Excel, see the Importing a Spreadsheet Tutorial.

Importing from XML

See the Import from XML tutorial.

Importing from MySql/SqlServer

See the Import from MySql/SqlServer tutorial.

What's Next?

Download a fully functional, time-unlimited trial version and try CatBase for yourself

Order your copy of CatBase now!

Look at other Tutorials

Watch some training videos

Download a PDF of this page