"	this is a Text Qualifier (just a double quotation mark). A Text Qualifier is what surrounds the data for each field to tell your import program which characters to put in the database field. Text Qualifiers are not always used/needed, but in general they are almost always used.
,	this is an example of a Delimiter... a comma, a space, a tab.  A Delimiter is simply something input into the file to say where two fields in the database diverge.

 

"Bob","Jones","Jr" "Mary","Williams",""	These are examples of Comma Delimited entries. Notice that there are two sets of quotes (Text Qualifiers) with nothing in between (where Bob's record has "Jr")? This simply means this field for this record is empty (Mary doesn't have a suffix like Jr, Sr, III, etc)
"Bob"     "Jones"   "Jr" "Mary"    "Williams"     ""	These are examples of TAB Delimited entries.  The spaces between the fields are created by the “Tab” key on your keyboard.

 

Space delimited is rarely used these days.

 

Both Comma and Tab Delimited with Text Qualifiers will easily (and almost automatically)

import into nearly database software program.

 

 

This is an example of FIXED FIELD (also called SDF for Standard Data Format, and "Pipe Delimited")

You can recognize this type of file right away, because for every record (each line) everything lines up perfectly with the records above it and below it.  No Text Qualifier is used, no Delimiter is used.  For this file type, POSITION IS KEY.

Bob             Jones             Jr            123 Main Street        Sunny          CA92121

Mary            Williams                        987 Other Way          Rainy          WA98675

Jonas           Thomas            Sr            654 Silly Lane         Crazytown      NE48537

 

In the case of a file like this, you will need a Structure Layout (sometimes called just a Layout, or a Field Layout).  Whatever you call it, it acts as a mapping key, to tell you where one field ends and the next begins, what kind of data is in the field, the order of the fields, and so on.  Structure Layouts vary a little bit depending on what software program they are generated by, but they all contain at least the bare minimum of information you need to import a file of this type.

 

Here are two examples of a structure layout:

 

In this example, you are told which order the fields are in (Field 1, Field 2 etc)

You are told the name of the field (Job_nbr, KEY_CODE, etc).  You don’t have to name your fields the same thing, but this is sometimes your only clue to what info is in that field.

You are told the Length of the field (how many positions it uses… for example the field containing the two digit state abbreviation State_Province is 2 positions long).

You are also told what position the field begins at, and what position it ends at. *This info is not always provided, and is not necessary, but can sometimes be helpful.

========================================================================

 

    Field    Fieldname                   Length      Start Position     End Position

       1     Job_Nbr                        10               1                10

       2     KEY_CODE                       10              11                20

       3     PROMO_CODE                     10              21                30

       4     Party_Id                       12              31                42

       5     HouseHold_ID                   12              43                54

       6     State_Province                  2              55                56

       7     Postal_Code9                    9              57                65

       8     Addressline1                   30              66                95

       9     Addressline2                   30              96               125

      10     First_Name                     25             126               150

      11     Middle_Initial                  1             151               151

      12     Last_Name                      30             152               181

      13     City_Name                      30             182               211

      14     Business_Name                  50             212               261

      15     SEG_ID                         12             262               273

      16     Birth_Date                      5             274               278

 

 

In this example, you are told the field order, the field name, the field Width (same as length in example above), and the Type.

The Type column tells you what sort of data they’ve allowed in that field.

“Character” allows anything you can input via the keyboard by pressing one or two keys.  Numbers, Letters, symbols like # and * etc.

“Numeric” means that only numbers are allowed, AND that this field is treated as a number.  For example, you don’t want a field for Zip Codes treated as “numeric”, otherwise, an entry for a zip code on the East Coast, like Bristol, Connecticut, which is 06011… would be treated as the number 6011.  Like you learned in regular kindergarten, leading zeroes don’t mean anything.  So you always want a zip code field, and any other field that could have important leading zeroes, treated as Text (aka Character).  Also, Numeric fields allow decimal points, shown in the “DEC” column if used.  The number there tells you how many decimal places are allowed.  “2” would represent 2 spots after the decimal point, like in “20.13”.

There are other field types rarely seen, such as “DATE”… usually these others are pretty self explanatory.

------------------------------------------------------------

Field  Field Name  Type       Width    Dec    Index  Collate

    1  PREFIXTTL   Character      4                 

    2  INDIVIDUAL  Character     63                 

    3  FIRSTNAME   Character     30                 

    4  MIDDLENAME  Character      1                 

    5  LASTNAME    Character     30                 

    6  ADDRESS     Character     49                 

    7  ADDRESS2LI  Character     33                 

    8  CITY        Character     30                 

    9  STATE       Character      2                 

   10  ZIP         Character      5                 

   11  ZIP4        Character      4                 

   12  DPBC        Character      3                  

   13  COUNTY      Character      3                 

   14  CRRT        Character      5                 

   15  DSFWALKSEQ  Character      4                 

   16  ESTIMATEDI  Numeric        10   2              

   17  AGEOFINDIV  Character      2                 

   18  ADDRTYPEIN  Character      1                 

** Total **                     280

 

You should ALWAYS request a structure layout with your data.  This prevents you from having to go back and ask for it later, and many times it can answer lots of your importing questions without ever having to pick up the phone or wait on your vendor.

 

FIELD HEADINGS

Field Headings are most simply, the name of the database field (each column).  When you receive a database from an outside source, or export a database of your own, sometimes you get field headings in the file, sometimes you don’t.  In the example below, the words in bold are the Field Headings.

 

FIRST         LAST          FULLNAME           AGE           GENDER     LOR

Lori          Bobori        Lori Bobori        63            F         2

Jackie        McFee         Jackie McFee       21            F         7

Jason         Funnybones    Jason Funnybones   47            M         0

 

In the example above, if your file did not come with the field headings already in place, or the

Structure layout, you could be in a pickle… you wouldn’t readily know what to do with the data in the last three fields… it could be anything!  Especially consider the implications when you have 25 or 30 different fields in the database… it starts to look like gibberish pretty quickly.  Just another reason to always request a layout with your orders.

 

File Extensions

A file extension is the set of characters after the period in a file name.  For example .txt or .csv or .dbf or .xls or .jpg or .mp3.  The most common extension used for Comma Delimited, Tab Delimited and Fixed Field files is .txt (which stands for “text”).  Microsoft made up their own extension for Comma Delimited files used in Excel .csv (stands for Comma Separated Values).  And to make it more fun, some service bureaus will create their own extensions out of thin air (sometimes in an attempt to thwart your email virus scanner so it doesn’t prevent you from receiving the file).  You might see .dat or .mer or whatever.  What to do?  Simply download the file and then, in Windows Explorer (My Computer) simply right click on the file in question, select “Open With” from the pop-up menu, then select to open it with NOTEPAD. Now that you know how to identify the file format on sight… just take a peek, decide if it’s in Comma Delimited or Tab Delimited or Fixed Field, and proceed from there.  You can then close NOTEPAD, and rename the file with the extension that your particular program likes to use FOR THE FORMAT THE DATA IS ACTUALLY IN.  If you determine that it’s comma delimited, and you are opening it with Excel, simply rename the file with a “.csv” extension.  If you are using some other program, it’s easiest to use the .txt extension since almost every program will recognize that (.txt will work for all 3 file types).  You CANNOT simply change the extension and have the format change as well… like from Comma Delimited to Fixed Field.  Changing the extension simply allows your particular software program to recognize that the file exists while you are trying to import.