addressTableImport

Description

Imports data from a database or datatabases to an address table.

Remarks

addressTable must be one of the following.

• The ID of an address table. The ID of an address table is guaranteed to be unique.
• The name$ prefix plus the name of an address table. The name of an address table is not required to be unique. However, if you use the name$ prefix to reference an address table with a name that is not unique, there is an error. In this case, you must refer to the address table by its ID.
• The file$ prefix plus the name of a JSON file representing an address table, excluding extension. The file name must be lower case and have an extension of .json. The file must be in the addresstables/ folder.

sources array

The sources array is an array of objects that each defines a database to import data from. There can be multiple sources, which can be of different classes of database, but there is only one fieldMappings object.

classId is the class of database and is an enumeration of the following values.

• csv - Comma Separated Values file, as defined in RFC 4180.
• dbf - dBASE or FoxPro file.
• delimited - Delimited file. This is similar to csv except that the record delimiter, field delimiter character and field quote character are user-defined.
• fixed - Fixed length file.

connection is the file name. If classId is dbf and the extension is not specified, it uses .dbf.

Example

Request

This request imports data from a Comma Separated Values file and a dBASE or FoxPro file.

{
  "addressTable": "7112661645",
  "sources": [
    {
      "classId": "csv",
      "connection": "C:\\path\\to\\my\\source-file.csv",
      "schema": {
        "fieldDelimiter": ",",
        "recordDelimiter": "\r\n",
        "fields": [
          {
            "fieldName": "City",
            "fieldType": "string",
            "fieldSize": 30
          },
          {
            "fieldName": "State",
            "fieldType": "string",
            "fieldSize": 2
          },
          {
            "fieldName": "ZIP",
            "fieldType": "string",
            "fieldSize": 10
          }
        ]
      }
    },
    {
      "classId" : "dbf",
      "connection" : "C:\\path\\to\\my\\source-file.dbf"
    }
  ],
  "overwrite": true,
  "fieldMappings": [
    {
      "sourceField": "City",
      "destField": "City"
    },
    {
      "sourceField": "State",
      "destField": "State"
    },
    {
      "sourceField": "ZIP",
      "destField": "ZIP"
    }
  ]
}

Reply

The reply provides information about the records processed.

{
  "recordsProcessed": 1550,
  "recordsAdded": 1550,
  "fieldsCopied": 12400,
  "fieldsNotCopied": 3100
}

recordsProcessed is the number of source records processed.

recordsAdded is the number of records added to the address table.

fieldsCopied is the number of field values copied to the address table.

fieldsNotCopied is the number of field values not copied to the address table due to an unresolved field reference in the fieldMappings object.

Request Schema

