This topic contains details of the format for comma separated variable (CSV) files used in importing and exporting Network data.
The following sections are included:
The way in which data is distributed between files is quite flexible:
Each file consists of a combination of:
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.
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 topic.
Click on the image below to display an example.
Example
**** cams_manhole (collection system)
**** wams_manhole (distribution network)
**** ams_asset (asset network)
The header delimiter can be omitted and filename used to denote object type instead by appending the table name minus the initial cams_, wams_ or ams_ to the filename.
Example:
xxx_node.csv
tells InfoAsset Manager 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 InfoAsset Manager 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.
The delimiter ****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.
Click on the image below to show an example.
Example
****deletes
ObjectTable, asset_id
cams_pipe, 28165073
cams_pipe, 28165094
cams_manhole, 10857075
cams_manhole, 10857076
Alternatively, append deletes to the filename to delete all objects in a file.
The header record contains a list of the field names contained in the database table for the network object.
For InfoAsset Manager the primary key fields are:
|
Nodes tables |
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 |
|
Pipes tables |
Pipes require three fields: upstream_node_id, downstream_nde_id and link_suffix. The combination of the three fields must be unique within the network. |
The header record is followed by zero or more data records.
In certain circumstances it is necessary to use escape characters to maintain the structure of the exported data.
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, such as coordinates, can be defined as packed (as a single field) or as unpacked (each value is defined in a separate field).
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.
Click on the image below to show an example of array defining bends in a pipe.
Example
{389956.,110970.,389997.,111017.,390146.,110934.,390661.,111197.}
{389956.,110970.,389923.,111048.,390117.,111259.,390309.,111765.}
The array is defined with each value in a separate field with column headers for the first of each value type.
Click on the image below to show an example of unpacked array.
Example
xs |
ys |
|
|
|
|
|
|
389956 |
110970 |
389997 |
111017 |
390146 |
110934 |
390661 |
111197 |
389956 |
110970 |
389923 |
111048 |
390117 |
111259 |
390309 |
111795 |
These arrays contain lists of values that describe more complex parts of particular objects, for example:
The arrays can be defined as packed (as a single field) or separately (in a separate block or separate file).
Each array is defined in a single field:
Click on the image below to show an example with CCTV Survey details.
Example
{{3347.,0,0.,,ST,,0.,,0,0,0.,0.,,,},
{0.,0,0.,,MH,,0.,,0,0,0.,0.,HK41204002,,},
{0.,0,0.,,WL,,0.,,0,0,5.,0.,,,},{3410.,0,0.6,,OJS,,0.,,0,0,0.,0.,,,},
0.,0,0.6,,ELJ,,0.,,3,5,0.,0.,,,},
{3434.,0,2.2999999999999998,,OJS,,0.,,0,0,0.,0.,,,},
{0.,0,2.2999999999999998,,ELJ,,0.,,1,5,0.,0.,,,},
{3448.,17,3.2000000000000002,,GP,,0.,,0,0,0.,0.,,,},
{0.,0,3.2000000000000002,,ELJ,,0.,,3,7,0.,0.,,,},
{3455.,18,3.7000000000000002,,FM,,0.,,10,5,0.,0.,,,},
{3506.,0,78.,,FH,,0.,,0,0,0.,0.,,,}}
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.
Click on the image below to show an example with CCTV Survey details.
Example
****cams_cctv_survey_details
id |
video_no |
photo_no |
distance |
cd |
code |
MH334561XU1 |
3347 |
0 |
0 |
|
ST |
|
|
0 |
0 |
0 |
|
MH |
|
|
0 |
0 |
0 |
|
WL |
|
|
3410 |
0 |
0.6 |
|
OJS |
|
|
0 |
0 |
0.6 |
|
ELJ |
|
|
3434 |
0 |
2.3 |
|
OJS |
|
|
0 |
0 |
78 |
|
FH |
When CSV files are exported from InfoAsset Manager, 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, InfoAsset Manager 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 InfoAsset Manager data in other packages. You should make sure that data you intend to import back into InfoAsset Manager 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.