CSV file formats
This topic contains details of the format for comma separated variable (CSV) files used in importing, exporting or updating Networks and Control Data sets.
The following sections are included:
- General File Formatting Information
- Formatting Details for Specific Field Types
- Using Data Exported from InfoWorks WS Pro
General File Formatting Information
The way in which data is distributed between files is quite flexible:
- Data can be contained in one file, or spread over several files
- Each file can contain data about one or more network object types
- More than one file can contain data about objects of the same type (duplication will cause errors)
- Files can reside in different directories
Each file consists of a combination of:
- one or more data type delimiter records, dividing the file into one or more sections
- each section contains
- a compulsory header record, listing the field names
- one data record per network object
Each file is treated as an individual entity. The data type delimiter and header record information is not carried forward from one file to another.
Data type delimiter
The data type delimiter takes the form of four stars followed by the name of the database table containing details of this type of network object.
Details of database table names can be found in the Network Data Fields section.
Example:
**** wn_node
The header delimiter can be omitted and filename used to denote object type instead by appending the table name minus the initial wn_ to the filename.
Example:
xxx_node.csv
tells InfoWorks WS Pro that the file begins with Node data.
Using the filename is a more visible way to organise your data if you have one network object type per file. This approach is used when you export from InfoWorks WS Pro using the Export each table to a different file option.
A filename can be used to define the data type at the start of a file and data type delimiters used to define different types further down the file, however this approach is unlikely to be of much benefit.
Deletes delimiter
The delimeter ****deletes can be used to define a list of objects to be deleted when updating a network. The delimiter is followed by a record for each object to be deleted containing table name and Asset ID of the object.
Example:
****deletes
ObjectTable, asset_id
wn_pipe, 28165073
wn_pipe, 28165094
wn_node, 10857075
wn_node, 10857076
Alternatively, append deletes to the filename to delete all objects in a file.
Note:
- Objects will be deleted regardless of the Type of update (Update and Add, Mixed, Deletes) selected on the Import / Update CSV Data dialog
- Asset IDs are always used regardless of whether the Update uses "Asset ID" field on the Import / Update CSV Data dialog is checked
Header record
The header record contains a list of the field names contained in the database table for the network object.
- The field names can be in any order.
- The list of field names does not have to be complete. The only essential fields are the primary key field(s). This field (or fields) uniquely identify the individual record within the database table (see below for list of primary key fields).
- The list may contain blanks or names not recognised by InfoWorks WS Pro. These fields will be ignored
For InfoWorks WS Pro the primary key fields are:
|
Nodes tables (Node, Transfer Node, Fixed Head Node, Hydrant, Reservoir, Well) |
The primary key for nodes is the node_id field. This field contains a string up to 30 characters long. The ID must be unique within the network |
|
Links tables (Pipe, Valve, Float Valve, Meter, Non-Return Valve, Pumping Station) |
Links require three fields: upstream_node_id, downstream_node_id, and link_suffix. The combination of the three fields must be unique within the network. |
|
Curves tables (Pump Curves, PRD Curves, Valve Curves) |
The primary key for pump curves is the ID field. This field contains a string up to 30 characters long. The ID must be unique within the network |
Control Data tables form a similar pattern to the network tables. There is a control table for every node and link table in the network. (There is no control table for the wn_pump table as pumps actually form part of pumping stations and the control rules are implemented at the pumping station level.)
All records in the control data are associated with a node or link in a network. For this reason the primary key fields match. For simulation to proceed the associated node or pipe described in the Control Data primary key must exist in the network.
Data records
The header record is followed by zero or more data records.
- The data records must follow the same structure as the header record, with all the fields in the same order.
- Zero data records is allowed so that any external software you may use to generate CSV data for use in InfoWorks WS Pro does not have to check whether records exist.
Escape Characters
In certain circumstances it is necessary to use escape characters to maintain the structure of the exported data.
- For Memo type fields (see Data Type Key) the text is surrounded by quotation marks only if it contains certain special characters , " \ CR (carriage return) or LF (line feed). In the exported text, " is changed to "", CR is changed to \r, LF is changed to \n and \ is changed to \\.
- For all other text fields the text is surrounded by quotation marks only if it contains " or , . In the exported text, " is changed to "".
Formatting Details for Specific Field Types
Text fields
Text fields must be contained in double quotation marks if they contain commas or other double quotation marks. Otherwise quotation marks are optional.
Arrays of values of a single type
Arrays of values of a single type, such as coordinates, can be defined as packed (as a single field) or as unpacked (each value is defined in a separate field).
Packed
The array is defined in a single field consisting of an opening curly brace, followed by a comma separated list of values, followed by a closing curly brace.
Example - array defining bends in a pipe
{389956.,110970.,389997.,111017.,390146.,110934.,390661.,111197.}
{389956.,110970.,389923.,111048.,390117.,111259.,390309.,111765.}
Unpacked
The array is defined with each value in a separate field with column headers for the first of each value type.
Example:
|
xs |
ys |
|
|
|
|
|
|
|
389956 |
110970 |
389997 |
111017 |
390146 |
110934 |
390661 |
111197 |
|
389956 |
110970 |
389923 |
111048 |
390117 |
111259 |
390309 |
111795 |
Arrays of values containing more than one field
These arrays contain lists of values that describe more complex parts of particular objects, for example:
- pump curve triplets
- demand by category at a particular node
- time varying profiles for pumps, valves. and fixed head nodes
- data associated with more than one pump in a pumping station
The arrays can be defined as packed (as a single field) or separately (in a separate block or separate file).
Packed
Each array is defined in a single field:
- The field starts and ends with opening and closing curly brackets. The field has one or more sub-fields containing the data for one of the items in the list above
- These sub-fields start and finish with opening and closing curly brackets. Sub-fields are separated by commas.
- Within each sub-field a number of fields are separated by commas. These fields must appear in a fixed order.
Example - Node Demand:
This example defines demand at a node. Two sub-fields each define demand in one Demand category:
{{Tourism,,10.000000,0},{DomSumer,,13.300000,0}}
Each sub-field contains 4 values, which have to appear in the correct order. In this case the order is:
- demand category
- demand per property per day (blank in this case)
- direct demand
- number of properties
Separately
Each array is defined in a separate block or separate file. Each value in the array is defined in a separate field.
The Data Type Delimiter takes the form of four starts followed by the Database Data Structure name.
Details of Database Data Structure names can be found in the NetWork Data Fields section.
Example - Pump Curve
|
****wn_pump_triplets |
|
|
|
|
id |
flow |
head |
power |
|
BPu-300 |
0 |
61.1 |
89.5 |
|
|
150 |
58 |
133 |
|
|
240 |
54.1 |
164.8 |
|
|
300 |
50 |
183.8 |
|
HP-200 |
0 |
48.5 |
46.7 |
|
|
100 |
46.2 |
70.5 |
|
|
200 |
40 |
98 |
|
|
300 |
22.3 |
117.5 |
Using Data Exported from InfoWorks WS Pro
When CSV files are exported from InfoWorks WS Pro, you have the option to export descriptive field names and units information. These are exported as additional records placed before the data records. If you export files including these additional records, and then try to import them, InfoWorks WS Pro will try to interpret the additional data as normal data records. You will almost certainly get error messages telling you that values in the file have the wrong size or type.
The additional export options are there for your convenience when viewing InfoWorks WS Pro data in other packages. You should make sure that data you intend to import back into InfoWorks WS Pro is exported without these additional records. The Select CSV Export Options dialog divides the options needed for re-import from the additional options and you should have no trouble exporting in the correct format. The additional records could also be removed by editing the file in another package prior to re-import.
Pruned sections of the network are not exported to CSV files. A Prune object is very complex, containing details of all the pruned links and nodes, as well as connectivity information.
