Extract Flat File Formats

This section outlines the format of output flat files created by master data, billing data, and consumption data extracts.

Data Areas:

• C1-AcctMailingAddrDataSnapshot

• C1-BillDataSnapshot

• C1-BillSegmentDataSnapshot

• C1-CustomerDataSnapshot

• C1-ExternalRepoSABasedSnapshot

• D1-ExternalRepositorySPSnapsht

• D1-ExternalRepositorySPMtrSnap

• D1-ExternalRepositoryIESnapsht

• C1-ExternalRepoBillingSnapshot

• D2-IntervalDataExtRepoSnapshot (used for interval consumption extract)

• D2-IntervalDataExtRepoTOUSnap (used for interval consumption extract mapped to TOU periods)

Specifics for how the flat files are created are defined on batch controls and algorithms used by the extract process.

• File Name and Path: Parameters on batch controls define the file name, path, and other details about the output file.

• File Size and Contents: Batch controls for initial load/ongoing consumption requests include a parameter to specify the number of service points to be included in each file.

C1-AcctMailingAddrDataSnapshot

C1-AcctMailingAddrDataSnapshot is used for mailing address data extracts, and includes the following information:

Field Name	Schema Element	Metadata Field
Account ID	accountId	ACCOUNT_ID
Person Id	personId	PERSON_ID
Is Seasonal Address	isSeasonalAddress
Seasonal Address Start Date/Time	seasonStart	IS_SEASONAL_ADDRESS
Seasonal Address End Date/Time	seasonEnd	SEASONAL_ADDRESS_END_DTTM
Mailing Address 1	address1	MAIL_ADDRESS_1
Mailing Address 2	address2	MAIL_ADDRESS_2
Mailing Address 3	address3	MAIL_ADDRESS_3
Mailing Address 4	address4	MAIL_ADDRESS_4
Locality	city	LOCALITY
Postal Code	postal	MAIL_POSTAL_CODE
Admin Area 1	state	ADMIN_AREA_1
Admin Area 2	adminArea2	ADMIN_AREA_2
Admin Area 3	adminArea3	ADMIN_AREA_3
Country	country	COUNTRY
Updated at	personAddressUpdatedAt	PERSON_ADDRESS_UPDATED_AT

Top of Page

C1-BillDataSnapshot

C1-BillDataSnapshot is used for the bill-based data extracts, and includes the following information:

Field Name	Schema Element	Metadata Field
Bill ID	billId	BILL_ID
Account ID	accountId	ACCOUNT_ID
Bill Date	billDate	BILL_DATE
Due Date	dueDate	DUE_DATE
Currency Code	currencyCode	CURRENCY_CD
Current Balance	currentBalance	CURRENT_BALANCE
Payoff Balance	payoffBalance	PAYOFF_BALANCE
Previous Period Balance	previousPeriodBalance	PREVIOUS_PERIOD_BALANCE
Total Payments	totalPayments	TOTAL_PAYMENTS
Total Adjustments	totalAdjustments	TOTAL_ADJUSTMENTS
Total Bill Corrections	totalBillCorrections	TOTAL_BILL_CORRECTIONS
Total Current Billing Charges	totalCurrentBillingCharges	TOTAL_CURRENT_BILLING_CHARGES
Total Balance Current Period	totalBalanceCurrentPeriod	TOTAL_BALANCE_CURRENT_PERIOD
Last Update Date/Time	lastUpdateDateTime	BILL_UPDATED_AT

Top of Page

C1-BillSegmentDataSnapshot

C1-BillSegmentDataSnapshot is used for the bill segment-based data extracts, and includes the following information:

Field Name	Schema Element	Metadata Field
Bill ID	billId	BILL_ID
Bill Segment ID	billSegmentId	BILL_SEGMENT_ID
Service Agreement	serviceAgreement	SERVICE_AGREEMENT_ID
Start Date	startDate	BILL_SEGMENT_START_DATE
End Date	endDate	BILL_SEGMENT_END_DATE
Usage Start Date/Time	usageStartDateTime	USAGE_PERIOD_START_DATE
Usage End Date/Time	usageEndDateTime	USAGE_PERIOD_END_DATE
Estimated	isEstimate	ESTIMATED
Closing Billing Statement	isClosingBillSegment	CLOSING
Bill Segment Status	billSegmentStatus	STATUS
Bill Cycle	billCycle	BILL_CYCLE_CODE
Bill Cycle Description	billCycleDescr	BILL_CYCLE_DESCRIPTION
Premide ID	premiseId	PREMISE_ID
Current Amount	currentAmount	CURRENT_AMOUNT
Outstanding Amount	outstandingAmount	OUTSTANDING_AMOUNT
Master Bill Segment ID	masterBillSegmentId	MASTER_BILL_SEGMENT_ID
Last Update Date/Time	lastUpdateDateTime	BILL_SEGMENT_UPDATED_AT

