No title

Started by Prakash Ramakrishnanalmost 7 years ago6 messagesgeneral

prakash.ramakrishnan.ap@nielsen.com

almost 7 years ago

Hi Team,

We are getting below error while during import the csv file please do the
needful.

CREATE TABLE ujshjk ( pod_id bigint NOT NULL, src_id smallint, eff_strt_dt
date,eff_end_dt date,ins_dt timestamp without time zone,updt_dt timestamp
without time zone,nat_key_1 character(50),nat_key_2 character(50),nat_key_3
character(50),nat_key_4 character(50),nat_key_5 character(50));

-bash-4.2$ more ckr_sto.csv
4937880 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |
| | 5000128790679 | | | |
4939355 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |
| | 5000128639345 | | | |
4939744 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |
| | 5000128684510 | | | |
4939750 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |
| | 5000128683100 | | | |
4936360 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |
| | 5000128567527 | | | |
4940308 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |
| | 5000128781329 | | | |
4938006 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |
| | 4000128912554 | | | |
4937457 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |
| | 5000128426574 | | | |

error
----------
postgres=# \copy ckr_sto from /data/ckr_sto.csv CSV HEADER;
ERROR: invalid input syntax for integer: " 4939355 | 12 | 2015-01-05
| | 2015-01-05 05:51:47 | | | 5000128639345 |
| | | "
CONTEXT: COPY ckr_sto, line 2, column pod_id: " 4939355 | 12 |
2015-01-05 | | 2015-01-05 05:51:47 | | |
500012863934..."
postgres=#
postgres=#

--
Thanks,
Prakash.R

Laurenz Albe

laurenz.albe@cybertec.at

almost 7 years ago

