There are 2 ways to bring codes and labels from a CSV file into a table (IVT).

Method 1 - Import Labels Into The Table After the Table is Built:

Preparing Labels in CSV Format

One way to import labels into a dimension is to prepare a comma-separated value file and import it into the field of a dimension view. Comma-separated value files are prepared outside of Beyond 20/20 and must have a .csv extension.

The comma-separated value file you create must contain a field whose values match those in a field that is already in the dimension view. This field is called the lookup field and is always the Code field. The first line of the comma-separated value file should be the case sensitive names of the fields in the dimension, one of which must be the lookup field.

For each row in the comma-separated value file, the Browser takes the Code field that you identify and uses it to search for a match in the lookup field of the dimension. When the Browser finds a match, it takes the corresponding label from the same row of the comma-separated value file and places it in the matching fields of the dimension that is being updated.

You can use any editor or spreadsheet to create a comma-separated value file. These files contain the same number of fields in each row entry, delimited by commas, and each entry has a carriage-return and linefeed character at the end of it. Most spreadsheets packages allow you to save active spreadsheets in comma-separated value format.

You can import more than one set of labels by including several sets in the comma-separated value file.

Example:

This is an example of entries in a comma-separated value file (see attachment Region.csv):

Code,LabelNA,North AmericaCA,CanadaMA,MaritimesQU,QuebecON,OntarioTO,TorontoWC,Western CanadaUS,United StatesWU,Western USCU,Central USNU,Northeast USNY,New YorkSU,Southeast USMX,MexicoNM,North MexicoCM,Central MexicoMC,Mexico CitySM,South Mexico

To prepare a comma-separated value file from a spreadsheet:

  1. In the first row of your spreadsheet, enter the field names as they appear in the Dimension view of the field you are importing labels into (i.e., the lookup field).

    Example: Enter Code in cell A1 and Label in cell B1. In the Code column, enter the complete set of item codes for the dimension you are importing labels into. In the Label column, enter the corresponding labels.

  2. From the File menu, choose Save As. The spreadsheet's Save As dialog box appears.
  3. In File Name, type or select a name for the file you want to save.
  4. Select comma-separated value (*.csv) as the file type.
  5. Click OK.

Once you have created your CSV file with column headings, e.g. Code, Label, you have to add the same fields in the dimension into which you will import the labels.

To add a field to a dimension

  1. Select the dimension you want to add fields to.
  2. Do one of the following:
    • Click the Display active dimension button on the toolbar.
    • From the View menu, choose Dimension.

  3. A Dimension view appears.

  4. From the Dimension menu, choose Add Field. The Add Field dialog box appears.
  5. In Field Name, enter the name that you want to give the new field.

    Note: To import labels, the field name must be identical to the field name in the import file, e.g. Label.

  6. In Field Type, select the type that represents the kind of data that will be stored in the field. The choices are:
    • Label - Values are textual descriptors (names) that help users understand the meaning of a code.
  7. Click OK.
    The new field displays in the Dimension view.

Importing Labels

Before using the Import command, you must first prepare a file for import. 

  • The name of the field that requires labels must match the field name in the input file that contains the labels. In the above example it must be called Label

To import labels:

Note: You must be in a Dimension view to import labels.

  1. From the File menu, choose Import. The Import dialog box appears.
  2. Do one of the following:
    • In Input Filename, enter the path and file name of the file you want to import.
    •  Click the browse button to locate and select a file to import.

  3. The Import Options area displays the available options for the particular input file type.

  4. In Lookup Field, select the field the Builder should use to match items with those in the input file. Normally Code should be selected.
  5. Select options in the Import Options area:
    • Add New Codes - Adds any new codes from the input file into the dimension. If you do not select this option, new codes will be ignored (i.e. not loaded).
    • Case Sensitive on Codes - Imports only those items that are an exact match to the codes in the lookup field.
    • Import Groups - Imports group definitions from a dimension definition file.
    • Use Windows Character Set - Imports the file as ANSI standard characters rather than OEM (DOS) characters.
  6. Click OK.

    The label field is filled with labels for those items in the input file that are also in the dimension.


Method 2 - Associate Source Fields with CSV files

You can associate source fields with CSV files so that when you drag the dimensions into your table the labels automatically appear. This is done before the table is built and before the source field is dragged into the table using Source Field Summary.

To associate a source field with a CSV file:

  1.  Do one of the following:
    From the Data menu, select Source Field Summary
    Right click on the source field tile
    The Source Field Summary Dialog appears:
  2. Do one of the following:
    • In Definition File, enter the path and file name of the file you want to import.
    •  Click the browse button to locate and select a file to import.

  3. Click OK 

Now when you drag the Region dimension into your table, the codes appear automatically:

If you view the dimension you will see the labels have been loaded too:


See Also