Formatting your Import File

The user-defined import process works when the import file is a "clean" comma-separated-values (CSV) file where recognizable field names are contained on the first line, and each subseqent line contains properly-formatted data in properly-labeled columns.

A "clean" file is free of the following:

1. End-of-Line markers within columns. These are common in Excel worksheets with multi-line cells or in files imported from Outlook. If you view these files in a text editor, you'll notice that each record with end-of-line markers ( literally, a "carriage return" character followed by a "line feed" character) are treated as multiple records. This will either cause the import program to "bug out" with an error, or at the very least it will result in a "garbage" record being imported into your database. Solution: Scroll through your import file with a text editor and correct the multiple-line records.

2. Double quotes ( " ). These wreak havoc on a comma-delimited file because they're also used to delimit fields with enclosed commas. Some systems (including ours sometimes) have trouble parsing records with double-quotes. Solution: It's best to get rid of them altogether. In Excel, highlight the entire worksheed and then do a simple Find-and-Replace, replacine double-quotes with blanks.

3. Columns with varying data types. The import program assumes that every record in the file contains the same type of data in each column. This seems obvious but if you scroll through the import file in Excel and discover that a column that started out displaying, say, a zip code, turns into the second address, you can safely assume this will create problems with the import.

4. Subtotal and Total records. If you're importing financial records, check the import file and remove any lines representing totals. Otherwise you'll be importing a "garbage" record.

5. Commas and currency marks in numeric fields. Remove them in Excel by appropriately formatting the column.

6. Unrecognizable dates. The import program tries, but doesn't always, properly format an uncommonly-stored date. Use the most common date format, which is MM/YY/DDDD.

7. Strange characters at the end of the file. Using a text editor, go to the bottom of the file. Hit the <Backspace> key until the cursor is blinking at the end of the last valid line. Hit <Enter> and you've properly formatted the end of the file, which should be nothing but an end-of-line mark.

8. Company and Last Name in the same column. This is common where the file contains both individual and organization records. If the field is marked as Last Name, and the import file encounters an organization record, it will import that record as an individual. If the Last Name and Organization Name (Company) are in different columns, the system will import the record as an organization if the name fields (last name, first name, middle, etc) are blank.

Before importing the file, replace the appropriate column headings with common field names that will be automatically recognized by the system. This will lessen the possibility of running the import but forgetting to mark all the fields. (Anything unmarked will be ignored by the system.)