BUG #15206: Can not import CSV into PostgreSQL

Hi

2018-05-19 11:09 GMT+02:00 PG Bug reporting form <noreply@postgresql.org>:

The following bug has been logged on the website:

Bug reference: 15206
Logged by: Milorad Krstevski
Email address: miloradkrstevski@gmail.com
PostgreSQL version: 10.4
Operating system: Ubuntu 18.04 LTS x64
Description:

Respected,

No matter what I try, I always get the error: ERROR: extra data after last
expected column, and can not import CSV into table in PostgreSQL.

I am using pgAdmin 4.30, Python 3.6.5, Flask 0.12.2.

Can you please point out solution to my problem please?

It is not PostgreSQL bug, so pg_general mailing list will be better.

PostgreSQL is very strict about CSV format - it looks so some line of CSV
doc has more columns than target table.

Regards

Pavel

Thank you.

On Sat, May 19, 2018 at 4:19 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

2018-05-19 11:09 GMT+02:00 PG Bug reporting form <noreply@postgresql.org>:

The following bug has been logged on the website:

Bug reference: 15206
Logged by: Milorad Krstevski
Email address: miloradkrstevski@gmail.com
PostgreSQL version: 10.4
Operating system: Ubuntu 18.04 LTS x64
Description:

Respected,

No matter what I try, I always get the error: ERROR: extra data after last
expected column, and can not import CSV into table in PostgreSQL.

I am using pgAdmin 4.30, Python 3.6.5, Flask 0.12.2.

Can you please point out solution to my problem please?

It is not PostgreSQL bug, so pg_general mailing list will be better.

PostgreSQL is very strict about CSV format - it looks so some line of CSV
doc has more columns than target table.

​IOW, we cannot help unless you provide both the CSV file and the table
definition.

David J.
​

Respected,

Thank you very much for the prompt reply.

I am trying to import the following files in new created empty table in
postgre db and schema (both are neptun, and the table is act):
1. s1.csv - is created manually by me (encoding UTF8 and delimiter ;) -
attachment 1
2. act2017short2.csv - is taken from system (I just put 10 lines here) -
attachment 2

The error that is shown in the import is below. I go import as CSV -
encoding UTF8 with header and delimiter ; - nothing else in the import
wizard is changed.
1. Attachment 3: Error 1
2. Attachment 4: Error 2

Can you please point me out where is my mistake? Thank you.

Respectfully,
Milorad K.

On Sat, May 19, 2018 at 2:55 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sat, May 19, 2018 at 4:19 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

2018-05-19 11:09 GMT+02:00 PG Bug reporting form <noreply@postgresql.org>
:

The following bug has been logged on the website:

Bug reference: 15206
Logged by: Milorad Krstevski
Email address: miloradkrstevski@gmail.com
PostgreSQL version: 10.4
Operating system: Ubuntu 18.04 LTS x64
Description:

Respected,

No matter what I try, I always get the error: ERROR: extra data after
last
expected column, and can not import CSV into table in PostgreSQL.

I am using pgAdmin 4.30, Python 3.6.5, Flask 0.12.2.

Can you please point out solution to my problem please?

It is not PostgreSQL bug, so pg_general mailing list will be better.

PostgreSQL is very strict about CSV format - it looks so some line of CSV
doc has more columns than target table.

​IOW, we cannot help unless you provide both the CSV file and the table
definition.

David J.
​

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

On Sat, May 19, 2018 at 6:57 AM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Can you please point me out where is my mistake? Thank you

​Given you still haven't provided the definition of ​"neptun.act" that
would be a no.

David J.

Can you please help me how to do that?

The table is empty new, nothing pre-set?

On Sat, May 19, 2018 at 4:03 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sat, May 19, 2018 at 6:57 AM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Can you please point me out where is my mistake? Thank you

​Given you still haven't provided the definition of ​"neptun.act" that
would be a no.

David J.

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

Here is the backup of the whole db.

It is empty. The pass is: miki (in case you need it).

On Sat, May 19, 2018 at 3:57 PM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Respected,

Thank you very much for the prompt reply.

I am trying to import the following files in new created empty table in
postgre db and schema (both are neptun, and the table is act):
1. s1.csv - is created manually by me (encoding UTF8 and delimiter ;) -
attachment 1
2. act2017short2.csv - is taken from system (I just put 10 lines here) -
attachment 2

