Export Fixed-Width Column Format Files

Facilities whose billing and customer information systems support fixed-width format files can integrate with AquaCUE via fixed-width file exports. All EDS v1 and v2 export types support this function.


About Fixed-Width Files
Fixed-width columns files are text files that have known and fixed column widths. Each line of the file represents one record with values for account_id, location_id, location address, billing address, meter_id, endpoint_sn, etc.

When configuring fixed-width column files, you are specifying columns based on which character the particular column starts on followed by number of characters in that column.

The term width describes the total number of characters per entry. Typical widths are 128, 256 and 512 characters.

AquaCUE supports any width file. To ensure compatibility, be sure the exported width matches whatever width your system expects to receive.


Fixed-width format file configurations are unique to each facility that uses them. AquaCUE lets you customize the configuration of your fixed-width exports using a standard Python specification format.

This format defines:

  • The width or number of characters in a column
  • Column content type, which can be either a character string or date-time
  • And filler used to separate columns with “blank” content.

 

TIP: Columns can be repeated as often as needed. For example, read_time can be used first to return the time that a read was taken and second to return the date that a read was taken.

Python Specification Format

When defining entries in a fixed-width file, use this format

'{!S:<W.W}'

to create a string at a given width.

Use this format

'{!S:<%m%d%y}'

to create date-time entries.

The symbols and characters between

'{ ... }'

perform these functions:

!S: tells AquaCUE to convert the values that come after it to a string.

< the less-than sign tells AquaCUE to left-justify the string. (> tells AquaCUE to right-justify the string; ^ centers the string).

W.W sets the width of the string, where W represents the value of the desired width. That is, replace W with the number of characters in a given column. For example, 10.10 tells AquaCUE to create a string that is 10 characters wide.

%x%x%x sets date-time parameters depending on the value entered after the % symbol. For example, %d returns the day. %H returns the hour. So that, for example, %m%d%y returns the month, day and year (09052019), while %H%M%S returns values for hours, minutes and seconds (00:00:00).

You can use other Python Specification arguments and parameters to customize your fixed-width API calls. For details, see Python Format Specification.

NOTE: The examples shown require Python 3.

Steps to Define Fixed-Width Files

When making API calls to export fixed-width format files, use square brackets [ ] to declare JSON Array.

Follow these steps to define fixed-width files:

  1. Use an open bracket ([) to start each entry.
    [
  2. Specify the width of the first column by entering the character number that the column starts on and number of characters in the column. For example,
    1, 15
  3. Enter the name of whatever it is that the column represents (account_id, location_address_line1, etc.). Be sure to put the column name in single quotes (‘). For example,
    'Account_id'
  4. Define the layout of the column using the Python Specification Format:'{:<15.15}’
  5. Close the entry by adding a closed bracket (]):
    ]

    When finished, your fixed-width file might look like this example:

1 export_format_spec = [

2     [1, 15, 'account_id', '{:<15.15}'],

3     [16, 15, 'endpoint_sn', '{:<15.15}'],

4     [31, 9, 'billing_read', '{:0>9.9}'],

5     [40, 16, 'filler', '{:<16.16}'],

6     [56, 1, 'service_point_id', '{:<1.1}'],

7     [57, 10, 'meter_id', '{:<10.10}'],

8     [67, 21, 'filler', '{:<21.21}'],

9     [88, 10, 'utility_use_2', '{:<10.10}'],

10    [98, 5, 'filler', '{:<5.5}'],

11    [103, 6, 'read_time', '{:%m%d%y}'],

12    [109, 6, 'read_time', '{:%H%M%S}'],

13    [115, 8, 'service_point_route', '{:<8}'],

14    [123, 4, 'filler', '{:<4.4}'],

15    [127, 2, 'filler', '{:0>2.2}']

16 ]

Line 2 [1, 15, ‘account_id’, ‘{:<15.15}’], sets the first column to a width of 15 characters that contain a string that is left-justified, and mapped to the account_id column header.

Line 3 [16, 15, ‘endpoint_sn’, ‘{:<15.15}’], sets the endpoint_sn column to a width of 15 left-justified characters that starts at the 16th character.

Line 4 [31, 9, ‘billing_read’, ‘{:0>9.9}’],sets the billing_read column to a width of 15 right-justified characters starting at the 31st character.

Lines 5, 8, 14 and 15 are column filler, meaning they define empty columns.

Line 15 uses the format

'{:0>2.2}'

that when used along with the argument

.format('')

will tell AquaCUE to add 00 to the record in character positions 127 and 128. For example,

'{:0>2.2}'.format('')

And read_time appears in lines 11 and 12, which lets you return read date and read time in separate columns.

Also notice the format for date:

{:%m%d%y}

and time:

{:%H%M%S}

Of course, you can get European dates by simply changing the month, day, year order to day, month, year:

{:%d%m%y}

 

CURL

#Use this cURL statement to post a request to export 
#a fixed-width format file that includes interval meter 
#reads for a given date range.

curl -X POST -uusername:password \
-H "Content-Type: application/x-www-form-urlencoded" \
-d Start_Date=2016-02-01T00:00:00Z \
-d End_Date=2016-02-29T23:59:59Z \
-d Output_Format='fixed_width' \
--data-urlencode Format_JSON='[
    [15,"account_id","{:<15.15}"],
    [15,"endpoint_sn","{:<15.15}"],
    [9,"read","{:0>9.9}"],
    [16,"filler","{:<16.16}"],
    [1,"service_point_id","{:<1.1}"],
    [10,"meter_id","{:<10.10}"],
    [21,"filler","{:<21.21}"],
    [5,"filler","{:<5.5}"],
    [6,"read_time","{:%m%d%y}"],
    [6,"read_time","{:%H%M%S}"],
    [5,"filler","{:<5.5}"],
    [2,"filler","{:0>2.2}"]
]' \
-d Limit=10 \
https://api.beaconama.net/v2/eds/range
KNOWLEDGE BASE



User Guide