2

I am trying to import a CSV using pgAdmin4. I created the table using the query,

CREATE TABLE i210_2017_02_18
(
  PROBE_ID character varying(255),
  SAMPLE_DATE timestamp without time zone,
  LAT numeric,
  LON numeric,
  HEADING integer,
  SPEED integer,
  PROBE_DATA_PROVIDER character varying(255),
  SYSTEM_DATE timestamp without time zone
)

The header and first line of my CSV read is...

PROBE_ID,SAMPLE_DATE,LAT,LON,HEADING,SPEED,PROBE_DATA_PROVIDER,SYSTEM_DATE
841625st,2017-02-18 00:58:19,34.11968,-117.80855,91.0,9.0,FLEET53,2017-02-18 00:58:58

When I try to use the import dialogue, the process fails with Error Code 1:

ERROR:  invalid input syntax for type timestamp: "SAMPLE_DATE"
CONTEXT:  COPY i210_2017_02_18, line 1, column sample_date: "SAMPLE_DATE"

Nothing seems wrong to me - any ideas?

greedIsGoodAha
  • 103
  • 2
  • 8
  • First just try a simple "insert into i210_2017_02_18 " and if that fails reduce the columns you are inserting to. Confirm that works before trying to import – Slumdog Jun 08 '18 at 00:01
  • 1
    make sure column SAMPLE_DATE has data in uniform format – lat long Jun 08 '18 at 05:29

3 Answers3

1

According to your table structure, this import will fail in the columns HEADING and SPEED, since their values have decimals and you declared them as INTEGER. Either remove the decimals or change the column type to e.g. NUMERIC.

Having said that, just try this from pgAdmin (considering that file and database are in the same server):

COPY i210_2017_02_18 FROM '/home/jones/file.csv' CSV HEADER;

In case you're dealing with a remote server, try this using psql from your console:

$ cat file.csv | psql yourdb -c "COPY i210_2017_02_18 FROM STDIN CSV HEADER;"

You can also check this answer.

In case you really want to stick to the pgAdmin import tool, which I discourage, just select the Header option and the proper Delimiter:

enter image description here

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
-1

Have you set the Header-Option = TRUE? Import settings

that should work.

Moje
  • 1
-1

Step 1: Create a table. you can use a query or dashboard to create a table.

Step 2: Create the exact number of columns present in the CSV file. I would recommend creating columns using the dashboard.

Step 3: Click on your table_name in pgadmin you will see an option for import/export.

Step 4: provide the path of your CSV file, remember to choose delimiter as comma,

Umesh Rathod
  • 1
  • 1
  • Welcome to SO, it seems like the OP is already doing the steps that you have written here, please elaborate on why the steps are not working in their case – Manik Jul 08 '22 at 11:03