In reply to: Prakash Ramakrishnan (#1)

Re:

On Tue, 2019-07-02 at 13:09 +0530, Prakash Ramakrishnan wrote:

We are getting below error while during import the csv file please do the needful.

I'd say the needful thing here is for you to read the documentation...

-bash-4.2$ more ckr_sto.csv
4937880 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128790679 | | | |
4939355 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128639345 | | | |
4939744 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128684510 | | | |
4939750 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128683100 | | | |
4936360 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128567527 | | | |
4940308 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128781329 | | | |
4938006 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 4000128912554 | | | |
4937457 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128426574 | | | |

error
----------
postgres=# \copy ckr_sto from /data/ckr_sto.csv CSV HEADER;
ERROR: invalid input syntax for integer: " 4939355 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128639345 | | | | "
CONTEXT: COPY ckr_sto, line 2, column pod_id: " 4939355 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 500012863934..."

From the documentation of COPY:

DELIMITER

Specifies the character that separates columns within each row (line) of the file.
The default is a tab character in text format, a comma in CSV format.
This must be a single one-byte character. This option is not allowed when using binary format.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Prakash Ramakrishnan

prakash.ramakrishnan.ap@nielsen.com

almost 7 years ago

In reply to: Laurenz Albe (#2)

Re:

Thanks a lot. I will check and let you know.

Regards,
Prakash.R

On Tue, Jul 2, 2019 at 1:15 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Tue, 2019-07-02 at 13:09 +0530, Prakash Ramakrishnan wrote:

We are getting below error while during import the csv file please do

the needful.

I'd say the needful thing here is for you to read the documentation...

-bash-4.2$ more ckr_sto.csv
4937880 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128790679 | | | |

4939355 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128639345 | | | |

4939744 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128684510 | | | |

4939750 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128683100 | | | |

4936360 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128567527 | | | |

4940308 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128781329 | | | |

4938006 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 4000128912554 | | | |

4937457 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128426574 | | | |

error
----------
postgres=# \copy ckr_sto from /data/ckr_sto.csv CSV HEADER;
ERROR: invalid input syntax for integer: " 4939355 | 12 |

2015-01-05 | | 2015-01-05 05:51:47 | | |
5000128639345 | | | | "

CONTEXT: COPY ckr_sto, line 2, column pod_id: " 4939355 | 12 |

2015-01-05 | | 2015-01-05 05:51:47 | | |
500012863934..."

From the documentation of COPY:

DELIMITER

Specifies the character that separates columns within each row (line)
of the file.
The default is a tab character in text format, a comma in CSV format.
This must be a single one-byte character. This option is not allowed
when using binary format.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

--
Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On call
: +91-8939599426

raf

raf@raf.org

almost 7 years ago

In reply to: Laurenz Albe (#2)

Re:

Laurenz Albe wrote:

On Tue, 2019-07-02 at 13:09 +0530, Prakash Ramakrishnan wrote:

We are getting below error while during import the csv file please do the needful.

I'd say the needful thing here is for you to read the documentation...

-bash-4.2$ more ckr_sto.csv
4937880 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128790679 | | | |
4939355 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128639345 | | | |
4939744 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128684510 | | | |
4939750 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128683100 | | | |
4936360 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128567527 | | | |
4940308 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128781329 | | | |
4938006 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 4000128912554 | | | |
4937457 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128426574 | | | |

error
----------
postgres=# \copy ckr_sto from /data/ckr_sto.csv CSV HEADER;
ERROR: invalid input syntax for integer: " 4939355 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 5000128639345 | | | | "
CONTEXT: COPY ckr_sto, line 2, column pod_id: " 4939355 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 | | | 500012863934..."

From the documentation of COPY:

DELIMITER

Specifies the character that separates columns within each row (line) of the file.
The default is a tab character in text format, a comma in CSV format.
This must be a single one-byte character. This option is not allowed when using binary format.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

in other words, ckr_sto.csv is not a csv file.
it just has .csv at the end of its name.
that's why psql tried to interpret the entire
line as the first column: there were no commas.

its contents should look something like:

4937880,12,2015-01-05,,2015-01-05 05:51:47,,,5000128790679,,,,
4939355,12,2015-01-05,,2015-01-05 05:51:47,,,5000128639345,,,,
4939744,12,2015-01-05,,2015-01-05 05:51:47,,,5000128684510,,,,
4939750,12,2015-01-05,,2015-01-05 05:51:47,,,5000128683100,,,,
4936360,12,2015-01-05,,2015-01-05 05:51:47,,,5000128567527,,,,
4940308,12,2015-01-05,,2015-01-05 05:51:47,,,5000128781329,,,,
4938006,12,2015-01-05,,2015-01-05 05:51:47,,,4000128912554,,,,
4937457,12,2015-01-05,,2015-01-05 05:51:47,,,5000128426574,,,,

Andrew Kerber

andrew.kerber@gmail.com

almost 7 years ago

In reply to: raf (#4)

Re:

Yes, CSV stands for comma separated variable length. This means that the
fields in each row should be separated by commas, with a carriage return at
the end of each record. You have a file using | separators, which mean it
is not csv.

On Tue, Jul 2, 2019 at 6:04 PM <raf@raf.org> wrote:

Laurenz Albe wrote:

On Tue, 2019-07-02 at 13:09 +0530, Prakash Ramakrishnan wrote:

We are getting below error while during import the csv file please do

the needful.

I'd say the needful thing here is for you to read the documentation...

-bash-4.2$ more ckr_sto.csv
4937880 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128790679 | | | |

4939355 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128639345 | | | |

4939744 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128684510 | | | |

4939750 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128683100 | | | |

4936360 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128567527 | | | |

4940308 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128781329 | | | |

4938006 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 4000128912554 | | | |

4937457 | 12 | 2015-01-05 | | 2015-01-05 05:51:47 |

| | 5000128426574 | | | |

error
----------
postgres=# \copy ckr_sto from /data/ckr_sto.csv CSV HEADER;
ERROR: invalid input syntax for integer: " 4939355 | 12 |

2015-01-05 | | 2015-01-05 05:51:47 | | |
5000128639345 | | | | "

CONTEXT: COPY ckr_sto, line 2, column pod_id: " 4939355 | 12 |

2015-01-05 | | 2015-01-05 05:51:47 | | |
500012863934..."

From the documentation of COPY:

DELIMITER

Specifies the character that separates columns within each row

(line) of the file.

The default is a tab character in text format, a comma in CSV format.
This must be a single one-byte character. This option is not allowed

when using binary format.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

in other words, ckr_sto.csv is not a csv file.
it just has .csv at the end of its name.
that's why psql tried to interpret the entire
line as the first column: there were no commas.

its contents should look something like:

4937880,12,2015-01-05,,2015-01-05 05:51:47,,,5000128790679,,,,
4939355,12,2015-01-05,,2015-01-05 05:51:47,,,5000128639345,,,,
4939744,12,2015-01-05,,2015-01-05 05:51:47,,,5000128684510,,,,
4939750,12,2015-01-05,,2015-01-05 05:51:47,,,5000128683100,,,,
4936360,12,2015-01-05,,2015-01-05 05:51:47,,,5000128567527,,,,
4940308,12,2015-01-05,,2015-01-05 05:51:47,,,5000128781329,,,,
4938006,12,2015-01-05,,2015-01-05 05:51:47,,,4000128912554,,,,
4937457,12,2015-01-05,,2015-01-05 05:51:47,,,5000128426574,,,,

--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Adrian Klaver

adrian.klaver@aklaver.com

almost 7 years ago

In reply to: Andrew Kerber (#5)

Re:

On 7/2/19 4:08 PM, Andrew Kerber wrote:

Yes, CSV stands for comma separated variable length. This means that

CSV = Comma Separated Values

the fields in each row should be separated by commas, with a carriage
return at the end of each record. You have a file using | separators,
which mean it is not csv.

That is not strictly true:

https://en.wikipedia.org/wiki/Comma-separated_values

And is definitely not true for this context, using Postgres COPY:

https://www.postgresql.org/docs/9.6/sql-copy.html

"CSV Format

This format option is used for importing and exporting the Comma
Separated Value (CSV) file format used by many other programs, such as
spreadsheets. Instead of the escaping rules used by PostgreSQL's
standard text format, it produces and recognizes the common CSV escaping
mechanism.

The values in each record are separated by the DELIMITER character. If
the value contains the delimiter character, the QUOTE character, the
NULL string, a carriage return, or line feed character, then the whole
value is prefixed and suffixed by the QUOTE character, and any
occurrence within the value of a QUOTE character or the ESCAPE character
is preceded by the escape character. You can also use FORCE_QUOTE to
force quotes when outputting non-NULL values in specific columns.

...
"

You do have to specify the delimiter if it is not the default comma. In
the OP's case the delimiter would need to be set to '|'.

On Tue, Jul 2, 2019 at 6:04 PM <raf@raf.org <mailto:raf@raf.org>> wrote:

Laurenz Albe wrote:

On Tue, 2019-07-02 at 13:09 +0530, Prakash Ramakrishnan wrote:

We are getting below error while during import the csv file

please do the needful.

I'd say the needful thing here is for you to read the

documentation...

-bash-4.2$ more ckr_sto.csv
4937880 | 12 | 2015-01-05 | | 2015-01-05

05:51:47 | | | 5000128790679 | |
| |

4939355 | 12 | 2015-01-05 | | 2015-01-05

05:51:47 | | | 5000128639345 | |
| |

4939744 | 12 | 2015-01-05 | | 2015-01-05

05:51:47 | | | 5000128684510 | |
| |

4939750 | 12 | 2015-01-05 | | 2015-01-05

05:51:47 | | | 5000128683100 | |
| |

4936360 | 12 | 2015-01-05 | | 2015-01-05

05:51:47 | | | 5000128567527 | |
| |

4940308 | 12 | 2015-01-05 | | 2015-01-05

05:51:47 | | | 5000128781329 | |
| |

4938006 | 12 | 2015-01-05 | | 2015-01-05

05:51:47 | | | 4000128912554 | |
| |

4937457 | 12 | 2015-01-05 | | 2015-01-05

05:51:47 | | | 5000128426574 | |
| |

error
----------
postgres=# \copy ckr_sto from /data/ckr_sto.csv CSV HEADER;
ERROR: invalid input syntax for integer: " 4939355 | 12 |

2015-01-05 | | 2015-01-05 05:51:47 | | |
5000128639345 | | | | "

CONTEXT: COPY ckr_sto, line 2, column pod_id: " 4939355 |

12 | 2015-01-05 | | 2015-01-05 05:51:47 | |
| 500012863934..."

From the documentation of COPY:

DELIMITER

Specifies the character that separates columns within each

row (line) of the file.

The default is a tab character in text format, a comma in CSV

format.

This must be a single one-byte character. This option is not

allowed when using binary format.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

in other words, ckr_sto.csv is not a csv file.
it just has .csv at the end of its name.
that's why psql tried to interpret the entire
line as the first column: there were no commas.

its contents should look something like:

4937880,12,2015-01-05,,2015-01-05 05:51:47,,,5000128790679,,,,
4939355,12,2015-01-05,,2015-01-05 05:51:47,,,5000128639345,,,,
4939744,12,2015-01-05,,2015-01-05 05:51:47,,,5000128684510,,,,
4939750,12,2015-01-05,,2015-01-05 05:51:47,,,5000128683100,,,,
4936360,12,2015-01-05,,2015-01-05 05:51:47,,,5000128567527,,,,
4940308,12,2015-01-05,,2015-01-05 05:51:47,,,5000128781329,,,,
4938006,12,2015-01-05,,2015-01-05 05:51:47,,,4000128912554,,,,
4937457,12,2015-01-05,,2015-01-05 05:51:47,,,5000128426574,,,,

--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
Adrian Klaver
adrian.klaver@aklaver.com