{
  "$schema":"http://json-schema.org/draft-04/schema#",
  "description":"Imports data into an address table.",
  "type":"object",
  "required":[
    "addressTable",
    "sources",
    "fieldMappings"
  ],
  "properties":{
    "addressTable":{
      "title":"Address Table",
      "description":"Resource item identifying the address table.",
      "type":"string",
      "pattern":"(?i)^([0-9]{10}|name\\$.+|file\\$[a-z]+)$"
    },
    "overwrite":{
      "title":"Overwrite",
      "description":"Indicates whether or not to overwrite existing records in the address table.",
      "default":false,
      "type":"boolean"
    },
    "sources":{
      "title":"Sources",
      "description":"List of source tables to import from.",
      "type":"array",
      "items":{
        "type":"object",
        "required":[
          "classId",
          "connection"
        ],
        "properties":{
          "classId":{
            "title":"Class ID",
            "description":"Database class ID.",
            "type":"string",
            "enum":[
              "csv",
              "dbf",
              "fixed",
              "delimited"
            ],
            "enumValues":[
              {
                "value":"csv",
                "displayValue":"Comma Separated Values",
                "description":"Comma Separated Values file, as defined in RFC 4180, with ANSI encoding."
              },
              {
                "value":"dbf",
                "displayValue":"dBASE/FoxPro",
                "description":"dBASE or FoxPro table."
              },
              {
                "value":"fixed",
                "displayValue":"Fixed Length",
                "description":"Fixed length text file, with ANSI encoding."
              },
              {
                "value":"delimited",
                "displayValue":"Delimited",
                "description":"Delimited text file, with ANSI encoding."
              }
            ]
          },
          "connection":{
            "title":"Connection",
            "description":"Database connection parameters.",
            "type":"string",
            "minLength":1
          },
          "schema":{
            "title":"Schema",
            "description":"Defines the structure of the table.",
            "type":"object",
            "properties":{
              "recordDelimiter":{
                "title":"Record Delimiter",
                "description":"String used in the delimited file to indicate the end of a record.",
                "type":"string",
                "minLength":1
              },
              "fieldDelimiter":{
                "title":"Field Delimiter",
                "description":"Character used in the delimited file to indicate the end of a field value and the start of the next field value.",
                "type":"string",
                "minLength":1,
                "maxLength":1
              },
              "fieldQuote":{
                "title":"Field Quote",
                "description":"Character used in the delimited file to quote a field value as needed.",
                "type":"string",
                "minimum":1,
                "maxLength":1
              },
              "fields":{
                "title":"Fields",
                "description":"Definitions of the fields in the table schema.",
                "type":"array",
                "minItems":1,
                "items":{
                  "type":"object",
                  "required":[
                    "fieldName",
                    "fieldType"
                  ],
                  "properties":{
                    "fieldName":{
                      "title":"Field Name",
                      "description":"Name of the field in the table schema.",
                      "type":"string",
                      "minLength":1
                    },
                    "fieldType":{
                      "title":"Field Type",
                      "description":"Type of the field in the table schema.",
                      "type":"string",
                      "enum":[
                        "autoInc",
                        "boolean",
                        "currency",
                        "date",
                        "dateTime",
                        "float",
                        "integer",
                        "memo",
                        "smallInt",
                        "string",
                        "word"
                      ],
                      "enumValues":[
                        {
                          "value":"autoInc",
                          "displayValue":"Auto-Increment",
                          "description":"Auto-incrementing 32-bit signed integer value."
                        },
                        {
                          "value":"boolean",
                          "displayValue":"Boolean",
                          "description":"Boolean value."
                        },
                        {
                          "value":"currency",
                          "displayValue":"Currency",
                          "description":"Currency value."
                        },
                        {
                          "value":"date",
                          "displayValue":"Date",
                          "description":"Date value."
                        },
                        {
                          "value":"dateTime",
                          "displayValue":"Date/Time",
                          "description":"Date/time (timestamp) value."
                        },
                        {
                          "value":"float",
                          "displayValue":"Floating Point",
                          "description":"Floating point numeric value."
                        },
                        {
                          "value":"integer",
                          "displayValue":"Integer",
                          "description":"32-bit signed integer value."
                        },
                        {
                          "value":"memo",
                          "displayValue":"Memo",
                          "description":"Binary value."
                        },
                        {
                          "value":"smallInt",
                          "displayValue":"Small Integer",
                          "description":"16-bit signed integer value."
                        },
                        {
                          "value":"string",
                          "displayValue":"String",
                          "description":"String value."
                        },
                        {
                          "value":"word",
                          "displayValue":"Word",
                          "description":"16-bit unsigned integer value."
                        }
                      ]
                    },
                    "fieldSize":{
                      "title":"Field Size",
                      "description":"Size of the field in the table schema.",
                      "type":"integer",
                      "minimum":0,
                      "maximum":65535
                    }
                  },
                  "additionalProperties":false
                }
              }
            },
            "additionalProperties":false
          }
        },
        "additionalProperties":false
      }
    },
    "fieldMappings":{
      "title":"Field Mappings",
      "description":"Maps source fields to destination fields in the address table.",
      "type":"array",
      "items":{
        "type":"object",
        "required":[
          "sourceField",
          "destField"
        ],
        "properties":{
          "sourceField":{
            "title":"Source Field",
            "description":"Name of the field in the source tables.",
            "type":"string",
            "minLength":1
          },
          "destField":{
            "title":"Destination Field",
            "description":"Field reference identifying the destination field in the address table.",
            "oneOf":[
              {
                "description":"Identifies a field by name.",
                "type":"string",
                "minLength":1
              },
              {
                "description":"Identifies a field by display name.",
                "type":"object",
                "required":[
                  "displayName"
                ],
                "properties":{
                  "displayName":{
                    "title":"Display Name",
                    "description":"Display name to match to a field.",
                    "type":"string",
                    "minLength":1
                  }
                },
                "additionalProperties":false
              },
              {
                "description":"Identifies a field by address group and data type.",
                "type":"object",
                "required":[
                  "addressGroup",
                  "dataType"
                ],
                "properties":{
                  "addressGroup":{
                    "title":"Address Group",
                    "description":"Address group to match to a field.",
                    "oneOf":[
                      {
                        "type":"integer",
                        "minimum":1,
                        "maximum":255
                      },
                      {
                        "type":"string",
                        "minLength":1
                      }
                    ]
                  },
                  "dataType":{
                    "title":"Data Type",
                    "description":"Data type to match to a field.",
                    "oneOf":[
                      {
                        "type":"integer",
                        "minimum":1,
                        "maximum":255
                      },
                      {
                        "type":"string",
                        "minLength":1
                      }
                    ]
                  }
                },
                "additionalProperties":false
              }
            ]
          }
        },
        "additionalProperties":false
      }
    }
  },
  "additionalProperties":false
}

Reply Schema

{
  "$schema":"http://json-schema.org/draft-04/schema#",
  "type":"object",
  "required":[
    "recordsProcessed",
    "recordsAdded",
    "fieldsCopied",
    "fieldsNotCopied"
  ],
  "properties":{
    "recordsProcessed":{
      "title":"Records Processed",
      "description":"Number of records processed from source tables.",
      "type":"integer",
      "minValue":1
    },
    "recordsAdded":{
      "title":"Records Added",
      "description":"Number of records added to the address table.",
      "type":"integer",
      "minValue":1
    },
    "fieldsCopied":{
      "title":"Fields Copied",
      "description":"Number of fields copied.",
      "type":"integer",
      "minValue":1
    },
    "fieldsNotCopied":{
      "title":"Fields Not Copied",
      "description":"Number of fields not copied because a source or destination field reference is incorrect.",
      "type":"integer",
      "minValue":1
    }
  },
  "additionalProperties":false
}