Top of Page

C1-CustomerDataSnapshot

C1-CustomerDataSnapshot is used for customer data extracts, and includes the following information:

Field Name	Schema Element	Metadata Field
Account ID	accountId	ACCOUNT_ID
Account Name	accountName	ACCOUNT_NAME
Account Setup Date/Time	accountSetUpDateTime	SETUP_DATETIME
Customer Class	customerClass	CUSTOMER_CLASS
Bill Cycle	billCycle	BILL_CYCLE_CODE
Bill Cycle Description	billCycleDescription	BILL_CYCLE_DESCRIPTION
Account Updated at	accountUpdatedAt	ACCOUNT_UPDATED_AT
Person ID	personId	PERSON_ID
Person Type	personOrBusiness	PERSON_TYPE
First NamE	firstName	FIRST_NAME
Last Name	lastName	LAST_NAME
Business Name	businessName	BUSINESS_NAME
Language Preference	language	LANGUAGE_PREFERENCE
Is Main Customer	isMainCustomer	IS_MAIN_CUSTOMER
Bill Route Typ	billRouteType	BILL_ROUTE_TYPE
Person Updated at	personUpdatedAt	PERSON_UPDATED_AT
Phone Type	phoneType	PHONE_TYPE_
Phone Number	phoneNumber	PHONE_NUMBER_
Phone Extension	phoneExtension	PHONE_EXTENSION_
Email	email	EMAIL_ADDRESS_
Email Type	emailType	EMAIL_TYPE_
Service Agreement ID	saId	SERVICE_AGREEMENT_ID
Service Agreement Name	saName	SERVICE_AGREEMENT_NAME
Start Date/Time	startDateTime	SA_START_DATETIME
End Date/Time	endDateTime	SA_END_DATETIME
Service Type	serviceType	SERVICE_TYPE_CODE
Service Type Description	serviceTypeDescription	SERVICE_TYPE_DESC
SA Status	saStatus	STATUS_CODE
SA Status Description	saStatusDescription	STATUS_DESCRIPTION
SA Type	saType	SERVICE_AGREEMENT_TYPE_CODE
SA Type Description	saTypeDescription	SA_TYPE_DESCRIPTION
Special Role Flag	specialRole	SPECIAL_ROLE_FLAG_CODE
Special Role Flag Description	specialRoleDescription	SPECIAL_ROLE_FLAG_DESCRIPTION
CIS Division	cisDivision	CIS_DIVISION
SIC	sic	BUSINESS_CLASSIFICATION_CODE
SA Updated at	saUpdatedAt	SERVICE_AGREEMENT_UPDATED_AT
SA Rate Code	saRateCode	SA_RATE_CODE
SA Rate Effective Date/Time	saRateEffectiveDateTime	EFFECTIVE_DATETIME
SA Rate Updated at	saRateUpdatedAt	SA_RATE_UPDATED_AT

Top of Page

C1-ExternalRepoSABasedSnapshot

C1-ExternalRepoSABasedSnapshot is used for the service agreement-based master data extracts, and includes the following information:

For DataConnect v1+ please refer to data area C1-CustomerDataSnapshot in the application for the layout/schema.

