Re: - Mailing list pgsql-general

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