Bulk Data – Customer Specification

Summary

This document will provide the necessary information when importing files for creating and modifying customer records using the Bulk Data application.

Link:

Example

File Format

General Format

The following general characteristics apply to Create and Modify Customer import files:

File naming convention must be *.csv
A header is required
The header must be followed by one or more comma-delimited detail records
Place quotes (“) around any text fields containing a comma.
Empty (non-required) fields are acceptable.
The columns are not required to appear in a specific order.
You do not have to include all columns in an import file.

Import Identifier

The import identifier is optional if you import files via the Bulk Import web module. The correct identifier is automatically applied by selecting the type of import.

If you import using the file automation method by uploading files and placing them on the file server, the first row of the file must have the file format identifier, starting in the first position of the first row. For the customer file the identifier is:

FORMAT:IDI/CostGuardBulkData/Customer

Note:
“Format:” is part of the identifier and must be included.

Header Record

The import file must contain the appropriate column names. This is typically the first row when you use the Bulk Import module. It is the second row if you use the file automation method. The column names can be found in the Record Layout section of this document. The columns do not need to appear in a specific order. Import files do not need to include all of the columns, only the columns that contain data to be imported are necessary.

Custom Properties

An import file may include any number of custom properties. In the file header record, the custom fields must be pre-fixed as follows:

ATTR_<attribute property name>

When using attributes, please contact customer support for the specific attribute property name to use in the Bulk Data file header. Only attribute display names are visible within the IDI platform and may not match the corresponding property name.

Clear Values

The modify customer import file supports the ability to clear previously set data. To clear data, the record must contain “@[System.Clear]” without the quotes. Example:

@[System.Clear]

Attempts to clear data for fields that are of ‘Yes/No’ data types or required, will result in an errored record.

Action Indicator

In addition to the Customer property fields there is a special field called Action that controls the behavior of the system when interpreting Customer Import rows. If the Action value is blank or equals Create, then the system will create a customer with the values in the row. If the Action value equals Modify, then the system will modify the customer identified by the AccountNumber value, updating the customer based on the values in the row.

If you are modifying a customer, the Action must be set to a value of Modify or the row will return an error.

CPNI

Required Fields

While CPNI properties in the Record Layout section below are not listed as required fields, CPNI properties will be required for account types that are configured to use CPNI on the CPNI > CPNI Configuration Settings page in CostGuard client. Additional verification is done when adding or modifying a customer account with an account type that has CPNI enabled. If any fields are marked as Required CPNI Data, on the CPNI Configuration Settings page, those fields will be required when importing customer accounts that use CPNI. This may include:

CPNI Account Password
Authorized User and Password
Security Question and Answer

If an account type is configured to use CPNI with no Required CPNI Data defined, at least one CPNI field must be populated for customer accounts with that account type.

You may choose to populate any one of the CPNI fields.

Notifications

When CPNI notifications are enabled from Customer Care (MANAGE > Settings – CPNI Settings – Configure Notifications), changes made to customer accounts using the Customer Bulk Data import can result in CPNI notifications being sent to customers. For account types that are configured to use CPNI, changes to any CPNI fields and changes to Primary Contact fields (name, address, phone, email, etc.) will result in a notification being sent to the customer.

Record Layout

