Importing Data from External Sources
Previous Topic  Next Topic 

Although the ability to import data is a common feature for some types of software, this is often limited to very simple "list" types of contact data (client list, vendor list). In order to import data into a design project, a much more complicated type of import process is often needed. This requires that you address the kinds of structure differences and data corruptions that can occur in the source data, especially if that source data was maintained in a spreadsheet. 

In addition, the various ways that different companies organize their data (how they put together things like budget codes, vendor IDs, etc.) can make it harder to simply "map" a field or column in the source data to a field in the database.

Finally, people using manual techniques often develop shortcuts that make sense in that context, but simply won't work in a database. For example, they may write a single spec for an object that uses one color in one location and a different one in another, and simply note on the spec how many go in each location. Since FF&EZ requires that you actually place objects in the locations where they are to be installed, you must create two separate objects if their specs are not identical (which is easy to do).

As a result, the process of exporting complex project data from one system and importing it into another can require programming skills that are beyond the skill set of most people in the design professions (and if the source data is not at least arranged in simple rows and columns, the process can be prohibitively expensive, no matter what skills are available). 

Fortunately, FF&EZ has a solution.

This introduction contains important information about how this challenge is handled in FF&EZ. If you want quick access to other parts of the import reference, use these links:

Basics / Data Preparation

Spreadsheet Formula Examples

Import Field List

Using the Import List Utility

FF&EZ's Practical Solution

After many years of helping clients manually import data, we have found a process that allows data conversions that can use common business tools and only need the software skills that are often possessed by at least one person in a modern business. 

FF&EZ's external data import utility, the Import List command appears on the FF&E Worksheet and the Specifications List screens. It is used to import properly pre-formatted external FF&E data into the currently open project. This can be in one of two major forms: 1) Specifications (only) and 2) a fully defined project structure containing areas, rooms, objects and specifications. 

You can also import just a list of areas and rooms, using a simple technique. See Importing a List of Rooms at the end of this section.

Here is the basic process (details are discussed later):

  1. Export your source data into a format that can be transferred into a spreadsheet (if not already in a simple spreadsheet table format). 
  2. Use the spreadsheet's basic functions and formula capabilities to reorder the data, clean up illegal characters in the text and merge, divide or concatenate data if needed. This is typically done using formulas on a second sheet in the spreadsheet to "pull" the raw data into the final format, row by row.
  3. Save the "clean" spreadsheet data (and only the data) on the second sheet into a simple text file, using the column order required by FF&EZ. This file must use the "tab-delimited" format to separate the columns.
  4. Import the text file into an FF&EZ project. 

If your project is already "in progress" and you are just learning how to import, we strongly suggest that you import into a new temporary project, then use the Room List screen's Add> Import command to import the results into your active project. This command screens all incoming data for duplicate object Tags and Spec IDs, giving you better control if a mistake was made in assigning these in your import source. You can also use the Object List and/or Specification List screens' Add> Import command to import individual items.

The most labor-intensive part of this process is the cleanup of the data, especially data that started in a spreadsheet, since users of spreadsheets have a tendency to add non-standard entries or entries that exceed the size of the database fields into which they will eventually be imported. However, once you understand the requirements of your data and create a spreadsheet template based on office standards, future imports (if needed) will be much easier.

Note that it is possible for someone to export data directly into the text format needed by FF&EZ or even directly link the data with ODBC. This skips steps 2 and 3 above but, as we mentioned, usually requires a trained programmer to satisfy the requirements that follow.

Basic Requirements

Whenever you import data into a database, there are some simple rules that are fairly universal for importing data in a row-and-column format:

  • Each "record" consists of a line of data "fields" and ends in a single "carriage return / line feed" which is also referred to as a "CR/LF".

    The CR/LF actually consists of two non-visible characters created when you press the [Enter] key on the keyboard in a word-processor (or something like [Ctrl-Enter] in a spreadsheet cell). This line of data is equivalent to a single row in a spreadsheet, while the fields are equivalent to the cells in that single row.
  • Data fields may be of different "types" such as numbers, integers, text, dates or "logicals" (true or false) The "structure" of the data file is expressed by the left-to-right order of the columns and the type of data in them.
  • Each non-blank cell of a column is expected to contain the same type of data (text, numbers, dates, etc.).
  • Most fields (columns) are restricted as to how many characters they can hold. Logicals are normally one character, with "T" for "true" and "F" for "false," but may also be represented by 1s and 0s, "Y" and "N" or by just one of those "true" values with a blank entry interpreted as "false". 
  • If "embedded" carriage returns or simple line feeds occur in the middle of an imported data row, an import utility will treat what follows as a new line, corrupting the import. (Embedded line feeds are those you can enter into a single spreadsheet cell with [Ctrl-Enter] (sometimes [Shift-Enter]) to break to a new line within the cell). The rule: A CR, a LF or a CR/LF always means a new record in a database import, so you must avoid having them in the data, except a final CR/LF at the end of each row (you don't have to enter this yourself). Fortunately, there are ways to deal with embedded CR/LFs, as we will show you.
  • Some databases (such as FF&EZ) support variable length character (text) fields that contain embedded carriage returns, however, the rules about importing such data (explained above) still apply. That means that embedded carriage returns must be temporarily converted to some other "symbol" that can be imported without causing unexpected results. FF&EZ will automatically convert the "symbol" denoted by the HTML "break" code "<BR>" to an embedded CR/LF after importing variable length data like descriptions.
  • Character data that is too long for the field into which it is being imported will be truncated. This means that you may have to transfer some data into a "catch all" column such as a variable-length description. This might be done manually or with a formula, if there is a distinct pattern in the too-long cell entries in that column.
  • A number that is too long produces a "numeric overflow" error, which leaves a series of asterisks (****) in the field. Character data that is mistakenly imported into a numeric or date field will leave the field blank or zero, while numbers being imported into a character field will be simply treated as text (and truncated at the end if the number is too big for the field width).

FF&EZ's Import List utility has an option that will create a pre-formatted text file that you can use as a template to satisfy the structure it needs. Once you understand these basic requirements, the next steps will be easier to understand.

Importing a Room List (Only)

If you have an extensive list of areas and rooms that you want to import, but do not have any objects to include in them, you can import the list by following the instructions for importing room contents (the second of the two main import options) and enter a single "dummy" object in each room:

  1. Create a spreadsheet in the format layout required for the "room contents" option (see the instructions for this in the next section).
  2. Create one row for each room, including all the required fields such as Area ID, Room ID, etc.
  3. In the columns dealing with product specs, enter the same spec ID and description in each row (and any other required fields), copying this information downward with the usual spreadsheet functions. For instance, you can create a Spec ID of "DUMMY01" or "INIT01" with the description "Temporary Item".
  4. Follow the rest of the instructions for creating the import source text file and import it. This will populate the project with the desired areas and rooms. Each room will have one entry, which is your temporary item.
  5. You can either leave the temporary item and later edit it into a "real" object and product, or you can use the Object List screen's Delete command to remove it and the associated spec from the project.

Next: Basics / Data Preparation