The error that is shown in the import is below. I go import as CSV -
encoding UTF8 with header and delimiter ; - nothing else in the import
wizard is changed.
1. Attachment 3: Error 1
2. Attachment 4: Error 2

Can you please point me out where is my mistake? Thank you.

Respectfully,
Milorad K.

On Sat, May 19, 2018 at 2:55 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sat, May 19, 2018 at 4:19 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

2018-05-19 11:09 GMT+02:00 PG Bug reporting form <noreply@postgresql.org

:

The following bug has been logged on the website:

Bug reference: 15206
Logged by: Milorad Krstevski
Email address: miloradkrstevski@gmail.com
PostgreSQL version: 10.4
Operating system: Ubuntu 18.04 LTS x64
Description:

Respected,

No matter what I try, I always get the error: ERROR: extra data after
last
expected column, and can not import CSV into table in PostgreSQL.

I am using pgAdmin 4.30, Python 3.6.5, Flask 0.12.2.

Can you please point out solution to my problem please?

It is not PostgreSQL bug, so pg_general mailing list will be better.

PostgreSQL is very strict about CSV format - it looks so some line of
CSV doc has more columns than target table.

​IOW, we cannot help unless you provide both the CSV file and the table
definition.

David J.
​

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

Is this OK?

Please let me know. Thank you.

On Sat, May 19, 2018 at 4:14 PM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Here is the backup of the whole db.

It is empty. The pass is: miki (in case you need it).

On Sat, May 19, 2018 at 3:57 PM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Respected,

Thank you very much for the prompt reply.

I am trying to import the following files in new created empty table in
postgre db and schema (both are neptun, and the table is act):
1. s1.csv - is created manually by me (encoding UTF8 and delimiter ;) -
attachment 1
2. act2017short2.csv - is taken from system (I just put 10 lines here) -
attachment 2

The error that is shown in the import is below. I go import as CSV -
encoding UTF8 with header and delimiter ; - nothing else in the import
wizard is changed.
1. Attachment 3: Error 1
2. Attachment 4: Error 2

Can you please point me out where is my mistake? Thank you.

Respectfully,
Milorad K.

On Sat, May 19, 2018 at 2:55 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sat, May 19, 2018 at 4:19 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

2018-05-19 11:09 GMT+02:00 PG Bug reporting form <
noreply@postgresql.org>:

The following bug has been logged on the website:

Bug reference: 15206
Logged by: Milorad Krstevski
Email address: miloradkrstevski@gmail.com
PostgreSQL version: 10.4
Operating system: Ubuntu 18.04 LTS x64
Description:

Respected,

No matter what I try, I always get the error: ERROR: extra data after
last
expected column, and can not import CSV into table in PostgreSQL.

I am using pgAdmin 4.30, Python 3.6.5, Flask 0.12.2.

Can you please point out solution to my problem please?

It is not PostgreSQL bug, so pg_general mailing list will be better.

PostgreSQL is very strict about CSV format - it looks so some line of
CSV doc has more columns than target table.

​IOW, we cannot help unless you provide both the CSV file and the table
definition.

David J.
​

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

A screenshot would be better for me, dealing with a dump file is too
high-maintenance for this kind of question.

On Sat, May 19, 2018 at 7:18 AM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Screenshot of what?

I will take now but please point me out.

On Sat, May 19, 2018 at 4:24 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

A screenshot would be better for me, dealing with a dump file is too
high-maintenance for this kind of question.

On Sat, May 19, 2018 at 7:18 AM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Is this OK?

Please let me know. Thank you.

On Sat, May 19, 2018 at 4:14 PM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Here is the backup of the whole db.

It is empty. The pass is: miki (in case you need it).

On Sat, May 19, 2018 at 3:57 PM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Respected,

Thank you very much for the prompt reply.

I am trying to import the following files in new created empty table in
postgre db and schema (both are neptun, and the table is act):
1. s1.csv - is created manually by me (encoding UTF8 and delimiter ;) -
attachment 1
2. act2017short2.csv - is taken from system (I just put 10 lines here)
- attachment 2

The error that is shown in the import is below. I go import as CSV -
encoding UTF8 with header and delimiter ; - nothing else in the import
wizard is changed.
1. Attachment 3: Error 1
2. Attachment 4: Error 2

Can you please point me out where is my mistake? Thank you.

Respectfully,
Milorad K.