Field	DataType	Required Create	Required Modify	Clearable	Description
AccountManager	Text	No	No	Yes	The account manager to assign to the customer.
AccountNumber	Text	No	Yes	No	The account number to assign to the customer.
AccountNumberNew	Text	No	No	No	The new account number to assign to the customer. Note: This value is only used when modifying a customer.
AccountReceivableGroupID	Integer	Yes*	No	No*	The ID of a valid configured account receivable group to assign to the customer. *Note: This value is not required for Prospect accounts
AccountStatusID	Integer	Yes	No	No	The ID of a valid configured account status to assign to the customer.
AccountTypeID	Integer	Yes	No	No	The ID of a valid configured account type to assign to the customer.
Action	Text	No	Yes	N/A	Indicates the operation to perform on a customer. Valid Values: “Create” or “Modify”. When not specified, this value defaults to “Create”.
AffinityGroupID	Integer	No	No	Yes	The ID of a valid configured affinity group assigned to the customer.
ATTR_*	Text	No	No	Yes	Contains the value of a custom property for the customer being added or modified. There may be zero or more custom properties included in an import file. See the Custom Properties section above.
BalanceAgingGroupID	Integer	Yes*	No	No*	The ID of a valid configured balance aging group to assign to the customer. *Note: This value is not required for Prospect accounts
BillCycleID	Integer	Yes*	N/A	Yes*	The ID of a valid configured bill cycle to assign to the customer. This field is only applicable when creating an account and is not supported for the Modify action. *Note: This value is not required for Prospect accounts
BillingAddress1	Text	No	No	Yes	Address line 1 associated with the billing contact.
BillingAddress2	Text	No	No	Yes	Address line 2 associated with the billing contact.
BillingAddress3	Text	No	No	Yes	Address line 3 associated with the billing contact.
BillingCity	Text	No	No	Yes	The city associated with the billing contact.
BillingCompanyName	Text	No	No	Yes	The company name associated with the billing contact.
BillingContactIsUnique	Yes/No	No	No	No	When false (Default) the billing contact is the same as primary contact. When true the billing contact is unique and the Billing contact fields should be filled in.
BillingCountry	Text	No	No	Yes	The country associated with the billing contact.
BillingCounty	Text	No	No	Yes	The county associated with the billing contact.
BillingEmailAddress1	Text	No	No	Yes	Email address associated to the billing contact.
BillingEmailAddress2	Text	No	No	Yes	Email address associated to the billing contact.
BillingEmailAddress3	Text	No	No	Yes	Email address associated to the billing contact.
BillingEmailType1	Text	No	No	Yes	Indicates the type of email of the billing contact. Valid Values: “Home”, “Work”
BillingEmailType2	Text	No	No	Yes	Indicates the type of email of the billing contact. Valid Values: “Home”, “Work”
BillingEmailType3	Text	No	No	Yes	Indicates the type of email of the billing contact. Valid Values: “Home”, “Work”
BillingFirstName	Text	No	No	Yes	The first name associated with the billing contact.
BillingLastName	Text	No	No	Yes	The last name associated with the billing contact.
BillingMiddleInitial	Text	No	No	Yes	The the middle initial associated with the billing contact.
BillingNote	Text	No	No	Yes	Notes associated with the billing contact.
BillingPhoneNumber1	Text	No	No	Yes	Phone number associated with the billing contact.
BillingPhoneNumber2	Text	No	No	Yes	Phone number associated with the billing contact.
BillingPhoneNumber3	Text	No	No	Yes	Phone number associated with the billing contact.
BillingPhoneNumber4	Text	No	No	Yes	Phone number associated with the billing contact.
BillingPhoneType1	Text	No	No	Yes	Indicates the type of phone number of the billing contact. Valid Values: “Home”, “Work”, “Mobile”, “Fax”, “Pager”
BillingPhoneType2	Text	No	No	Yes	Indicates the type of phone number of the billing contact. Valid Values: “Home”, “Work”, “Mobile”, “Fax”, “Pager”
BillingPhoneType3	Text	No	No	Yes	Indicates the type of phone number of the billing contact. Valid Values: “Home”, “Work”, “Mobile”, “Fax”, “Pager”
BillingPhoneType4	Text	No	No	Yes	Indicates the type of phone number of the billing contact. Valid Values: “Home”, “Work”, “Mobile”, “Fax”, “Pager”
BillingState	Text	No	No	Yes	The state associated to the billing contact.
BillingValidateAddress	Yes/No	No	No	Yes	Indicates if the billing contact address should be validated. Default value is false. When true address validation errors will prevent the customer from being created/updated.
BillingZipCode	Text	No	No	Yes	The zip code associated to the billing contact.
ChildSortOrder	Text	No	No	No	The description of the sort order for the corporate child invoices. Valid Values: “AccountNumber”, “CustomerName”
CPNIAccountPassword	Text	No	No	Yes	The password for the customer account.
CPNIAuthorizedUserFirstName1	Text	No	No	No	First name of an authorized user for this account.
CPNIAuthorizedUserFirstName2	Text	No	No	No	First name of an authorized user for this account.
CPNIAuthorizedUserFirstName3	Text	No	No	No	First name of an authorized user for this account.
CPNIAuthorizedUserLastName1	Text	No	No	No	Last name of an authorized user for this account.
CPNIAuthorizedUserLastName2	Text	No	No	No	Last name of an authorized user for this account.
CPNIAuthorizedUserLastName3	Text	No	No	No	Last name of an authorized user for this account.
CPNIAuthorizedUserPassword	Text	No	No	Yes	The password that authorized users must supply for access to the account.
CPNIAuthorizedUserRemove1	Yes/No	No	No	No	For a “Modify” action, whether or not to remove an authorized user (specified by CPNIAuthorizedUserFirstName1 and CPNIAuthorizedUserLastName1) for this account.
CPNIAuthorizedUserRemove2	Yes/No	No	No	No	For a “Modify” action, whether or not to remove an authorized user (specified by CPNIAuthorizedUserFirstName2 and CPNIAuthorizedUserLastName2) for this account.
CPNIAuthorizedUserRemove3	Yes/No	No	No	No	For a “Modify” action, whether or not to remove an authorized user (specified by CPNIAuthorizedUserFirstName3 and CPNIAuthorizedUserLastName3) for this account.
CPNIExternalMarketing	Yes/No	No	No	No	Indicates the customer’s preference regarding external marketing.
CPNIInternalMarketing	Yes/No	No	No	No	Indicates the customer’s preference regarding internal marketing.
CPNIOptIn	Yes/No	No	No	No	Indicates if the customer has opted-in to providing CPNI information.
CPNIRemove	Yes/No	No	No	No	Indicates the customer’s CPNI information should be removed. Note: This value is only used when modifying a customer.
CPNISecurityQuestionAnswer	Text	No	No	Yes	The answer to the provided SecurityQuestion.
CPNISecurityQuestionID	Integer	No	No	No	The unique identifier for the SecurityQuestion.
CreditClassID	Integer	No	No	Yes	The ID of the credit class assigned to the customer.
CreditLimit	Decimal	No	No	Yes	The credit limit assigned to the customer.
CurrencyID	Integer	No	No	Yes	The unique identifier for the currency assigned to the customer.
CustomerName	Text	Yes	No	No	The name of the customer. Note: This field is required when creating a customer.
DateOfBirth	Date	No	No	Yes	The customer’s date of birth.
DisableEPayNotifications	Yes/No	No	No	No	When false (default) EPay Notifications will not be disabled. When true EPay Notifications will be disabled.
DriversLicenseNumber	Text	No	No	Yes	The customer’s driver’s license number.
DriversLicenseState	Text	No	No	Yes	The customer’s driver’s license state.
DUNSNumber	Text	No	No	Yes	The Data Universal Numbering System (DUNS) number for the customer — a unique numeric identifier for business entities.
EstablishDate	Date & Time	No	No	No	Date the customer’s account was established. Note: If this value is not specified, Today’s date will be used.
FEIN	Text	No	No	Yes	The FEIN of the customer.
FinanceChargeExempt	Yes/No	No	No	No	Indicates if the customer is exempt from being charged finance charges, if warranted.
FinanceGroupID	Integer	Yes*	No	No*	The ID of a valid configured finance group to assign to the customer. This value is not required for prospect accounts.
HomeRegionID	Integer	No	No	Yes	The unique identifier for the home region, used for local area calling, associated with the customer.
IsDirectoryAssistanceExempt	Yes/No	No	No	No	Indicates if the customer receives all Local/Regular Directory Assistance calls for free.
IsDisabled	Yes/No	No	No	No	Indicates if the customer qualifies for a reduction of the residential recurring basic service charge due to being disabled.
IsHearingImpaired	Yes/No	No	No	No	Indicates if the customer qualifies for a reduction of the residential recurring basic service charge due to being hearing impaired.
IsLifeline	Yes/No	No	No	No	Indicates if the single residential SLC charge is reduced as required by the state, if the customer has at least one single residential SLC charge created.
IsProspect	Yes/No	No	No	No	When false (Default) the customer is billable (can be invoiced). When true the customer is a prospect.
IsSpeechImpaired	Yes/No	No	No	No	Indicates if the customer qualifies for a reduction of the residential recurring basic service charge due to being speech impaired.
IsVisuallyImpaired	Yes/No	No	No	No	Indicates if the customer qualifies for 50 free Local/Regular Directory Assistance calls.
LanguageID	Integer	No	No	Yes	The unique identifier for the language assigned to the customer.
MainBillingNumber	Text	No	No	Yes	The main billing number for the account.
MarketID	Integer	Yes	No	No	The ID of a valid configured market to assign to the customer. Note: This field is required when creating a customer.
PrimaryAddress1	Text	No	No	Yes	Address line 1 associated with the primary contact.
PrimaryAddress2	Text	No	No	Yes	Address line 2 associated with the primary contact.
PrimaryAddress3	Text	No	No	Yes	Address line 3 associated with the primary contact.
PrimaryCity	Text	No	No	Yes	The city associated with the primary contact.
PrimaryCompanyName	Text	No	No	Yes	The company name associated with the primary contact.
PrimaryCountry	Text	No	No	Yes	The country associated with the primary contact.
PrimaryCounty	Text	No	No	Yes	The county associated with the primary contact.
PrimaryEmailAddress1	Text	No	No	Yes	Email address associated to the primary contact.
PrimaryEmailAddress2	Text	No	No	Yes	Email address associated to the primary contact.
PrimaryEmailAddress3	Text	No	No	Yes	Email address associated to the primary contact.
PrimaryEmailType1	Text	No	No	Yes	Indicates the type of email of the primary contact. Valid Values: “Home”, “Work”
PrimaryEmailType2	Text	No	No	Yes	Indicates the type of email of the primary contact. Valid Values: “Home”, “Work”
PrimaryEmailType3	Text	No	No	Yes	Indicates the type of email of the primary contact. Valid Values: “Home”, “Work”
PrimaryFirstName	Text	No	No	Yes	The first name associated with the primary contact.
PrimaryLastName	Text	No	No	Yes	The last name associated with the primary contact.
PrimaryMiddleInitial	Text	No	No	Yes	The the middle initial associated with the primary contact.
PrimaryNote	Text	No	No	Yes	Notes associated with the primary contact.
PrimaryPhoneNumber1	Text	No	No	Yes	Phone number associated with the primary contact.
PrimaryPhoneNumber2	Text	No	No	Yes	Phone number associated with the primary contact.
PrimaryPhoneNumber3	Text	No	No	Yes	Phone number associated with the primary contact.
PrimaryPhoneNumber4	Text	No	No	Yes	Phone number associated with the primary contact.
PrimaryPhoneType1	Text	No	No	Yes	Indicates the type of phone number of the primary contact. Valid Values: “Home”, “Work”, “Mobile”, “Fax”, “Pager”
PrimaryPhoneType2	Text	No	No	Yes	Indicates the type of phone number of the primary contact. Valid Values: “Home”, “Work”, “Mobile”, “Fax”, “Pager”
PrimaryPhoneType3	Text	No	No	Yes	Indicates the type of phone number of the primary contact. Valid Values: “Home”, “Work”, “Mobile”, “Fax”, “Pager”
PrimaryPhoneType4	Text	No	No	Yes	Indicates the type of phone number of the primary contact. Valid Values: “Home”, “Work”, “Mobile”, “Fax”, “Pager”
PrimaryState	Text	No	No	Yes	The state associated to the primary contact.
PrimaryValidateAddress	Yes/No	No	No	No	Indicates if the primary contact address should be validated. Default value is false. When true address validation errors will prevent the customer from being created/updated.
PrimaryZipCode	Text	No	No	Yes	The zip code associated to the primary contact.
PullInvoice	Yes/No	No	No	No	Indicates if the invoice should not be sent automatically to a customer (a value of true indicates that the invoice will not be sent out automatically).
ReceiveBillNotification	Yes/No	No	No	Yes	Indicates whether the custom has opted in for bill notifications.
ReportGroupID	Integer	No	No	No	The unique identifier for the Report Group (i.e., Invoice Format) specified for the customer. Note: If this value is not specified, 0 (None) will be used.
SalesCodeID	Integer	No	No	Yes	The unique identifier of the sales code for the sales associate associated with the customer.
SocialSecurityNumber	Text	No	No	Yes	The customer’s social security number.
SuppressInvoicePrint	Yes/No	No	No	No	If specified as true, indicates that the customer should not receive a printed paper invoice. For example, if the customer receives invoice online, a paper invoice does not need to be printed.
TaxExemptE911City	Yes/No	No	No	No	Indicates that exemptions apply to E911 city taxes.
TaxExemptE911County	Yes/No	No	No	No	Indicates that exemptions apply to E911 county taxes.
TaxExemptE911Regional	Yes/No	No	No	No	Indicates that exemptions apply to E911 regional taxes.
TaxExemptE911State	Yes/No	No	No	No	Indicates that exemptions apply to E911 state taxes.
TaxExemptIDCity	Text	No	No	Yes	The city tax exemption ID of the customer.
TaxExemptIDCounty	Text	No	No	Yes	The county tax exemption ID of the customer.
TaxExemptIDFederal	Text	No	No	Yes	The federal tax exemption ID of the customer.
TaxExemptIDRegional	Text	No	No	Yes	The regional tax exemption ID of the customer.
TaxExemptIDState	Text	No	No	Yes	The state tax exemption ID of the customer.
TaxExemptSurchargeCity	Yes/No	No	No	No	Indicates that exemptions apply to surcharge city taxes.
TaxExemptSurchargeCounty	Yes/No	No	No	No	Indicates that exemptions apply to surcharge county taxes.
TaxExemptSurchargeFederal	Yes/No	No	No	No	Indicates that exemptions apply to surcharge federal taxes.
TaxExemptSurchargeRegional	Yes/No	No	No	No	Indicates that exemptions apply to surcharge regional taxes.
TaxExemptSurchargeState	Yes/No	No	No	No	Indicates that exemptions apply to surcharge state taxes.
TaxExemptTaxCity	Yes/No	No	No	No	Indicates that exemptions apply to city taxes.
TaxExemptTaxCounty	Yes/No	No	No	No	Indicates that exemptions apply to county taxes.
TaxExemptTaxFederal	Yes/No	No	No	No	Indicates that exemptions apply to federal taxes.
TaxExemptTaxRegional	Yes/No	No	No	No	Indicates that exemptions apply to regional taxes.
TaxExemptTaxState	Yes/No	No	No	No	Indicates that exemptions apply to state taxes.
TaxExemptUSFCity	Yes/No	No	No	No	Indicates that exemptions apply to USF city taxes.
TaxExemptUSFCounty	Yes/No	No	No	No	Indicates that exemptions apply to USF county taxes.
TaxExemptUSFFederal	Yes/No	No	No	No	Indicates that exemptions apply to USF federal taxes.
TaxExemptUSFRegional	Yes/No	No	No	No	Indicates that exemptions apply to USF regional taxes.
TaxExemptUSFState	Yes/No	No	No	No	Indicates that exemptions apply to USF state taxes.
TaxOverrideRemove	Yes/No	No	No	No	When true indicates the current tax override state and jurisdictions should no longer be associated to the customer. Note: This value is only used when modifying a customer.
TaxOverrideState	Text	No	No	No	Indicates the customer should be taxed as if they had this state in their address.
TaxOverrideJurisdictionID1	Integer	No	No	No	Indicates the customer should be taxed as if they had this tax jurisdiction assigned. Note: This requires TaxOverrideState to be set.
TaxOverrideJurisdictionID1Remove	Yes/No	No	No	No	When true indicates the value in the TaxOverrideJurisdictionID1 field should no longer be associated to the customer. Note: This value is only used when modifying a customer.
TaxOverrideJurisdictionID2	Integer	No	No	No	Indicates the customer should be taxed as if they had this tax jurisdiction assigned. Note: It is recommended to not use multiple tax override jurisdictions on an account unless you fully understand the consequences.
TaxOverrideJurisdictionID2Remove	Yes/No	No	No	No	When true indicates the value in the TaxOverrideJurisdictionID2 field should no longer be associated to the customer. Note: This value is only used when modifying a customer.
TaxOverrideJurisdictionID3	Integer	No	No	No	Indicates the customer should be taxed as if they had this tax jurisdiction assigned. Note: It is recommended to not use multiple tax override jurisdictions on an account unless you fully understand the consequences.
TaxOverrideJurisdictionID3Remove	Yes/No	No	No	No	When true indicates the value in the TaxOverrideJurisdictionID3 field should no longer be associated to the customer. Note: This value is only used when modifying a customer.
TaxOverrideJurisdictionID4	Integer	No	No	No	Indicates the customer should be taxed as if they had this tax jurisdiction assigned. Note: It is recommended to not use multiple tax override jurisdictions on an account unless you fully understand the consequences.
TaxOverrideJurisdictionID4Remove	Yes/No	No	No	No	When true indicates the value in the TaxOverrideJurisdictionID4 field should no longer be associated to the customer. Note: This value is only used when modifying a customer.
TaxTypeExemptID1	Integer	No	No	No	The unique identifier for the taxtypeexempt to be associated to the customer.
TaxTypeExemptID1Remove	Yes/No	No	No	No	When true indicates the value in the TaxTypeExemptID1 field should no longer be associated to the customer. Note: This value is only used when modifying a customer.
TaxTypeExemptID2	Integer	No	No	No	The unique identifier for the taxtypeexempt to be associated to the customer.
TaxTypeExemptID2Remove	Yes/No	No	No	No	When true indicates the value in the TaxTypeExemptID2 field should no longer be associated to the customer. Note: This value is only used when modifying a customer.
TaxTypeExemptID3	Integer	No	No	No	The unique identifier for the taxtypeexempt to be associated to the customer.
TaxTypeExemptID3Remove	Yes/No	No	No	No	When true indicates the value in the TaxTypeExemptID3 field should no longer be associated to the customer. Note: This value is only used when modifying a customer.
UsageRounding	Text	No	No	No	Indicates what variety of usage round. Valid Values: “None”, “Conventional”, “Up”, “UseBillCycleDefault”
UsesTTY	Yes/No	No	No	No	Indicates if the customer qualifies for a reduction of the residential recurring basic service charge due to using a TTY.
ZoneID	Integer	No	No	Yes	The unique identifier of the zone for the customer.

Documentation Index