Field Name	Schema Element	Metadata Field
Service Agreement Id	saId	SA_ID
Account Id	accountId	ACCT_ID
Person Id	personId	PER_ID
Bill Cycle	billCycle	BILL_CYC_CD
Bill Cycle Description	billCycleDescription	DESCR
Collection Class	collectionClass	COLL_CL_CD
Collection Class Description	collectionClassDescription	DESCR
Account Information	accountInformation	ACCT_INFO
Customer Class	customerClass	CUST_CL_CD
Customer Class Description	customerClassDescription	DESCR
Landlord Indicator	landlordIndicator	C1_LANDLORD_IND
Mailing Address	mailingAddress1	ADDRESS1
Mailing Address 2	mailingAddress2	ADDRESS2
Mailing Address 3	mailingAddress3	ADDRESS3
Mailing City	mailingCity	CITY
Mailing State	mailingState	STATE
Mailing Postal	mailingPostal	POSTAL
First Name	firstName	ENTITY_NAME
Last Name	lastName	ENTITY_NAME
Phone Number 1	phone1	PHONE
Phone Number 2	phone2	PHONE
Email Address	email	EMAILID
Special Role	specialRole	SPECIAL_ROLE_FLG
Special Role Description	specialRoleDescription	DESCR
Service Type	serviceType	SVC_TYPE_CD
Service Type Description	serviceTypeDescription	DESCR
CIS Division	cisDivision	CIS_DIVISION
CIS Division Description	cisDivisionDescription	DESCR
SA Type	saType	SA_TYPE_CD
SA Type Description	saTypeDescription	DESCR
Revenue Class	revenueClass	REV_CL_CD
Revenue Class Description	revenueClassDescription	DESCR
SIC	sic	SIC_CD
SIC Code Description	sicDescription	DESCR
Deposit Class	depositClass	DEP_CL_CD
Deposit Class Description	depositClassDescription	DESCR
Campaign	campaign	CAMPAIGN_CD
Campaign Description	campaignDescription	DESCR
Debt Class	debtClass	DEBT_CL_CD
Debt Class Description	debtClassDescription	DESCR
SA Start Date	startDate	START_DT
SA End Date	endDate	END_DT
SA Status	saStatus	SA_STATUS_FLG
SA Status Description	saStatusDescription	DESCR
Rate Schedule	rateSchedule	RS_CD
Rate Schedule Description	rateScheduleDescription	DESCR
SA's Service Point(s) Id	spId	SP_ID
SA's Service Point(s) Start Date/Time	startDateTime	START_DTTM
SA's Service Point(s) End Date/Time	stopDateTime	STOP_DTTM

Top of Page

D1-ExternalRepositorySPSnapsht

D1-ExternalRepositorySPSnapsht is used for service point-based master data extracts, and includes the following information:

Field Name	Schema Element	Metadata Field
Service Point ID	spId	D1_SP_ID
CIS Service Point ID	cisSpId	CIS_EXT_SP_ID
Address	address1	DESCR254
City	city	CITY
State	state	STATE
Postal	postal	POSTAL
Service Point Type Code	spType	D1_SP_TYPE_CD
Service Point Type Description	spTypeDescription	DESCR100
Service Type Code	serviceType	D1_SVC_TYPE_CD
Service Type Description	serviceTypeDescription	DESCR100
Market Code	market	MKT_CD
Market Description	marketDescription	DESCR100
Service Point Status Code	boStatus	BO_STATUS_CD
Service Point Status Description	boStatusDescription	DESCR100
Disconnect Location Flag	disconnectLocation	DISCONN_LOC_FLG
Disconnect Location Description	disconnectLocationDescription	DESCR100
Measurement Cycle Code	measurementCycle	MSRMT_CYC_CD
Measurement Cycle Description	measurementCycleDescription	DESCR100
Measurement Cycle Route Code	measurementCycleRoute	MSRMT_CYC_RTE_CD
Measurement Cycle Route Description	measurementCycleRouteDescription	DESCR100
Geocode Latitude	geocodeLatitude	D1_GEO_LAT
Geocode Longitude	geocodeLongitude	D1_GEO_LONG
Facility Level 1	facilityLevel1	FACILITY_LEVEL1
Facility Level 2	facilityLevel2	FACILITY_LEVEL2
Facility Level 13	facilityLevel3	FACILITY_LEVEL3

Top of Page

D1–ExternalRepositorySPMtrSnap

D1-ExternalRepositorySPMtrSnap is used for service points, device configurations, and devices master data extracts, and includes the following information:

Field Name	Schema Element	Metadata Field
Premise ID	premiseId	D1_PREMISE_ID
Premise Name	premiseName	PREMISE_NAME_LBL
External Premise ID	externalPremiseId	D1_SP_EXT_PREMISE_ID
Parent Premise ID	parentPremiseId	D1_PREMISE_ID
Premise Type	premiseType	D1_PREMISE_TYPE
Premise Type Description	premiseTypeDescription	PREMISE_TYPE_DESCR_LBL
Address 1	address1	SP_ADDRESS1_LBL
Address 2	address2	SP_ADDRESS2_LBL
Address 3	address3	SP_ADDRESS3_LBL
Address 4	address4	SP_ADDRESS4_LBL
City	city	CITY
County	county	DESCR50
Postal	postal	POSTAL
State	state	STATE
Country	country	COUNTRY
Admin Area 3	adminArea3	D1_ADMIN_AREA_3
Division	division	DIVISION_CD
Premise Time Zone	premiseTimeZone	PREMISE_TIME_ZONE_CD
Geocode Latitude	geocodeLatitude	D1_GEO_LAT
Geocode Longitude	geocodeLongitude	D1_GEO_LONG
Landlord Agreement	landlordAgreementId	D1_LL_ID
Premise Updated At	premiseUpdatedAt	D1_PREMISE_UPDATED_AT
Service Point ID	spId	D1_SP_ID
Service Point Name	spName	SP_NAME_LBL
SP Type Description	spTypeDescription	SP_TYPE_DESCR_LBL
Service Point Type Category	spTypeCategory	SP_CATEGORY_FLG
SP Type	spType	D1_SP_TYPE_CD
Most Recent SA ID	mostRecentSAId	US_ID
Service Agreement Start Date/Time	SAStartDateTime	START_DTTM
Service Agreement Stop Date/Time	SAStopDateTime	D2_STOP_DTTM
Measurement Cycle	measurementCycle	MSRMT_CYC_CD
Measurement Cycle Description	measurementCycleDescription	MSRMT_CYCLE_DESCR_LBL
Measurement Cycle Route	measurementCycleRoute	MSRMT_CYC_RTE_CD
Measurement Cycle Route Description	measurementCycleRouteDescription	MSRMT_CYCLE_RT_DESCR_LBL
Facility Level 1	facilityLevel1	FACILITY_LEVEL1
Facility Level 2	facilityLevel2	FACILITY_LEVEL2
Facility Level 3	facilityLevel3	FACILITY_LEVEL3
Life Support / Senstive Load	lifeSupportSensitiveLoad	D1_LS_SL_FLG
UTC Offset	utcOffset	UTC_OFFSET
Business Object Status	boStatus	SP_BO_STATUS_LBL
Business Object Status Description	boStatusDescription	SP_BO_STATUS_DESCR_LBL
Service Source Status	spSourceStatus	SP_SRC_STAT_FLG
Service Source Status Description	spSourceStatusDescription	SP_SOURCE_STATUS_DESCR_LBL
Disconnect Location	disconnectLocation	DISCONN_LOC_FLG
Disconnect Location Description	disconnectLocationDescription	DISCON_LOC_DESCR_LBL
Service Point Time Zone	spTimeZone	SP_TIME_ZONE_CD
Service Type	erviceType	D1_SVC_TYPE_CD
Service Type Description	serviceTypeDescription	SERVICE_TYPE_DESCR_LBL
Disconnect for NonPay	disconnectForNonPay	D1_DISCONNECT_NON_PAY
Service Point Type Category Description	spTypeCategoryDescription	SP_TYPE_CAT_DESCR_LBL
Remote Disconnect	remoteDisconnect	D1_SGG_DISC_CFG
Install Event ID	installEventId	INSTALL_EVT_ID
Install Event External ID	deviceInstallationExternalId	D1_DVC_INSTALL_EXT_ID
Meter ID	meterId	D1_DEVICE_ID
Meter Name	meterName	D1_DEVICE_NAME
Install Date/Time	installDateTime	D1_INSTALL_DTTM
Removal Date/Time	removalDateTime	D1_REMOVAL_DTTM
Installation Event BO Status	installationBoStatus	IE_BO_STATUS_LBL
Installation Event BO Status Description	installationBoStatusDescription	IE_BO_STATUS_DESCR_LBL
Arming Status	armingStatus	ARMING_REQ_FLG
Device On/Off Status	deviceOnOffStatus	ONOFF_HIST_FLG
Installation Constant	installationConstant	INSTALLATION_CONST
Meter External ID	meterExternalId	D1_DVC_EXTERNAL_ID
Device Type	deviceType	DEVICE_TYPE_CD
Device Type Description	deviceTypeDescription	DEVICE_TYPE_DESCR_LBL
Manufacturer	manufacturer	MANUFACTURER_CD
Manufacturer Description	manufacturerDescription	MANUFACTURER_DESCR_LBL
Model	model	D1_MODEL_CD
Model Description	modelDescription	MODEL_DESCR_LBL
Device Manufacture Date	deviceManufactureDate	D1_DEVICE_MANUFACTURE_DATE
Dial Count	dialCount	D1_DIAL_COUNT
Sub Meter	subMeter	SUB_METER_SW
Net Meter	netMeter	NET_METER_SW
Device Business Object Status	deviceBoStatus	DEVICE_BO_STATUS_LBL
Device BO Status Description	deviceBoStatusDescription	DVC_BO_STATUS_DESCR_LBL
Device Service Type	deviceServiceType	DEVICE_SERVICE_TYPE_LBL
Device Service Type Description	deviceServiceTypeDescription	DEVICE_SERVICE_TYPE_DESCR_LBL
Head End System	headEndSystem	D1_SPR_CD
Head End System Description	eadEndSystemDescription	HEAD_END_SYSTEM_DESCR_LBL
Head End Registration Status	headEndRegistrationStatus	HEAD_END_REGISTR_STATUS_FLG
Device Classification	deviceClassification	DVC_CLASS_FLG
Measuring Component ID	measuringComponentId	MEASR_COMP_ID
External ID	externalId	D1_MC_EXTERNAL_ID
Channel ID	channelId	CHANNEL_ID
Number of Digits Left	numberOfDigitsLeft	D1_NBR_OF_DGTS_LFT
Number of Digits Right	numberOfDigitsRight	D1_NBR_OF_DGTS_RGT
Channel Multiplier	channelMultiplier	CHANNEL_MULTIPLIER
Measuring Component Type	measuringComponentType	MEASR_COMP_TYPE_CD
Measuring Component Type Description	measuringComponentTypeDescription	MC_TYPE_DESCR_LBL
Measuring Component Class	measuringComponentClass	MC_CLASS_FLG
Seconds per Interval (SPI)	secondsPerInterval	SEC_PER_INTRVL
Interval/Scalar	intervalScalar	INTERVAL_SCALAR_FLG
UOM	uom	UNIT_OF_MEASR_CD
TOU	tou	TIME_OF_USE_CD
SQI	sqi	SVC_QTY_IDNTFR_CD
Allow Negative Consumption	allowNegativeConsumption	ALLOW_NEG_CONSUMPTION_FLG
CIS Service Point ID	cisSPId	CSI_EXT_SP_ID

