Importing CSV file PostgreSQL using pgAdmin 4

Question

I'm trying to import a CSV file to my PostgreSQL but I get this error

ERROR:  invalid input syntax for integer: "id;date;time;latitude;longitude"
CONTEXT:  COPY test, line 1, column id: "id;date;time;latitude;longitude"

my csv file is simple

id;date;time;latitude;longitude
12980;2015-10-22;14:13:44.1430000;59,86411203;17,64274849

The table is created with the following code:

CREATE TABLE kordinater.test
(
    id integer NOT NULL,
    date date,
    "time" time without time zone,
    latitude real,
    longitude real
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE kordinater.test
    OWNER to postgres;

Hi, can you help me with this related post? stackoverflow.com/questions/60087614/… — The Great
– The Great, Commented Feb 6, 2020 at 4:15

Murtuza Z · Accepted Answer · 2018-04-18 10:25:00Z

3

You can use Import/Export option for this task.

Right click on your table
Select "Import/Export" option & Click
Provide proper option
Click Ok button

answered Apr 18, 2018 at 10:25

Murtuza Z

6,0951 gold badge32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

k-kundan · Accepted Answer · 2018-04-15 17:11:37Z

0

You should try this it must work

COPY kordinater.test(id,date,time,latitude,longitude) FROM 'C:\tmp\yourfile.csv' DELIMITER ',' CSV HEADER;

Your csv header must be separated by comma NOT WITH semi-colon or try to change id column type to bigint

to know more

answered Apr 15, 2018 at 17:11

k-kundan

466 bronze badges

Comments

Jim Jones · Accepted Answer · 2018-04-15 18:03:46Z

I believe the quickest way to overcome this issue is to create an intermediary temporary table, so that you can import your data and cast the coordinates as you please.

Create a similar temporary table with the problematic columns as text:

CREATE TEMPORARY TABLE tmp
(
    id integer,
    date date,
    time time without time zone,
    latitude text,
    longitude text
);

And import your file using COPY:

COPY tmp FROM '/path/to/file.csv' DELIMITER ';' CSV HEADER;

Once you have your data in the tmp table, you can cast the coordinates and insert them into the test table with this command:

INSERT INTO test (id, date, time, latitude, longitude) 
SELECT id, date, time, replace(latitude,',','.')::numeric, replace(longitude,',','.')::numeric from tmp;

One more thing:

Since you're working with geographic coordinates, I sincerely recommend you to take a look at PostGIS. It is quite easy to install and makes your life much easier when you start your first calculations with geospatial data.

Collectives™ on Stack Overflow

Importing CSV file PostgreSQL using pgAdmin 4

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related