# Loading CSV files

It is recommended that you have a basic understanding of database terminology before following the guides in the tabs below. The guides contain generic instructions, and it is recognised that there are multiple ways to load CSV files into databases which may be more suitable to your environment and existing processes.

Prior to loading the data into a database, it is necessary to create the relevant tables in the database. We have supplied the DDL statements that can be accessed in our [OS NGD Resources GitHub repository](https://github.com/OrdnanceSurvey/osngd-resources/tree/main/database-resources).

{% tabs %}
{% tab title="PostgreSQL" %}
These instructions are based on [PostgreSQL](https://www.postgresql.org) version 14, but should work for all supported versions. The instructions assume that you have set-up your database with the [PostGIS](https://postgis.net/) spatial extension. 

Once connected to your PostgreSQL database, with the relevant schema and table created, the CSV file can be loaded with the following SQL statement using the [COPY command](https://www.postgresql.org/docs/current/sql-copy.html):

```sql
COPY destination_schema.destination_table 
    FROM 'source_path_to_csv_file\source_name_of_csv_file.csv'
    DELIMITER ','
    CSV HEADER
    ENCODING 'UTF8';
```

{% hint style="info" %}
PostGIS will automatically store the geometry data that is supplied in Well-Known Text (WKT) format.
{% endhint %}

{% hint style="warning" %}
There is a known bug affecting PostgreSQL versions 11, 12 and 13 in Windows environments, where the `COPY` command cannot load files larger than 4GB. As a workaround, version 14 (or later) of the `COPY` command can be used to load data into the affected database versions.

For reference, the error message states `ERROR: could not stat file.`
{% endhint %}
{% endtab %}

{% tab title="SQL Server" %}
These instructions are based on [Microsoft SQL Server](https://www.microsoft.com/sql-server) 2019, but should work for all supported versions.

Once connected to your SQL Server database, with the relevant schema and table created, the CSV file can be loaded with the following SQL statement using the [BULK INSERT command](https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql):

<pre class="language-sql"><code class="lang-sql"><strong>BULK INSERT destination_schema.destination_table
</strong>FROM 'source_path_to_csv_file\source_name_of_csv_file.csv'
WITH (
    FORMAT = 'CSV',
    FIRSTROW = 2
);
</code></pre>

{% hint style="info" %}
It is not possible to `BULK INSERT` the geometries directly in their Well-Known Text (WKT) format.&#x20;
{% endhint %}

However, it is possible to change the destination `geometry` column to a `nvarchar(max)` type, and then either post process the table or use a a computed column to generate a geometry type column (see code examples below).&#x20;

{% code title="Post process example" overflow="wrap" %}

```sql
-- Add new geometry column
ALTER TABLE destination_schema.destination_table
ADD new_geometry_column_name geometry;

-- Set new geometry column using existing WKT column
UPDATE destination_schema.destination_table
SET [new_geometry_column_name] =  geometry::STGeomFromText(destination_table.geometry, /* EPSG_CODE_OF_GEOM */); 

-- Optional drop original WKT column
ALTER TABLE destination_schema.destination_table
DROP COLUMN geometry;
```

{% endcode %}

{% code title="Computed column example" overflow="wrap" %}

```sql
ALTER TABLE destination_schema.destination_table
ADD new_computed_column_name AS
    geometry::STGeomFromText(destination_table.geometry, /* EPSG_CODE_OF_GEOM */) persisted;
```

{% endcode %}
{% endtab %}

{% tab title="Oracle" %}
It is not possible to load OS NGD CSV files into an Oracle database using the default SQL\*Loader utility. The geometries are supplied in Well-Known Text (WKT) format and some of them are too large for SQL\*Loader to process.

However, with the relevant schema and table created in your Oracle database, the CSV file can be loaded using ETL (extract, transform, load) tools, for example, [GDAL](https://gdal.org/) or [FME](https://www.safe.com/).
{% endtab %}
{% endtabs %}