Importing Terms from CSV Files
Overview
SAS Business Data Network
supports importing lists of terms stored in comma-separated-value
format (CSV) files. You should create these term lists in Microsoft
Excel and export them to CSV files. You should avoid entering terms
directly into a CSV format using a text editor. Plain text editors
do not support putting a newline character in the cell for attributes
like description, requirements, or multi-line and rich texts.
Importing Terms from CSV Files
-
Display the main window for SAS Business Data Network, if you have not done so already.
-
In the Action menu beside the New Term icon, select Import Terms.
-
Select or clear the Publish import results to relationships service? check box in the Import Terms window.The check box and its explanatory message are shown in the following figure:Publish to Relationships Service
Clearing this check box prevents the import operation from publishing import results to the Relationship service. This approach improves performance when you import a large CSV file. If relationships are not updated, the terms and tags in the imported file might not be added to SAS Lineage. Also, future updates to the terms or their attributes are not reflected in the Relationship service. However, the Relationship service can be updated later by running the batch command line program described in Command-Line Processing for Imported Terms. -
Navigate to the folder that contains the CSV file to be imported.
-
Select the CSV file. The file that you select must have an .csv extension and a name that is encoded in UTF-8.
-
Click Open. The terms in the file are imported into the current SAS Business Data Network.
Rules for Importing CSV Files
Overview
Specific rules have
been developed for the SAS Business Data Network tool for importing
CSV files. Therefore, you must inspect your CSV files before you import
them using the tool. Otherwise, you might not get the results that
you expect. The import operation might fail, or data might be imported
into the wrong places in the SAS Business Data interface.
These rules can be
divided into the following types:
General CSV Formatting Rules
General CSV formatting
rules apply to CSV import in SAS Business Data Network. For example,
values with multiple embedded commas must be enclosed in double quotation
marks. Multiline values must also be double quoted. Applications such
as Excel enforce some of these rules when they export data to CSV
files.
Examine your CSV files
for compliance with the following formatting rules:
Values with multiple embedded commas
must be enclosed in
double quotation marks.
Multiline values
must be enclosed in
double quotation marks. Note that the multi-line fields in Excel files
are not supported by the SAS Business Data Network CSV import tool.
Types and paths
When type of a term
is not specified, the Default term
type is used.
Paths can use only
a backslash as a separator. Using a forward slash or any other character
as a separator is invalid. For example, State Health
Plan\Benefit Focus is a valid path. State
Health Plan/Benefit Focus and State
Health Plan*Benefit Focus are invalid paths. If
the path is not found, an error is displayed to the user and the term
is not imported.
The path to a term
can end at either the parent term or the term name. The path for top-level
terms can be empty. For example, the following types and paths are
valid:
The following table
illustrates valid type and paths in a CSV file:
|
Name
|
Type
|
Path
|
|---|---|---|
|
State Health Plan
|
Empty Cell
|
(No path specified for
this top-level term)
|
|
Benefit Focus
|
Empty Cell
|
State Health Plan (Path
extends to parent term)
|
|
United Health
|
Empty Cell
|
State Health Plan\United
Health (Path extends to term)
|
The empty cells in
the Type column fall back to the default value.
Case sensitivity
User-defined attribute
names are case sensitive. However, system column names are not case
sensitive.
In the following header
row, the names Name, Type,
and Path in the first three
columns are system columns unaffected by case.
|
Name
|
Type
|
Path
|
Name
|
Division
|
Rating
|
division
|
raTing
|
However, the columns
named Rating and raTing and Division and division are
user-defined attributes that are case sensitive. Therefore, these
columns are treated as four separate user-defined attributes. The
difference in case between them marks them as individual entities.
Names
Names cannot contain
a backslash, but forward slashes are permitted. For example, BCBC/Financial is
valid, but BCBS\Financial is
invalid.
Column Rules
Examine your CSV files
for compliance with the following column rules:
System attributes and system columns
Columns with the following
names are eligible to be designated as the system attributes: Name,
Type, Path, Description, Requirements, Status, Importance, CreatedByUser,
ModifiedByUser, Version, and VersionId. The first occurrence of any
of these column names in the header is designated as the system column.
Subsequent occurrences of the same column name are treated as user-defined
attributes.
The column named Name in
the first column is the system column. The column named Name in
the fourth column is a user-defined attribute. The columns named Type and Path are
the only instances of these columns and are treated as system columns.
|
Name
|
Type
|
Path
|
Name
|
|---|---|---|---|
|
Data object
|
Empty Cell
|
Empty Cell
|
Data object2
|
The Name system
column (Data object) is displayed in the Terms list in SAS Business
Data Network when the CSV file is imported. The Name user-defined
attribute (Data object2) is displayed in the Attributes field
in the Identification tab.
When there are multiple
instances (columns) of a user-defined attribute, the final occurrence
is treated as a user-defined attribute.
|
Name
|
Type
|
Path
|
Name
|
Name
|
Name
|
|---|---|---|---|---|---|
|
Data object
|
Empty Cell
|
Empty Cell
|
Data object2
|
Data object3
|
Data object4
|
The first Name column
(Data object) is treated as the system column. The final Name column
(Data object4) is treated as a user-defined attribute. The other Name columns
(Data object2 and Data object3) are ignored when the CSV file is imported.
Number of columns
The number of columns
in a data record must be equal the number of columns in the header.
When a data record has more columns than the number of columns in
the header, all of the values in the extra columns are ignored.
The header row for
the following table contains Name, Type, Path,
and Description columns:
|
Name
|
Type
|
Path
|
Description
|
|---|---|---|---|
|
State Health Plan
|
Empty Cell
|
Empty Cell
|
Empty Cell
|
However, suppose that
the data record contains one or more extra columns. This file can
be displayed as follows in a text editor:
Name, Type, Path, Description State Health Plan,,, State Health Plan
The first line of the
file defines the four columns in the file header: Name, Type, Path,
and Description. The second line of the file forms the first data
record of the file. The three commas in this data record represent
three empty columns that fill the Type, Path, and Description columns
in the first data record row in the table. Therefore, the text that
should fill the Description column in the table (State Health Plan)
is ignored.
Extra columns that
are not included in the header row are simply ignored. However, if
the data columns have fewer fields than the header row, then the file
is invalid. If a row in the file shown in the table above had only
three columns, it would be invalid because the header row contains
four columns.
Column header
must be contained in
the first line of the CSV file.
Mandatory columns
All CSV files must
contain Name, Type, and Path columns. These mandatory columns must
be defined in the header and can be listed in any order.
Both of the following
tables are valid:
|
Name
|
Tags
|
Path
|
Type
|
|---|---|---|---|
|
State Health Plan
|
A,b
|
Empty Cell
|
Empty Cell
|
|
Tags
|
Name
|
Type
|
Path
|
|---|---|---|---|
|
A,b
|
State Health Plan
|
Empty Cell
|
Empty Cell
|
Base columns and system attribute columns
Base columns must be
present in the CSV file. System attribute columns are optional. The
CSV file can be imported with or without these columns.
|
Column
|
Are Blanks Allowed?
|
Valid Values
|
Example
|
Value WhenBlank
|
|---|---|---|---|---|
|
Name
|
No
|
Empty Cell
|
Empty Cell
|
Error
|
|
Type
|
Yes
|
An exact match to a
SAS Business Data Network term type
|
Empty Cell
|
Default
|
|
Path
|
Yes
|
Empty Cell
|
Term1
Term1\Term2\Term3
|
Term becomes root term
|
|
Column
|
Data Type
|
Are Multiples Allowed?
|
Value When Blank
|
Value Not Matched in
CSV
|
|---|---|---|---|---|
|
Requirements
|
Multi-line text allowed
with line return
|
No
|
None
|
Not applicable
|
|
Importance
|
Should be one of those
set on the term type (case insensitive)
|
No
|
Picks up default from
term type
|
Empty Cell
|
|
Status
|
Should be one of those
set on the term type (case insensitive)
|
No
|
Picks up default from
term type
|
Empty Cell
|
|
Tags
|
Empty Cell
|
Yes
|
None
|
New tag created
|
Attribute Rules
Examine your CSV files
for compliance with the following attribute rules:
Custom user-defined attributes
You can define user-defined
attribute types for the term types in SAS Business Data Network. (See Review or Edit a Term Type for more information.)
When you import these user-defined attributes in a CSV file, the
column name in the CSV file must match the attribute name defined
in the SAS Business Data Network term type. If a default value is
defined in the term type, then the user-defined attributes for the
term are set to the default value from the term type. If validation
fails, then the value is blank.
The following table
lists the user-defined attribute types that are available for SAS
Business Data Network term types and describes their characteristics:
|
Attribute Type
|
Column Name
|
Column Value
|
Validation
|
Invalid
|
|---|---|---|---|---|
|
Single Line Text
|
Defined in term type
|
Single line of text
|
None
|
Not applicable
|
|
Multi-Line Text
|
Defined in term type
|
Multiple lines of text
(ALT+ENTER in Excel)
|
None
|
Not applicable
|
|
Single Select
|
Defined in term type
|
One of the values defined
in term type must match case
|
Set if a matching value
found in list
|
Not Selected
|
|
Multi-Select
|
Defined in term type
|
One or more of the values
defined in term type. Separated by commas. Case-sensitive.
|
Set if a matching value
found in list
|
Not Selected
|
|
Boolean
|
Defined in term type
|
Yes, No, Y, N, True,
False
|
Default
|
Default
|
|
Date
|
Defined in term type
|
Accepted format isYYYY-MM-DD.
Other formats produce unexpected results.
|
Default taken, if specified
|
Blank
|
|
URL
|
Defined in term type
|
Valid URL format
|
Empty Cell
|
No validation done during
CSV import
|
|
Rich Text
|
Defined in term type
|
<html><BODY><P
align="left"><B><U>AT2 a BOLD and Underline</B></U></P>
|
Empty Cell
|
No validation performed
|
If the term type supports
new user-defined attributes, then any attribute that is not defined
for that term’s term type is imported as a user-defined attribute.
The user-defined attributes
that you add use the following format:
Value|Instructions|RequiredThe column name is the Attribute name.
Complex attributes
The following items
have been designated as complex attributes: Tags, Links, Related Terms,
Associated Items, Notes, and Contacts. You can also think of them
as system attributes that correspond to elements present in SAS Business
Data Network.
The data for a term
can include one or more complex attributes. If multiple complex attributes
are present, each attribute must be contained in a single cell of
the data record and be separated by commas. Each of these attributes
is defined by various properties. Those properties must be separated
by a pipe symbol (|). For example, links use the following format:
label | URIHere is an example of a link in this format:
cnn| Http://www.cnn.comWhen you include multiple links, separate them with commas. Then surround them in quotation marks in the CSV file to indicate that they are part of a single column, as follows:
"cnn| Http://www.cnn.com, newsweek | Http://newsweek.com"
Additional descriptions
and examples of these complex attributes are provided in remaining
CSV import rules.
Tags
All tags must be defined
in a single column. The individual tags must be separated by commas.
Tag has only one property, which is Name.
The following table
shows tags added to a term:
|
Name
|
Type
|
Path
|
Tags
|
|---|---|---|---|
|
State Health Plan
|
Empty Cell
|
Empty Cell
|
Health Care Providers,
Pharmacy Providers
|
Links
All links must be defined
in a single column. The individual links must be separated by commas.
The link properties are Label and URI. They must be separated by the
pipe symbol (|).
The following table
shows links added to a term:
|
Name
|
Type
|
Path
|
Links
|
|---|---|---|---|
|
State Health Plan
|
Empty Cell
|
Empty Cell
|
Cnn headlines|http://www.cnn.com,
msn| www.msn.com
|
Related Terms
All related terms must
be defined in a single column. The individual related terms must be
separated by commas. The related term properties are Path and Label.
They must be separated by the pipe symbol (|). The Path must be a
valid path to the term. Label is not required.
The following table
shows related terms added to terms:
|
Name
|
Type
|
Path
|
Related Terms
|
|---|---|---|---|
|
State Health Plan
|
Empty Cell
|
Empty Cell
|
CEDS|ceds,_ANew/B3 |
newB3invalid
|
|
Benefit Focus
|
Empty Cell
|
Empty Cell
|
CEDS|ceds,_ANew/B3
|
The first file depicted
in the table contains two related terms. Both of them have labels.
The second file in the table also contains two related terms, but
only the first one contains a label.
Associated Items
All associated items
must be defined in a single column. The individual associated items
must be separated by commas. The required associated items properties
are Name, ID, and Type. Description, Notes, and Path are optional
properties. The properties must be separated by the pipe symbol (|).
The order of the properties that you define is important.
The associated items
properties must be entered in the exact order that is shown in the
last paragraph. Therefore, all associated items must begin with the
three required properties, which are Name | ID | Type. If you need
to use optional properties, add them to the end of the required list
and provide a blank space for any property that you omit. For example,
an associated item that includes the optional notes and path properties
but not the description property would be structured as follows: Name
| ID | Type | | Notes | Path.
The following table
shows associated items added to a term:
|
Name
|
Type
|
Path
|
Associated Items
|
|---|---|---|---|
|
State Health Plan
|
Empty Cell
|
Empty Cell
|
"Abort|A59KZUAD.B7000001/Action|Action|Description|
Notes, notes"
|
The associated item
in the file depicted in the table is enclosed in double quotation
marks because the Notes portion of the associated item contains a
comma.
Notes
All notes must be defined
in a single column. Individual notes must be separated by commas.
The note properties are Content and CreatedByUser. They must be separated
by the pipe symbol (|). If the user is omitted, or not in the system,
then the name of the user who is performing the import job is used.
The following table
shows notes added to a term:
|
Name
|
Type
|
Path
|
Notes
|
|---|---|---|---|
|
State Health Plan
|
Empty Cell
|
Empty Cell
|
Somenotes|someuser
|
Contacts
All contacts must be
defined in a single column. Individual contacts must be separated
by commas. The contact properties are User and Role. They must be
separated by the pipe symbol (|). Text matching is done in the role.
The role is case sensitive and must match the set values. Otherwise,
the contact will not be created. The contact does not have to exist
in the system.
The following table
shows contacts added to a term:
|
Name
|
Type
|
Path
|
Contacts
|
|---|---|---|---|
|
State Health Plan
|
Empty Cell
|
Empty Cell
|
login| Business Owner,
login2|Network Admin
|
Example
This example shows an
Excel file that has been exported to a CSV file and then processed
for importing into SAS Business Data Network. Then it shows where
the data in the file has been added in the SAS Business Data Network
interface.
The following display
shows a portion of the exported CSV file:
CSV File Exported from Excel
When CSV terms are
imported into SAS Business Data Network successfully, you can see
them in the Terms list, as shown in the following display:
Imported CSV Terms
You can see how the
terms flow into SAS Business Data Network if you compare the formatted
columns in the CSV file to the imported file in SAS Business Data
Network.
For example, the formatting
in the first row in the links column of the CSV file is shown in the
following display:
Links Column in the CSV File
The label and URL for
each link are separated by a | (pipe) symbol. The two links also are
separated by a comma. Both of these formatting choices are specified
in the Importing Terms from CSV Files and are needed
to ensure that the links are properly displayed in the SAS Business
Data Network.
The following display
shows how the links are shown in the Identification tab:
Links Displayed in SAS Business Data Network
Associated items follow
the same pattern.
The formatting in
associated items column of the CSV file is shown in the following
display:
Associated Items Column in the CSV File
The associated item
in the first row is enclosed in double quotation marks because the
Notes portion of the associated item contains a comma. The individual
associated items are separated by commas, and the properties are separated
by | (pipe) symbols.
The following display
shows the Associated Items tab in SAS Business
Data Network:
Associated Items Displayed in SAS Business Data Network
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: June 7, 2017