Constructing data lines in CSV-formatted files

Every CSV-formatted file that you import into PolicyCenter must have a heading line, followed by one or more data lines. The import_tool identifies data lines by scanning the file for lines with the following characteristics:

  • The line does not contain the three required heading fields.
  • The line contains comma-delimited values.
  • The line contains a third field that is non-empty.

Each data line represents a single instance of a data model entity.

Data line - field 1

The first field in any data line must be an entity name or an entity subtype name.

1: Policy
2: type,data-set,entityid,account,corepolicynumber,policytype,producttype,productversion,
       systemofrecorddate,,,,,,,,,,,
3: Policy,0,ds:1,ds:1,34-123436-CORE,wc,wc_workerscomp,1,1/1/2002,,,,,,,,,,,
4: Policy,0,ds:2,ds:1,25-123436-CORE,bop,bop_businessowners,1,1/1/2002,,,,,,,,,,,
5: Policy,0,ds:3,ds:3,54-123456-CORE,personalauto,pa_personalauto,1,1/1/2002,,,,,,,,,,,
6: Policy,0,ds:4,ds:4,25-708090-CORE,bop,bop_businessowners,1,1/1/2002,,,,,,,,,,,
7: Policy,0,ds:5,ds:2,98-456789-CORE,bop,bop_businessowners,1,1/1/2002,,,,,,,,,,,
8: Policy,0,ds:6,ds:1,20-123436-CORE,businessauto,ba_businessauto,1,1/1/2002,,,,,,,,,,,
9: Policy,0,ds:7,ds:1,50-123436-CORE,umbrella,u_umbrella,1,1/1/2002,,,,,,,,,,,
...

In lines 3 - 9, the entity name Policy appears in the first field as required. The capitalization of an entity or subtype name must be identical to that used in the Data Dictionary. For example, to create a RevisionAnswer data line the entry name would be invalid if you specified it as revisionanswer.

Data line - field 2

The second field in a data line is the value of the highest-numbered data-set of which the imported object is part.

2: type,data-set,entityid,account,corepolicynumber,policytype,producttype,productversion,
      systemofrecorddate,,,,,,,,,,,
3: Policy,0,ds:1,ds:1,34-123436-CORE,wc,wc_workerscomp,1,1/1/2002,,,,,,,,,,,

PolicyCenter orders data-sets by inclusion. Thus, data-set 0 is a subset of data-set 1 and data-set 1 is a subset of data-set 2, and so forth. It is possible to request a particular data-set while converting CSV to XML. By default, PolicyCenter requests data-set 10240. PolicyCenter assumes that data-set 10240 includes every data-set that it is possible to create.

You can leave the second field blank, in which case PolicyCenter always includes this object in the import regardless of the requested data-set.

Data line - field 3

The third field in any data line must be the public ID for that particular data object. This field is mandatory. For example, ds:2 is the public ID of the Policy on line 4.

Foreign key and column data

The import_tools command imports both column and typelist data values from the CSV file. In the previous example, the policytype column has a value of wc in line 3 and a value of bop in line 4. You represent foreign key data by a string in one of two formats:

publicID

or

entity_id:identity_source

If there is more than one : (colon), the import ignores everything after the second : (colon). 

1  ADDRESS 
2  type,data-set,entityid,addresstype,addressline1,createuser
3  Address,0,ab:1001,home,1253 Paloma Ave.,import_tools
4  Address,0,ab:1002,business,325 S. Lake Ave.,import_tools
6
7  PERSON
8  type,data-set,entityid,firstname,lastname,primaryaddress,inaddressbook,loadrelatedcontacts,
       referred,contactaddresses
9  Person,0,ab:2001,John,Foo,ab:1002,true,true,true,ContactAddress|address[ab:10001,ab:1002]
10 Person,0,ab:2002,Paul,Bar,ab:1002,false,false,false,ContactAddress|address[ab:10001]
11 Person,0,ab:2003,David,Goo,,false,true,false,,

In the previous example, the primaryaddress on line 9 is a foreign key to the Address specified on line 4.

If PolicyCenter cannot resolve a foreign key reference and does not require the foreign key, PolicyCenter imports the data, sets the foreign key field to null, and reports an error. If PolicyCenter does require the foreign key, then PolicyCenter reports an error and does not import that data.

Simple array data

You specify simple array data, referencing a single foreign key by using the following format:
  • arraykey|foreignkey[publicID,publicID,...]

In the PERSON example (line 9), the arraykey value is the array key on the parent entity (Person). The foreignkey is the foreign key name of the array without the ID. ContactAddress is the array key and address is the foreign key name. The public ID values [publicID,publicID,...] correspond to public IDs that are referenced by the foreign key.

In this format, the arraykey is optional. However, you might want to retain it for readability.

Complex array data

You might need to specify more complex arrays that have a mixture of data types. If you specify arrays that contain a mixture of columns, foreign key data, or typelists, use a different format. The basic format of these complex array entries appear as follows:
  • [ [array_entry];[array_entry]; ...]
Enclose each array_entry in brackets. Separate multiple entries with semicolons. Enclose all completed entries in a second set of brackets. Each array_entry is made up of comma-separated [typevalue] pairs as follows:
  • [[[type|value],[type|value]];[[type|value],[type|value]]]

The type is the name of a column, typelist, or foreign key, as in a heading line. The value is the column value, typelist typecode, or a foreign key. In the following sample, there are three array_entry specifications, the first and last array_entry specifications appear in bold:

Group
type,data-set,entityid,users
Group,0,demo_sample:27,[[[user|demo_sample:101],
  [loadfactor|50],[loadfactortype|loadfactorview]];[[user|demo_sample:102],
  [loadfactor|100],[loadfactortype|loadfactoradmin]];
  [[user|demo_sample:103],[loadfactor|50],[loadfactortype|loadfactorview]]]