csv import error

[Eugenio Trumpy]

Hello,

I'm trying to import data using a csv file,
but I got an error:


ERROR: column "key;daprof;aprof;tipo;valore;note;oid;unit_mis" of relation "assorb" does not exist
LINE 1: INSERT INTO "info_pozzi_hydrocarbon"."assorb" ("key;daprof;a...
^
In statement:

INSERT INTO "info_pozzi_hydrocarbon"."assorb" ("key;daprof;aprof;tipo;valore;note;oid;unit_mis")
VALUES ('1001334;19.1;21;A;6;;;11')
My sql statement for the table that I would like to populate is:

-- Table: info_pozzi_hydrocarbon.assorb

-- DROP TABLE info_pozzi_hydrocarbon.assorb;

CREATE TABLE info_pozzi_hydrocarbon.assorb
(
key integer,
daprof double precision,
aprof double precision,
tipo character(1),
valore double precision,
note character(254),
oid serial NOT NULL,
unit_mis smallint,
CONSTRAINT assorb_pk PRIMARY KEY (oid),
CONSTRAINT assorb_fk FOREIGN KEY (key)
REFERENCES pozzi (key) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT unit_m_fk FOREIGN KEY (unit_mis)
REFERENCES info_cod.unita (unita) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);


the csv file is like the following (it is only a part):

key;daprof;aprof;tipo;valore;note;oid;unit_mis
1001334;19.1;21;A;6;;;11
1001334;93.5;94;A;30;;;11
1001334;94;115;A;20;;;11
1001334;154.5;255;A;644;;;11
1001334;273;282;A;4;;;11
1001334;298;309;A;7;;;11
1001334;432;1224;P;1850;;;11
4277001;121;901;A;397;ALLARGAMENTO FORO;;11
4277001;121;901;A;96;PERFORAZIONE;;11


The filed order it is the same and also the decimal separator is set as dot. I set also the delimiter for fields.
I don't understand what was the problem.

Can somebody help me?

Eugenio

[FarjadFarid(ChkNet)]

Hi,



Quotation marks should be around both the name of
each and every column and their values.



Also replace column comas as separation character
for sql insert statement.



What has happened here is that the values from CSV
are directly into sql.



Hope this helps.



Best Regards





Farjad Farid



From: pgsql-general-***@postgresql.org
[mailto:pgsql-general-***@postgresql.org] On
Behalf Of Eugenio Trumpy
Sent: 23 September 2014 11:26
To: pgsql-***@postgresql.org
Subject: [GENERAL] csv import error



Hello,

I'm trying to import data using a csv file,
but I got an error:


ERROR: column
"key;daprof;aprof;tipo;valore;note;oid;unit_mis"
of relation "assorb" does not exist
LINE 1: INSERT INTO
"info_pozzi_hydrocarbon"."assorb"
("key;daprof;a...

^

In statement:

INSERT INTO "info_pozzi_hydrocarbon"."assorb"
("key;daprof;aprof;tipo;valore;note;oid;unit_mis")
VALUES ('1001334;19.1;21;A;6;;;11')


My sql statement for the table that I would like
to populate is:

-- Table: info_pozzi_hydrocarbon.assorb

-- DROP TABLE info_pozzi_hydrocarbon.assorb;

CREATE TABLE info_pozzi_hydrocarbon.assorb
(
key integer,
daprof double precision,
aprof double precision,
tipo character(1),
valore double precision,
note character(254),
oid serial NOT NULL,
unit_mis smallint,
CONSTRAINT assorb_pk PRIMARY KEY (oid),
CONSTRAINT assorb_fk FOREIGN KEY (key)
REFERENCES pozzi (key) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT unit_m_fk FOREIGN KEY (unit_mis)
REFERENCES info_cod.unita (unita) MATCH
SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);


the csv file is like the following (it is only a
part):

key;daprof;aprof;tipo;valore;note;oid;unit_mis
1001334;19.1;21;A;6;;;11
1001334;93.5;94;A;30;;;11
1001334;94;115;A;20;;;11
1001334;154.5;255;A;644;;;11
1001334;273;282;A;4;;;11
1001334;298;309;A;7;;;11
1001334;432;1224;P;1850;;;11
4277001;121;901;A;397;ALLARGAMENTO FORO;;11
4277001;121;901;A;96;PERFORAZIONE;;11


The filed order it is the same and also the
decimal separator is set as dot. I set also the
delimiter for fields.
I don't understand what was the problem.

Can somebody help me?

Eugenio

[Rémi Cura]

Why don't you use "COPY" ?
Cheers,
Rémi-C

2014-09-23 12:50 GMT+02:00 FarjadFarid(ChkNet) <

Hi,
Quotation marks should be around both the name of each and every column
and their values.
Also replace column comas as separation character for sql insert
statement.
What has happened here is that the values from CSV are directly into sql.
Hope this helps.
Best Regards
Farjad Farid
*Sent:* 23 September 2014 11:26
*Subject:* [GENERAL] csv import error
Hello,
I'm trying to import data using a csv file,
ERROR: column "key;daprof;aprof;tipo;valore;note;oid;unit_mis" of relation "assorb" does not exist
LINE 1: INSERT INTO "info_pozzi_hydrocarbon"."assorb" ("key;daprof;a...
^
*In statement:*
INSERT INTO "info_pozzi_hydrocarbon"."assorb"
("key;daprof;aprof;tipo;valore;note;oid;unit_mis") VALUES
('1001334;19.1;21;A;6;;;11')
-- Table: info_pozzi_hydrocarbon.assorb
-- DROP TABLE info_pozzi_hydrocarbon.assorb;
CREATE TABLE info_pozzi_hydrocarbon.assorb
(
key integer,
daprof double precision,
aprof double precision,
tipo character(1),
valore double precision,
note character(254),
oid serial NOT NULL,
unit_mis smallint,
CONSTRAINT assorb_pk PRIMARY KEY (oid),
CONSTRAINT assorb_fk FOREIGN KEY (key)
REFERENCES pozzi (key) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT unit_m_fk FOREIGN KEY (unit_mis)
REFERENCES info_cod.unita (unita) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
key;daprof;aprof;tipo;valore;note;oid;unit_mis
1001334;19.1;21;A;6;;;11
1001334;93.5;94;A;30;;;11
1001334;94;115;A;20;;;11
1001334;154.5;255;A;644;;;11
1001334;273;282;A;4;;;11
1001334;298;309;A;7;;;11
1001334;432;1224;P;1850;;;11
4277001;121;901;A;397;ALLARGAMENTO FORO;;11
4277001;121;901;A;96;PERFORAZIONE;;11
The filed order it is the same and also the decimal separator is set as dot. I set also the delimiter for fields.
I don't understand what was the problem.
Can somebody help me?
Eugenio

[FarjadFarid(ChkNet)]

Hi,



Quotation marks should be around both the name of
each and every column and their values.



As the columns names are all lower case. You may
wish to remove all quotation marks which is much
easier.

But character value needs quotation mark.



Also replace the semi-column as column separation
character with commas for sql insert statement.

What has happened here is that the values from CSV
are directly inserted into sql. It needs a little
bit of extra work.

Hope this helps.



Best Regards





Farjad Farid



From: pgsql-general-***@postgresql.org
[mailto:pgsql-general-***@postgresql.org] On
Behalf Of Eugenio Trumpy
Sent: 23 September 2014 11:26
To: pgsql-***@postgresql.org
Subject: [GENERAL] csv import error



Hello,

I'm trying to import data using a csv file,
but I got an error:


ERROR: column
"key;daprof;aprof;tipo;valore;note;oid;unit_mis"
of relation "assorb" does not exist
LINE 1: INSERT INTO
"info_pozzi_hydrocarbon"."assorb"
("key;daprof;a...

^

In statement:

INSERT INTO "info_pozzi_hydrocarbon"."assorb"
("key;daprof;aprof;tipo;valore;note;oid;unit_mis")
VALUES ('1001334;19.1;21;A;6;;;11')


My sql statement for the table that I would like
to populate is:

-- Table: info_pozzi_hydrocarbon.assorb

-- DROP TABLE info_pozzi_hydrocarbon.assorb;

CREATE TABLE info_pozzi_hydrocarbon.assorb
(
key integer,
daprof double precision,
aprof double precision,
tipo character(1),
valore double precision,
note character(254),
oid serial NOT NULL,
unit_mis smallint,
CONSTRAINT assorb_pk PRIMARY KEY (oid),
CONSTRAINT assorb_fk FOREIGN KEY (key)
REFERENCES pozzi (key) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT unit_m_fk FOREIGN KEY (unit_mis)
REFERENCES info_cod.unita (unita) MATCH
SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);


the csv file is like the following (it is only a
part):

key;daprof;aprof;tipo;valore;note;oid;unit_mis
1001334;19.1;21;A;6;;;11
1001334;93.5;94;A;30;;;11
1001334;94;115;A;20;;;11
1001334;154.5;255;A;644;;;11
1001334;273;282;A;4;;;11
1001334;298;309;A;7;;;11
1001334;432;1224;P;1850;;;11
4277001;121;901;A;397;ALLARGAMENTO FORO;;11
4277001;121;901;A;96;PERFORAZIONE;;11


The filed order it is the same and also the
decimal separator is set as dot. I set also the
delimiter for fields.
I don't understand what was the problem.

Can somebody help me?

Eugenio

[Eugenio Trumpy]

Hi,

I adjusted the csv, changing the semi-column with column and inserting the quote for character.
Now it seems to be better but I got another error. This last is due to the fact that oid column in my
postgresql table is a serial and I did not filled it in csv because I was thinking that would have been
filled-in automatically during the data import.

What do you suggest on this regard? How can I solve?

E.

From: ***@checknetworks.com
To: ***@hotmail.com; pgsql-***@postgresql.org
Subject: RE: [GENERAL] csv import error
Date: Tue, 23 Sep 2014 12:03:15 +0100

Hi, Quotation marks should be around both the name of each and every column and their values. As the columns names are all lower case. You may wish to remove all quotation marks which is much easier. But character value needs quotation mark. Also replace the semi-column as column separation character with commas for sql insert statement. What has happened here is that the values from CSV are directly inserted into sql. It needs a little bit of extra work. Hope this helps. Best Regards Farjad Farid From: pgsql-general-***@postgresql.org [mailto:pgsql-general-***@postgresql.org] On Behalf Of Eugenio Trumpy
Sent: 23 September 2014 11:26
To: pgsql-***@postgresql.org
Subject: [GENERAL] csv import error Hello,

I'm trying to import data using a csv file,
but I got an error:


ERROR: column "key;daprof;aprof;tipo;valore;note;oid;unit_mis" of relation "assorb" does not existLINE 1: INSERT INTO "info_pozzi_hydrocarbon"."assorb" ("key;daprof;a... ^In statement:

INSERT INTO "info_pozzi_hydrocarbon"."assorb" ("key;daprof;aprof;tipo;valore;note;oid;unit_mis") VALUES ('1001334;19.1;21;A;6;;;11')
My sql statement for the table that I would like to populate is:

-- Table: info_pozzi_hydrocarbon.assorb

-- DROP TABLE info_pozzi_hydrocarbon.assorb;

CREATE TABLE info_pozzi_hydrocarbon.assorb
(
key integer,
daprof double precision,
aprof double precision,
tipo character(1),
valore double precision,
note character(254),
oid serial NOT NULL,
unit_mis smallint,
CONSTRAINT assorb_pk PRIMARY KEY (oid),
CONSTRAINT assorb_fk FOREIGN KEY (key)
REFERENCES pozzi (key) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT unit_m_fk FOREIGN KEY (unit_mis)
REFERENCES info_cod.unita (unita) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);

the csv file is like the following (it is only a part):
key;daprof;aprof;tipo;valore;note;oid;unit_mis1001334;19.1;21;A;6;;;111001334;93.5;94;A;30;;;111001334;94;115;A;20;;;111001334;154.5;255;A;644;;;111001334;273;282;A;4;;;111001334;298;309;A;7;;;111001334;432;1224;P;1850;;;114277001;121;901;A;397;ALLARGAMENTO FORO;;114277001;121;901;A;96;PERFORAZIONE;;11


The filed order it is the same and also the decimal separator is set as dot. I set also the delimiter for fields.
I don't understand what was the problem.

Can somebody help me?

Eugenio

[FarjadFarid(ChkNet)]

Hi Eugenio,



How you got about resolving this depends on your
project, the size of data.



For a simple case, which this seems to be. Simply
don't insert the serial column. E.g. remove both
name of the column and its corresponding value in
the insert statement.

Postgresql will insert these automatically.



If the size of the data is larger or there are
dependencies on the OID value elsewhere in the DB
then recreated the target table but without making
the column 'serial'. But actually creating all
columns. And inserting all column values.



Once the operation is done. Then manually create
serial sequencer and attach it to the OID column.



The disadvantage of this is in error detection and
correction of the process.

If there are errors in CSV files ,which often are
as sometimes they are edited manually, then it
will be more time consuming to correct. Depending
on the size of data in CSV file.



Hope this helps.





Best Regards





Farjad Farid



From: pgsql-general-***@postgresql.org
[mailto:pgsql-general-***@postgresql.org] On
Behalf Of Eugenio Trumpy
Sent: 23 September 2014 13:14
To: ***@checknetworks.com;
pgsql-***@postgresql.org
Subject: Re: [GENERAL] csv import error



Hi,

I adjusted the csv, changing the semi-column with
column and inserting the quote for character.
Now it seems to be better but I got another error.
This last is due to the fact that oid column in my
postgresql table is a serial and I did not filled
it in csv because I was thinking that would have
been
filled-in automatically during the data import.

What do you suggest on this regard? How can I
solve?

E.

_____

From: ***@checknetworks.com
To: ***@hotmail.com;
pgsql-***@postgresql.org
Subject: RE: [GENERAL] csv import error
Date: Tue, 23 Sep 2014 12:03:15 +0100



Hi,



Quotation marks should be around both the name of
each and every column and their values.



As the columns names are all lower case. You may
wish to remove all quotation marks which is much
easier.

But character value needs quotation mark.



Also replace the semi-column as column separation
character with commas for sql insert statement.

What has happened here is that the values from CSV
are directly inserted into sql. It needs a little
bit of extra work.

Hope this helps.



Best Regards





Farjad Farid



From: pgsql-general-***@postgresql.org
[mailto:pgsql-general-***@postgresql.org] On
Behalf Of Eugenio Trumpy
Sent: 23 September 2014 11:26
To: pgsql-***@postgresql.org
Subject: [GENERAL] csv import error



Hello,

I'm trying to import data using a csv file,
but I got an error:


ERROR: column
"key;daprof;aprof;tipo;valore;note;oid;unit_mis"
of relation "assorb" does not exist
LINE 1: INSERT INTO
"info_pozzi_hydrocarbon"."assorb"
("key;daprof;a...

^

In statement:

INSERT INTO "info_pozzi_hydrocarbon"."assorb"
("key;daprof;aprof;tipo;valore;note;oid;unit_mis")
VALUES ('1001334;19.1;21;A;6;;;11')


My sql statement for the table that I would like
to populate is:

-- Table: info_pozzi_hydrocarbon.assorb

-- DROP TABLE info_pozzi_hydrocarbon.assorb;

CREATE TABLE info_pozzi_hydrocarbon.assorb
(
key integer,
daprof double precision,
aprof double precision,
tipo character(1),
valore double precision,
note character(254),
oid serial NOT NULL,
unit_mis smallint,
CONSTRAINT assorb_pk PRIMARY KEY (oid),
CONSTRAINT assorb_fk FOREIGN KEY (key)
REFERENCES pozzi (key) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT unit_m_fk FOREIGN KEY (unit_mis)
REFERENCES info_cod.unita (unita) MATCH
SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);


the csv file is like the following (it is only a
part):

key;daprof;aprof;tipo;valore;note;oid;unit_mis
1001334;19.1;21;A;6;;;11
1001334;93.5;94;A;30;;;11
1001334;94;115;A;20;;;11
1001334;154.5;255;A;644;;;11
1001334;273;282;A;4;;;11
1001334;298;309;A;7;;;11
1001334;432;1224;P;1850;;;11
4277001;121;901;A;397;ALLARGAMENTO FORO;;11
4277001;121;901;A;96;PERFORAZIONE;;11


The filed order it is the same and also the
decimal separator is set as dot. I set also the
delimiter for fields.
I don't understand what was the problem.

Can somebody help me?

Eugenio

[Eugenio Trumpy]

Thank you Farjad,

following your suggestion I'm now able to import my csv and the serial
automatically is inserted in the new records.

Bye

E.

From: ***@checknetworks.com
To: ***@hotmail.com; pgsql-***@postgresql.org
Subject: RE: [GENERAL] csv import error
Date: Tue, 23 Sep 2014 13:38:27 +0100

Hi Eugenio, How you got about resolving this depends on your project, the size of data.

For a simple case, which this seems to be. Simply don’t insert the serial column. E.g. remove both name of the column and its corresponding value in the insert statement. Postgresql will insert these automatically. If the size of the data is larger or there are dependencies on the OID value elsewhere in the DB then recreated the target table but without making the column ‘serial’. But actually creating all columns. And inserting all column values. Once the operation is done. Then manually create serial sequencer and attach it to the OID column. The disadvantage of this is in error detection and correction of the process. If there are errors in CSV files ,which often are as sometimes they are edited manually, then it will be more time consuming to correct. Depending on the size of data in CSV file. Hope this helps. Best Regards Farjad Farid From: pgsql-general-***@postgresql.org [mailto:pgsql-general-***@postgresql.org] On Behalf Of Eugenio Trumpy
Sent: 23 September 2014 13:14
To: ***@checknetworks.com; pgsql-***@postgresql.org
Subject: Re: [GENERAL] csv import error Hi,

I adjusted the csv, changing the semi-column with column and inserting the quote for character.
Now it seems to be better but I got another error. This last is due to the fact that oid column in my
postgresql table is a serial and I did not filled it in csv because I was thinking that would have been
filled-in automatically during the data import.

What do you suggest on this regard? How can I solve?

E.From: ***@checknetworks.com
To: ***@hotmail.com; pgsql-***@postgresql.org
Subject: RE: [GENERAL] csv import error
Date: Tue, 23 Sep 2014 12:03:15 +0100 Hi, Quotation marks should be around both the name of each and every column and their values. As the columns names are all lower case. You may wish to remove all quotation marks which is much easier. But character value needs quotation mark. Also replace the semi-column as column separation character with commas for sql insert statement. What has happened here is that the values from CSV are directly inserted into sql. It needs a little bit of extra work. Hope this helps. Best Regards Farjad Farid From: pgsql-general-***@postgresql.org [mailto:pgsql-general-***@postgresql.org] On Behalf Of Eugenio Trumpy
Sent: 23 September 2014 11:26
To: pgsql-***@postgresql.org
Subject: [GENERAL] csv import error Hello,

I'm trying to import data using a csv file,
but I got an error:


ERROR: column "key;daprof;aprof;tipo;valore;note;oid;unit_mis" of relation "assorb" does not existLINE 1: INSERT INTO "info_pozzi_hydrocarbon"."assorb" ("key;daprof;a... ^In statement:

INSERT INTO "info_pozzi_hydrocarbon"."assorb" ("key;daprof;aprof;tipo;valore;note;oid;unit_mis") VALUES ('1001334;19.1;21;A;6;;;11')
My sql statement for the table that I would like to populate is:

-- Table: info_pozzi_hydrocarbon.assorb

-- DROP TABLE info_pozzi_hydrocarbon.assorb;

CREATE TABLE info_pozzi_hydrocarbon.assorb
(
key integer,
daprof double precision,
aprof double precision,
tipo character(1),
valore double precision,
note character(254),
oid serial NOT NULL,
unit_mis smallint,
CONSTRAINT assorb_pk PRIMARY KEY (oid),
CONSTRAINT assorb_fk FOREIGN KEY (key)
REFERENCES pozzi (key) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT unit_m_fk FOREIGN KEY (unit_mis)
REFERENCES info_cod.unita (unita) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);

the csv file is like the following (it is only a part):
key;daprof;aprof;tipo;valore;note;oid;unit_mis1001334;19.1;21;A;6;;;111001334;93.5;94;A;30;;;111001334;94;115;A;20;;;111001334;154.5;255;A;644;;;111001334;273;282;A;4;;;111001334;298;309;A;7;;;111001334;432;1224;P;1850;;;114277001;121;901;A;397;ALLARGAMENTO FORO;;114277001;121;901;A;96;PERFORAZIONE;;11


The filed order it is the same and also the decimal separator is set as dot. I set also the delimiter for fields.
I don't understand what was the problem.

Can somebody help me?

Eugenio