Skip to content

File conversions

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

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

  1. Spreadsheet

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.

There is an option at output to format the column widths in the sheets to make some of the data items more readable.

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.