CODEWITHSUNDEEP
postgresqlpgadmin
niran
niran

Reputation: 1980

pgadmin import issue?

I am trying import the following csv file which is imported from PgAdmin.

CSV File:

"id";"email";"password";"permissions";"activated";"activation_code";"activated_at";"last_login";"persist_code";"reset_password_code";"first_name";"last_name";"created_at";"updated_at"
"11";"[email protected]";"$2y$10$gNfDFVCgqhQzCgKoiLuKfeskyCUPzK1akJvk6PdXt1DmMJooCuYpi";"";"t";"XY56HNcFHeAcAwzUhleYhvVbxxmOaMr57ifYEhxiUd";"";"2014-05-27 08:47:33";"$2y$10$g0LnAStmA/kEWuhNDfWleOjQeyo9maGvvIlfiJms/KpRiPAdfBWHm";"";"";"";"2014-05-27 07:51:07";"2014-05-27 08:47:33"
"5";"[email protected]";"$2y$10$dXODoI520pddcmiSXcS/OuiH.4K/87LEXeQRzvUl2k/Uth2HumpNy";"";"t";"4k8s1TbgrPfAMcNozVEP19MOQkCApQ0LA8bhGkF55A";"";"2014-05-21 21:18:06";"$2y$10$CefSnbQIzAJBo5PfbMdzKuhzpW17fHqh/frWabmljzJvv0A5Vkt1O";"";"";"";"2014-05-21 21:17:45";"2014-05-22 19:12:01"

And this the command I am using to import CSV file,

 DROP TABLE users;


 CREATE TABLE users
(
  id serial NOT NULL,
  email character varying(255) NOT NULL,
  password character varying(255) NOT NULL,
  permissions text,
  activated boolean NOT NULL DEFAULT true,
  activation_code character varying(255),
  activated_at timestamp without time zone,
  last_login timestamp without time zone,
  persist_code character varying(255),
  reset_password_code character varying(255),
  first_name character varying(255),
  last_name character varying(255),
  created_at timestamp without time zone,
  updated_at timestamp without time zone
);


COPY users (email,password,permissions,activated,activation_code,activated_at,last_login,persist_code,reset_password_code,first_name,last_name,created_at,updated_at)
FROM 'D:/test/db_backup/data_csv.csv'  WITH CSV HEADER delimiter ';'
        ;

But I am getting following error, not sure why.

**ERROR: extra data after last expected column SQL state: 22P04**

Please let me know what is the issue here?

Upvotes: 0

Views: 5208

Answers (1)

John Powell
John Powell

Reputation: 12571

Because you have 14 columns of data but only 13 inside your copy statement. Specifically, you are missing the ID column in your copy statment. If the data are in the same order as they are declared in the table, there is no need to put the column names in the copy table from statement.

COPY users FROM 'D:/test/db_backup/data_csv.csv'  CSV HEADER delimiter ';' null '\N';

will work just fine in your case -- note you don't need the WITH either.

EDIT. You need to explicitly set the null value, as in, null '\N'. If you use "", you can get an error such as: "CSV quote character must not appear in the NULL specification". Also, you can only specify HEADER in CSV mode, so if you want to use HEADER, and CSV, then you cannot use "" as a delimiter, and must use an explicit null '\N'.

I have updated my original answer to include this and imported your sample data, after replacing all the "" with \N.

Upvotes: 3

Related Questions