Top of Page

D1-ExternalRepositoryIESnapsht

D1-ExternalRepositoryIESnapsht used for install event-based master data extracts, and includes the following information:

Field Name	Schema Element	Metadata Field
Service Point ID	spId	D1_SP_ID
CIS Service Point ID	cisSpId	CIS_EXT_SP_ID
Meter ID	meterId	D1_DEVICE_ID
Device Type Code	deviceType	DEVICE_TYPE_CD
Device Type Description	deviceTypeDescription	DESCR100
Device Status Code	deviceBoStatus	BO_STATUS_CD
Device Status Description	deviceBoStatusDescription	DESCR100
Manufacturer Code	manufacturer	MANUFACTURER_CD
Manufacturer Description	manufacturerDescription	DESCR100
Model Code	model	D1_MODEL_CD
Model Description	modelDescription	DESCR100
Head-End System Code	headEndSystem	D1_SPR_CD
Head-End System Description	headEndSystemDescription	DESCR100
Badge Number	badgeNumber	D1_BADGE_NBR
Serial Number	serialNumber	D1_SERIAL_NBR
CIS Meter ID	cisMeterId	CIS_EXT_DEVICE_ID
Install Date/Time	installDateTime	D1_INSTALL_DTTM
Install Date/Time with UTC Offset	installDttmUtcOffset	D1_INSTALL_DTTM_UTC_OFFSET
Removal Date/Time	removalDateTime	D1_REMOVAL_DTTM
Removal Date/Time with UTC Offset	removalDttmUtdOffset	D1_REMOVAL_DTTM_UTC_OFFSET

