Export Non-spatial CSV files

Owned by Kim Stimpson
Last updated: 22 Apr, 2021

Problem

When running a task to export a table from the SDW Postgres database using Studio on the Studio-ETL server to a location on the ETL server it produces an error. Studio only seems to allow exporting a non-spatial csv to a location on the SDW server using this method.

Solution

Unfortunately the Postgres COPY command only supports writing results to a path that's local to the database server. The alternative is to use a Spatial Data Transformation Task specifying the CSV as the output format. The only issue with this is that, if the CSV file already exists, it will need to be deleted before exporting.

Step-by-step guide

  • Step 1 - Create a Job to Export your data
  • Step 2 - Create a Program Task to delete the CSV file if it already exists. You need to ensure paths are quoted if they contain spaces as per the example below:
  • Step 3 - Create a Spatial Data Transformation Task to export the data to CSV
    • Leave Source Table blank as we are going to specify this in the -sql.
    • Select Comma-Delimited Text File as the Output and specify a path to the filename you wish to create.
    • Tick Force Geometry: and pick NONE.
    • Tick Show export mode and use the -sql SELECT option to specify the source table.
      • Escape any double quotes in the -sql value with a backslash.
    • If you wish to limit the fields included then you can ad them to your -select option (https://gdal.org/programs/ogr2ogr.html#cmdoption-ogr2ogr-select) to specify a Comma-delimited list of fields.