Summary
This document will provide the necessary information when importing files for creating and modifying customer records using the Bulk Data application.
Links:
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 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 second row of the import file must contain the appropriate column names. 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 colunns that contain data to be imported are necessary.
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.
Attributes
An import file may include any number of customer attributes. In the file header record, the attribute 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 CostGuard and may not match the corresponding property name.
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 an attribute property for the customer being added or modified. There may be zero or more attribute columns included in an import file.
See the Attributes 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). |
| 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. |