Top of Page

C1-ExternalRepoBillingSnapshot

C1-ExternalRepoBillingSnapshot is used for the billing-based extracts, and includes the following information:

Field Name	Schema Element	Metadata Field
Billable Service Quantity	billSQ	BILL_SQ
Bill Segment End Date	endDate	END_DT
Consumption Days	duration	CONSUMPTION_DAYS
Bill Segment Estimate Indicator	estimateIndicator	C1_BS_ESTIMATE_IND
Calculated Amount	calculatedAmount	CALC_AMT
Unit of Measure	uom	UOM_CD
Time of Use	tou	TOU_CD
SQI	sqi	SQI_CD
Service Agreement ID	saId	SA_ID
Service Point ID	spId	SP_ID

Top of Page

D2-IntervalDataExtRepoSnapshot

D2-IntervalDataExtRepoSnapshot is used for interval-based consumption data extracts, and includes the following information:

Field Name	Schema Element	Metadata Field
CIS Service Agreement ID	cisSAId	CIS_EXT_SA_ID
Service Point ID	spId	D1_SP_ID
CIS Service Point ID	cisSPId	CIS_EXT_SP_ID
Unit of Measure	uom	D1_UOM_CD
Service Quantity Identifier	sqi	SVC_QTY_IDNTFR_CD
Seconds Per Interval	secondsPerInterval	SECONDS_PER_INTERVAL
Measurement Value	measurementValue	MSRMT_VAL
Measurement Date	measurementDate	MSRMT_DATE
Measurement Time	measurementTime	MSRMT_TIME
Estimate Indicator	estimateIndicator	ESTIMATION_IND_FLG
Estimate Indicator Description	estimateIndicatorDescription	DESCR100
UTC Offset	utcOffset	UTC_OFFSET

Top of Page

D2-IntervalDataExtRepoTOUSnap

D2-IntervalDataExtRepoTOUSnap is used for interval consumption data extracts mapped to TOU periods, and includes the following information:

Field Name	Schema Element	Metadata Field
CIS Service Agreement ID	cisSAId	CIS_EXT_SA_ID
Service Point ID	spId	D1_SP_ID
CIS Service Point ID	cisSPId	CIS_EXT_SP_ID
Unit of Measure	uom	D1_UOM_CD
Time of Use Period	tou	D1_TOU_CD
Service Quantity Identifier	sqi	SVC_QTY_IDNTFR_CD
Seconds Per Interval	secondsPerInterval	SECONDS_PER_INTERVAL
Measurement Value	measurementValue	MSRMT_VAL
Start Date	startDate	START_DT
Start Time	startTime	START_TM
Start Time UTC Offset	startUtcOffset	UTC_OFFSET
End Date	endDate	END_DT
End Time	endTime	D2_END_TIME
End UTC Offset	endUtcOffset	UTC_OFFSET

Top of Page

Example Extract File

The following example illustrates comma-separated interval data extracts based on the Consumption Extract Snapshot () data area:

19502793-60E-KMUS,714532246966,19502793-60E-KMSP,KWH, ,3,600,1.366,2015-01-01,07.00.00,No
19502793-60E-KMUS,714532246966,19502793-60E-KMSP,KWH, ,3,600,1.366,2015-01-01,08.00.0,No
19502793-60E-KMUS,714532246966,19502793-60E-KMSP,KWH, ,3,600,1.366.2015-01-01,09.00.0,No
19502793-60E-KMUS,714532246966,19502793-60E-KMSP,KWH, ,3,600,1.366,2015-01-01,10.00.00,No
19502793-60E-KMUS,714532246966,19502793-60E-KMSP,KWH, ,3,600,1.366,2015-01-01,11.00.00,No
19502793-60E-KMUS,714532246966,19502793-60E-KMSP,KWH, ,3,600,1.366,2015-01-01,12.00.00,No
19502793-60E-KMUS,714532246966,19502793-60E-KMSP,KWH, ,3,600,1.366,2015-01-01,13.00.00,No

Parent topic

Defining DataConnect Options