Обсуждение:

Поиск

Список

Период

Сортировка

От

Prakash Ramakrishnan

Дата:

02 июля 2019 г., 10:39:19

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

Re:

От

Laurenz Albe

Дата:

02 июля 2019 г., 10:45:03

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

Re:

От

Prakash Ramakrishnan

Дата:

02 июля 2019 г., 11:02:27

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

Re:

От

raf@raf.org

Дата:

03 июля 2019 г., 02:04:27

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,,,,

Re:

От

Andrew Kerber

Дата:

03 июля 2019 г., 02:08:04

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.'

Re:

От

Adrian Klaver

Дата:

03 июля 2019 г., 02:14:28

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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение:

Re:

Re:

Re:

Re:

Re: