2

I'm new to pgAdmin and GIS DB in general. I want to upload a CSV file to pgAdmin v4.1 and I'm trying to understand the logic to do so. I am able to do this by creating a new table under the desired DB and then manually defined the column (name, type etc.), only then I am able to load the CSV into pgAdmin using the GUI. This seems a bit cumbersome way to import a CSV file, because let's say I have a CSV file with 200 columns, it is not practical to define them all manually, and there must be a way to tell pgAdmin: this is the CSV file, now get the columns by yourself and get (or at least assume) the columns type, ad create a new table, much similar to how pandas reads CSV in python. As I'm new to this topic, please elaborate your answer\comment as much as possible.

user88484
  • 1,249
  • 1
  • 13
  • 34

2 Answers2

1

NO: Unfortunately, we can only import CSV after the table is created.

YES: There is no GUI method, but:

  1. There is a utility called pgFutter which will do exactly what you want. This is a command line utility. Here are the binaries.

  2. You can write a function that does that. Here is an example.

jlee88my
  • 2,935
  • 21
  • 28
0

I would look into using GDAL to upload your CSV file into postgis. I used this recently to do a similar job.

ogr2ogr -f "PostgreSQL" -lco GEOMETRY_NAME=geometry -lco FID=gid PG:"host=127.0.0.1 user=username dbname=dbname password=********" postgres.vrt -nln th_new_data_2019 -t_srs EPSG:27700

Code used to upload a csv to postgis and transform the coordinate system.

-f = file format name

output file format name, some possible values are: -f "ESRI Shapefile" -f "TIGER" -f "MapInfo File" -f "GML" -f "PostgreSQL

-lco = NAME=VALUE: Layer creation option (format specific)

-nln name: Assign an alternate name to the new layer

-t_srs srs_def: target spatial reference set. The coordinate systems that can be passed are anything supported by the OGRSpatialReference.SetFromUserInput() call, which includes EPSG PCS and GCSes (i.e. EPSG:4296), PROJ.4 declarations (as above), or the name of a .prj file containing well known text.

The best and simplest guide for installing GDAL that I have used is : https://sandbox.idre.ucla.edu/sandbox/tutorials/installing-gdal-for-windows

Hutch
  • 411
  • 10
  • 32
  • 1
    hi, thanks for the answers, but I eventually used QGIS as a mediator for that, meaning that I uploaded the CSV to QGIS and from QGIS to PgAdmin using the DB manager – user88484 Feb 01 '19 at 16:03
  • Using QGIS is the best solution I think, it's also dealing with encoding problems. Because my CSV contains Chinese characters as column names, other methods can not correctly import even when I already use the UTF-8 encoding. – allenyllee Nov 24 '22 at 05:36