On Sat, May 19, 2018 at 2:55 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sat, May 19, 2018 at 4:19 AM, Pavel Stehule <
pavel.stehule@gmail.com> wrote:

Hi

2018-05-19 11:09 GMT+02:00 PG Bug reporting form <
noreply@postgresql.org>:

The following bug has been logged on the website:

Bug reference: 15206
Logged by: Milorad Krstevski
Email address: miloradkrstevski@gmail.com
PostgreSQL version: 10.4
Operating system: Ubuntu 18.04 LTS x64
Description:

Respected,

No matter what I try, I always get the error: ERROR: extra data
after last
expected column, and can not import CSV into table in PostgreSQL.

I am using pgAdmin 4.30, Python 3.6.5, Flask 0.12.2.

Can you please point out solution to my problem please?

It is not PostgreSQL bug, so pg_general mailing list will be better.

PostgreSQL is very strict about CSV format - it looks so some line of
CSV doc has more columns than target table.

​IOW, we cannot help unless you provide both the CSV file and the
table definition.

David J.
​

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

The definition of the neptune.act table. The pgAdmin docs can walk you
through how to go about finding that if you cannot intuit how to navigate
the object browser it provides.

You need to count how many columns are on that table and compare them to
how many are in the csv file. You should find there are fewer in the table
which means you need to fix the table. If you find the counts to be the
same then it might be something different.

Dave

On Sat, May 19, 2018 at 7:25 AM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Please find in attach the backup schema.

I really do not know how to export the settings (or what is needed to you).
If you could please send me screen of what is needed?

I created all db, schema and table empty with default settings. Nothing is
changed or limited. I put postgre user with all privileges everywhere.
That's it. Nothing else.

On Sat, May 19, 2018 at 4:31 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

The definition of the neptune.act table. The pgAdmin docs can walk you
through how to go about finding that if you cannot intuit how to navigate
the object browser it provides.

You need to count how many columns are on that table and compare them to
how many are in the csv file. You should find there are fewer in the table
which means you need to fix the table. If you find the counts to be the
same then it might be something different.

Dave

On Sat, May 19, 2018 at 7:25 AM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Screenshot of what?

I will take now but please point me out.

On Sat, May 19, 2018 at 4:24 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

A screenshot would be better for me, dealing with a dump file is too
high-maintenance for this kind of question.

On Sat, May 19, 2018 at 7:18 AM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Is this OK?

Please let me know. Thank you.

On Sat, May 19, 2018 at 4:14 PM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Here is the backup of the whole db.

It is empty. The pass is: miki (in case you need it).

On Sat, May 19, 2018 at 3:57 PM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

Respected,

Thank you very much for the prompt reply.

I am trying to import the following files in new created empty table
in postgre db and schema (both are neptun, and the table is act):
1. s1.csv - is created manually by me (encoding UTF8 and delimiter ;)
- attachment 1
2. act2017short2.csv - is taken from system (I just put 10 lines
here) - attachment 2

The error that is shown in the import is below. I go import as CSV -
encoding UTF8 with header and delimiter ; - nothing else in the import
wizard is changed.
1. Attachment 3: Error 1
2. Attachment 4: Error 2

Can you please point me out where is my mistake? Thank you.

Respectfully,
Milorad K.

On Sat, May 19, 2018 at 2:55 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sat, May 19, 2018 at 4:19 AM, Pavel Stehule <
pavel.stehule@gmail.com> wrote:

Hi

2018-05-19 11:09 GMT+02:00 PG Bug reporting form <
noreply@postgresql.org>:

The following bug has been logged on the website:

Bug reference: 15206
Logged by: Milorad Krstevski
Email address: miloradkrstevski@gmail.com
PostgreSQL version: 10.4
Operating system: Ubuntu 18.04 LTS x64
Description:

Respected,

No matter what I try, I always get the error: ERROR: extra data
after last
expected column, and can not import CSV into table in PostgreSQL.

I am using pgAdmin 4.30, Python 3.6.5, Flask 0.12.2.

Can you please point out solution to my problem please?

It is not PostgreSQL bug, so pg_general mailing list will be better.

PostgreSQL is very strict about CSV format - it looks so some line
of CSV doc has more columns than target table.

​IOW, we cannot help unless you provide both the CSV file and the
table definition.

David J.
​

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

"Milorad" == Milorad Krstevski <miloradkrstevski@gmail.com> writes:

