OpenCities Map - - Import coordinate and property information from a XLS/XLSX or CSV file

Create a design file and attach a geographic coordinate system. This ensures that the imported graphics will be drawn in the correct space.

In this example the Excel file contains location and property information for wells:

ID, BOOK, OPERATOR, STATUS, X_SURF, L_SURF, ELEVATION, TD, FIELD, LEASENAME, LEASENUM and COMPDATE.

For the import of CSV or Excel file, a metafile is created. This file has the extension VRT and is formatted according to the Open Source GDAL format, see https://gdal.org/ and https://gdal.org/en/stable/drivers/vector/vrt.html.

Import CSV with Points

Import a CSV file with the following steps:

Save the Excel file to the CSV format. In this example, the file is named Wells.csv.
Create a new text file named "Well Import.vrt" and copy/paste the following lines of text:

<OGRVRTDataSource>
  <OGRVRTLayer name="well">
    <SrcDataSource>well.csv</SrcDataSource>
    <SrcLayer>Well</SrcLayer>
    <GeometryType>wkbPoint</GeometryType>
    <LayerSRS>wgs84</LayerSRS>
    <GeometryField encoding="PointFromColumns" x="x_surf" y="y_surf"/>
  </OGRVRTLayer>
</OGRVRTDataSource>

In OpenCities Map, open the Map Imports dialog choose File > Import > Geospatial File Types > Map Imports/Exports dialog.
In the Map Imports/Exports dialog, select the Imports item and Imports > Right mouse click > New Import.
Select the new item and Right mouse click> Add File.
In the Open File dialog, Change Files of Type to All Files (*.*).
Select the VRT file created above.
Adjust the Feature Parameters (e.g. change the Point Type to cell or text), Feature Symbology, Cell Symbology or Text Symbology as desired.
When done, right click and choose Import.

The graphics are imported into the open design file and are automatically converted to OpenCities Map features with properties.
A sample dataset csv.zip for practice is attached for your reference (see the Attachments section above on this page).

Import XLS/XLSX with Points

The following VRT template can be used to create a custom VRT file to import an XLSX file with points. There is no need to save the XLSX file to a CSV file. The coordinates are listed in the columns Lat and Long. During import the coordinates are transformed to the coordinate system of the active model. The attached ZIP file addresses.zip contains an example.
Note: A VRT file can be created quickly using AI-tools, by dragging the XLS in the AI-dialog.

<OGRVRTDataSource>
  <OGRVRTLayer name="Blad1">
    <SrcDataSource>Address.xlsx</SrcDataSource>
    <SrcLayer>Blad1</SrcLayer>
    <GeometryType>wkbPoint</GeometryType>
    <LayerSRS>EPSG:4326</LayerSRS>
    <GeometryField encoding="PointFromColumns" x="Long" y="Lat"/>
    <Field name="Worker" type="String"/>
    <Field name="Postcode" type="String"/>
    <Field name="Address_complete" type="String"/>
    <Field name="City" type="String"/>
    <Field name="Contract_code" type="String"/>
    <Field name="Usage_Code" type="String"/>
    <Field name="Day" type="String"/>
    <Field name="Contract_Rule" type="String"/>
    <Field name="Address" type="String"/>
    <Field name="Contract_even_week" type="String"/>
    <Field name="Day_Number" type="Integer"/>
    <Field name="Fullfill_criteria" type="Integer"/>
    <Field name="Contract_invoice" type="String"/>
  </OGRVRTLayer>
</OGRVRTDataSource>

Import CSV with geometry as WKT

The following VRT templates can be used to create a custom VRT files based on linestrings, polygons and points. The geometry is stored in a column as WKT (Well-Known Text) format.
Edit the layer name, data source, and geometry type and field as needed.

Linestrings:

<OGRVRTDataSource>
  <OGRVRTLayer name="Linestrings">
    <SrcDataSource relativeToVRT="1">linestrings.csv</SrcDataSource>
    <GeometryType> wkbLineString</GeometryType>
    <LayerSRS>WGS84</LayerSRS>
    <GeometryField encoding="WKT" field="WKT"/>
  </OGRVRTLayer>
</OGRVRTDataSource>

Polygons:

<OGRVRTDataSource>
  <OGRVRTLayer name="Polygons">
    <SrcDataSource relativeToVRT="1">polygons.csv</SrcDataSource>
    <GeometryType> wkbPolygon</GeometryType>
    <LayerSRS>WGS84</LayerSRS>
    <GeometryField encoding="WKT" field="WKT"/>
  </OGRVRTLayer>
</OGRVRTDataSource>

Points:

<OGRVRTDataSource>
  <OGRVRTLayer name="Points">
    <SrcDataSource relativeToVRT="1">points.csv</SrcDataSource>
    <GeometryType> wkbPoint</GeometryType>
    <LayerSRS>WGS84</LayerSRS>
    <GeometryField encoding="PointFromColumns" x="X" y="Y"/>
  </OGRVRTLayer>
</OGRVRTDataSource>

Note: The element <LayerSRS> is not required. With this information the features are transformed to the local geographic coordinate system during import.