Skip to content

File conversions

Version 3+ of the AGS Desktop Validator provides for the following file conversions:

Info

These file conversion tools are provided for use to make the AGS4 data accessible to software such as spreadsheets, databases and GIS. They DO NOT provide conversion to any other format of AGS data.

From AGS4

  1. CSV (zip file folder)
  2. Spreadsheet (workbook)
  3. SQLite (database)
  4. JSON (row-wise)
  5. JSON (column-wise)
  6. GeoPackage (database)
  7. Spatialite (database)

Note

Any number of Export from AGS4 file types can be converted in a single operation, by selecting the check boxes.

To AGS4

Details of each conversion are presented below:

AGS4 to CSV (zip file folder)

The output is a zip file containing a single folder with many CSV files, one for each group in the AGS4 file.

The CSV files contain all the header rows present in the AGS4 file groups. Data items are NOT enclosed in double quotes so that the csv files can be used for calculations/plots etc. Users should specify data types when importing them into software such as a spreadsheet.

To view the files, extract the zip file archive.

AGS4 to Spreadsheet (workbook)

The output is a single workbook with an .xlsx file extension. The workbook contains many sheets, one for each group in the AGS4 file.

The sheets contain all the header rows present in the AGS4 file groups and all data items are formatted as strings in the cells.

Tip

The UNIT and TYPE heading rows can be removed from the workbook sheets files using the option provided on the file conversion page. Removing these rows makes the data easier to use.

Tip

To improve the speed of conversion the column widths are not formatted. This can be over-riden using the option available on the file conversion page.

AGS4 to SQLite

The output is a single SQLite database file. The database contain many tables, one for each group in the AGS4 file.

The tables only contain the headings as field names. The UNIT and TYPE heading rows are extracted and put in a single table (UNTY) for all groups.

The data is converted to numeric values where the TYPE represents a numeric type.

Note

There are no relationships created between the groups, they are independent of each other.

To view the data in the SQLite database, open it in an application such as one of the following applications:

DB Browser DBeaver Community SQLiteStudio Beekeeper Studio

AGS4 to JSON (row-wise)

The output is a single JSON file. The information is split into two main objects: (1) the definition of the groups ('DEFN') and the data ('MAIN').

The data items are presented as a list of headings lists of values.

All data items are returned as JSON strings (i.e. enclosed within double quotes).

I simple excerpt from a JSON file is shown below:

{
    "DEFN": [{
        "GROUP": "BKFL",
        "HEADING": ["LOCA_ID", "BKFL_TOP", "BKFL_BASE", "BKFL_DESC", "BKFL_LEG", "BKFL_DATE", "BKFL_REM", "FILE_FSET"],
        "UNIT": ["", "m", "m", "", "", "yyyy-mm-dd", "", ""],
        "TYPE": ["ID", "2DP", "2DP", "X", "PA", "DT", "X", "X"]
    }],
    "MAIN": [{
        "GROUP": "BKFL",
        "HEADING": ["LOCA_ID", "BKFL_TOP", "BKFL_BASE", "BKFL_DESC", "BKFL_LEG", "BKFL_DATE", "BKFL_REM", "FILE_FSET"],
        "DATA": [
            ["BH502", "0.00", "0.15", "Asphalt.", "907", "", "", ""],
            ["BH502", "0.15", "0.40", "Concrete.", "906", "", "", ""],
            ["BH502", "0.40", "1.65", "Bentonite seal.", "903", "", "", ""],
            ["BH502A", "0.00", "0.15", "Asphalt.", "907", "", "", ""],
            ["BH502A", "0.15", "0.40", "Concrete.", "906", "", "", ""],
            ["BH502A", "0.40", "1.00", "Bentonite seal.", "903", "", "", ""],
            ["BH502B", "0.00", "0.15", "Asphalt.", "907", "", "", ""],
            ["BH502B", "0.15", "0.40", "Concrete.", "906", "", "", ""],
            ["BH502B", "0.40", "28.50", "Bentonite seal.", "903", "", "", ""]

        ]
    }]

AGS4 to JSON (Column-wise)

The output is a single JSON file. The information is split into two main objects: (1) the definition of the groups ('DEFN') and the data ('MAIN').

The data items are presented as heading keys and a list of values.

All data items are returned as JSON strings (i.e. enclosed within double quotes).

I simple excerpt from a JSON file is shown below:

{
    "DEFN": [{
        "GROUP": "BKFL",
        "HEADING": ["LOCA_ID", "BKFL_TOP", "BKFL_BASE", "BKFL_DESC", "BKFL_LEG", "BKFL_DATE", "BKFL_REM", "FILE_FSET"],
        "UNIT": ["", "m", "m", "", "", "yyyy-mm-dd", "", ""],
        "TYPE": ["ID", "2DP", "2DP", "X", "PA", "DT", "X", "X"]
    }],
    "MAIN": [{
        "GROUP": "BKFL",
        "DATA": {
            "LOCA_ID": ["BH502", "BH502", "BH502", "BH502A", "BH502A", "BH502A", "BH502B", "BH502B", "BH502B"],
            "BKFL_TOP": ["0.00", "0.15", "0.40", "0.00", "0.15", "0.40", "0.00", "0.15", "0.40"],
            "BKFL_BASE": ["0.15", "0.40", "1.65", "0.15", "0.40", "1.00", "0.15", "0.40", "28.50"],
            "BKFL_DESC": ["Asphalt.", "Concrete.", "Bentonite seal.", "Asphalt.", "Concrete.", "Bentonite seal.", "Asphalt.", "Concrete.", "Bentonite seal."],
            "BKFL_LEG": ["907", "906", "903", "907", "906", "903", "907", "906", "903"],
            "BKFL_DATE": ["", "", "", "", "", "", "", "", ""],
            "BKFL_REM": ["", "", "", "", "", "", "", "", ""],
            "FILE_FSET": ["", "", "", "", "", "", "", "", ""]
        }
    }]
}

AGS4 to GeoPackage (OGC)

The output is a single (OGC)GeoPackage database file. The database contain many tables, one for each group in the AGS4 file.

The tables only contain the headings as field names. The UNIT and TYPE heading rows are extracted and put in a single table (UNTY) for all groups.

The data is converted to numeric values where the TYPE represents a numeric type.

Note

There are no relationships created between the groups, they are independent of each other.

The LOCA co-ordinate data items in the LOCA_NATE and LOCA_NATN are used to populate a geometry data in the database.

Note

At the moment the output is hard coded to the OSGB 27000 co-ordinate reference system.

To view the data in the GeoPackage file, import it, or drag and drop it into a GIS application, such as QGIS. The exploratory hole points will be automatically placed on a map. Alternatively any one of the applications listed under SQLite can be used to access the data.

AGS4 to SpatiaLite

The output is a single Spatialite databse database file. The database contain many tables, one for each group in the AGS4 file.

The tables only contain the headings as field names. The UNIT and TYPE heading rows are extracted and put in a single table (UNTY) for all groups.

The data is converted to numeric values where the TYPE represents a numeric type.

Note

There are no relationships created between the groups, they are independent of each other.

The LOCA co-ordinate data items in the LOCA_NATE and LOCA_NATN are used to populate a geometry data ('exp_points' table) in the database.

Note

At the moment the output is hard coded to the OSGB 27000 co-ordinate reference system.

To view the data in the Spatialite file, import it, or drag and drop it into a GIS application, such as QGIS. The exploratory hole points will be automatically placed on a map. Alternatively any one of the applications listed under SQLite can be used to access the data.

Note

The SpatiaLite database includes spatial functions, enabling spatia queries to be performed on the data, such as listing points enclosed by a polygon or buffer to a point or line.

Spreadsheet (workbook) to AGS4

This converts a spreadsheet to an AGS4 file. The spreadsheet must be in xlsx format and have one group per sheet.', Each sheet must have headings in the correct format. To understand the correct format please convert an AGS file to a spreadsheet. Note that the conversion process is blunt and no error checking is carried out during conversion. This is only recommended where minor edits are needed rather than new AGS file creation.