Overview of Data Importing
There are just a few steps to importing data into your CatBase database:
- Create a new Import Style
- 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, click on the Import Data button on the main window. If there are no Import Styles already in the database, you'll see a dialogue asking if you want to set one up: click on Yes Please. If there is already at least one Import Style, instead you'll see a popup menu listing your existing Import Styles: choose Add a New Import Style from the bottom of the menu.
2. Specify your Options
A new Import Style looks like this:
2.1 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.
2.2 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 the Import Data Formats tutorial. For the moment we will assume that you have chosen the default option: Tab-delimited.
2.3 Choose the table that you're going to import data into from the Import Into Table popup menu. This menu lists the tables you've set up in your database, plus a few others, which are described below. For example, in our Demo Database the choices are:
- Photographers and Pictures are our data tables.
- Categories Level 1, 2, and 3 are the Category tables.
- Category Entries is a special table that links individual records to Categories. (For the technically minded, this enables a many-to-many relation between the Categories and the data tables).
- Table to Table Link is a special table that links records in one data table to records in another data table. For example, in our Demo Database there is a relationship between Photographers and Pictures: each Photographer can have any number of Pictures. A list of each Photographer's Pictures is displayed on the Pictures tab of a Photographer's record. The Table to Table Link is what tells CatBase which Picture records are related (linked) to which Photographer records. You will probably not need to be concerned with this table; normally it just sits quietly in the background and you don't have any direct interaction with it. It's just there as an option for advanced users.
After you've selected the import table, the left-hand part of the window changes and shows you a list of the fields in the selected table:
(Note that this will appear rather different if you are importing an XML file or from an ODBC source - these are all described in the Import Data Formats tutorial). The field names in your database may be different to those shown here.
3. Import some Sample Data
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, reads a few records, and tries to match the fields in the import file to the fields in the import table. For delimited text files, this works best if the field names are included in the first row of data. The program is quite intelligent about matching the field names – for example, id a field 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.
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 delimiters you have specified in the Import Style do not match the field and/or delimiters in the actual file. See below for a description of field and record delimiters.
CatBase has matched up most of the fields, but what's this "Extra Field "? This is one that it couldn't match up, because in our import file the field is called "Photographer" whereas the field that it should match in the database is Photographers Name. No problem: Find Photographers Name in the list of Our Fields, click on it, and drag and drop it onto the Extra Field column in the Sample Import Data area:
(Note that you drop it into the column data, not on the column header).
The column header changes to reflect the appropriate 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 ...
4. 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 show you the imported records in a new window (for data and Category files; for linking records it will just tell you how many were imported). If any errors were encountered, it will show you a list of the errors.
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). 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. Some applications (such as Excel) also put quote marks around fields that contain commas in a tab-delimited file. If your source data is an Excel file, you can choose Save As ... from the File menu and select Text (Tab delimited) or CSV (Comma delimited) in the Format popup menu to save your spreadsheet in one of those formats.
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 form:
- Delete all records in the table before importing data: Choose this option if you want to import a completely fresh set of data
- Don't update records: Choose this option if you do not want to modify any existing records, and only to save new ones.
- Update existing records: Choose this potion 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, 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:
Character Translation
- Convert Mac to PC Character Set or PC to Mac Character Set: Choose one of these options if you are working on one system but the data you're importing came from the other system. This will ensure that special characters, such as umlauts and such, will be converted to the correct characters.
- Remove Surrounding Quotes: Some applications, such as Excel, put quote marks around fields if they contain commas. 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.
Tag Conversion
Sometimes the text that you are importing may contain formatting tags such as HTML tags.
- Delete all tags: Choose this option to remove all formatting tags (ie, any piece of text that is surrounded by angle brackets, like this: <b>).
- 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,, of clean up and standardize address details. Please contact us for more information about scripts.
What's Next?