Milorad> I created all db, schema and table empty with default
Milorad> settings.

The problem is that your table is "empty" in the sense of having NO
COLUMNS AT ALL. So obviously you can't copy data into it.

(Tables with no columns are not useful, and should never be created
intentionally.)

This isn't a bug, and you should never have posted it to the bug
reporting form in the first place. There are places to ask for help,
such as the pgsql-general or pgsql-novice mailing lists or the IRC
channel (#postgresql on freenode.net).

--
Andrew (irc:RhodiumToad)

So first I have to create all (for example 50 columns and define them in
data type) and then import? That is not the case with any other db.

I tested as you said, first to create 3x columns, import and it went well
(att. example 1). Please see the screenshot.

BTW, how can I bulk import many columns? Is it possible via SQL query or
another way is better?

Sorry for the wrong mailing list and the "bug classification" but I am new
in PostgreSQL and want it to connect to an app.

On Sat, May 19, 2018 at 4:53 PM, Andrew Gierth <andrew@tao11.riddles.org.uk>
wrote:

"Milorad" == Milorad Krstevski <miloradkrstevski@gmail.com> writes:

Milorad> I created all db, schema and table empty with default
Milorad> settings.

The problem is that your table is "empty" in the sense of having NO
COLUMNS AT ALL. So obviously you can't copy data into it.

(Tables with no columns are not useful, and should never be created
intentionally.)

This isn't a bug, and you should never have posted it to the bug
reporting form in the first place. There are places to ask for help,
such as the pgsql-general or pgsql-novice mailing lists or the IRC
channel (#postgresql on freenode.net).

--
Andrew (irc:RhodiumToad)

--
Milorad Krstevski

Mobile: +389 71 381 168
E-mail: miloradkrstevski@gmail.com

On Sat, May 19, 2018 at 8:05 AM, Milorad Krstevski <
miloradkrstevski@gmail.com> wrote:

So first I have to create all (for example 50 columns and define them in
data type) and then import? That is not the case with any other db.

​Cannot speak about others but stock PostgreSQL, yes.

Haven't used it but the following seems relevant if you are looking for a
tool to make life easier:

http://csvkit.readthedocs.io/en/1.0.3/tutorial/3_power_tools.html#csvsql-and-sql2csv-ultimate-power

David J.
​

On 2018-05-19 17:05:42 +0200, Milorad Krstevski wrote:

So first I have to create all (for example 50 columns and define them in data
type) and then import? That is not the case with any other db.

I am quite certain that it is also the case for Oracle and MySQL.

I suspect that it is true for all SQL databases, since SQL distinguishes
between data definition (DDL) and data manipulation (DML) statements.
Loading data into a table is data manipulation, and you don't want that
to change your data definition as a side effect.

Which database can automatically create columns while loading a CSV file
into a table?

(It is of course possible to write a program which guesses the types of
the columns from the contents of the CSV file and then issues an
appropriate CREATE TABLE or ALTER TABLE statement. I wrote one 20 years
ago. But that's an application program, not part of the database.)

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;

Thank you for all the prompt replies and explanations about databases. I am
a little new in this, so maybe I made some conclusions not quite right.

I was hoping that you can help me with issues like importing sql dumps from
other sources (mssql and mysql), because I wanna migrate into postgre, and
also how to import DDL from other tables into postgre and afterwards import
the CSV files. Having them created manually is quite long.

---
Milorad Krstevski
M: +389 71 381 168
E: miloradkrstevski@gmail.com

On Sun, May 20, 2018, 10:15 Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

2018-05-20 11:36 GMT+02:00 Milorad Krstevski <miloradkrstevski@gmail.com>:

Thank you for all the prompt replies and explanations about databases. I
am a little new in this, so maybe I made some conclusions not quite right.

I was hoping that you can help me with issues like importing sql dumps
from other sources (mssql and mysql), because I wanna migrate into postgre,
and also how to import DDL from other tables into postgre and afterwards
import the CSV files. Having them created manually is quite long.

Ora2pg supports migration from mysql too now.

https://github.com/darold/ora2pg

Regards

Pavel Stehule

Thanks, I'll check it out.

---
Milorad Krstevski
M: +389 71 381 168
E: miloradkrstevski@gmail.com

On Sun, May 20, 2018, 12:21 Pavel Stehule <pavel.stehule@gmail.com> wrote: