Обсуждение: Error: extra data after last expected column

On Wed, May 18, 2016 at 5:41 PM, Mitra . <mitraahi@gmail.com> wrote:

Hi,

I want to import excel file to postgresql 9.5 but when run the query , got error :extra data after last expected column

Mac OS

1) downloaded excel file , open with LibreOffice, saved as CSV, UTF-8 (attachment). 

2) renamed and moved the file under  /Users/<mylogin>/Library/Application Support/Postgres/var-9.5

​I'm somewhat surprised the server was able to resolve "test.csv" to above path...​

3) created an empty table called mytable

​Pertinent also to your subsequent question - I'm not sure where you expect to place import the data to if the table you've created has no columns...​

 

4) ran the following command: 

query ran under PSequel:

​Its nice to use actual names, and not phonetic spellings, when writing.

i.e., psql​

COPY mytable

FROM  'test.csv'

Do I have to create columns inside mytable ? I also tried "Freeze header and footer" before converting to CSV , same error. 

What am I missing here? 

​Try:

COPY mytable FROm 'test.csv' WITH (FORMAT csv);

​Then read the following while paying special attention to what the defaults are:

http://www.postgresql.org/docs/9.5/interactive/sql-copy.html

PostgreSQL doesn't try to figure things out on its own - you have to tell it what format your data is in.

David J.

On Wed, May 18, 2016 at 5:41 PM, Mitra . <mitraahi@gmail.com> wrote:

Hi,

I want to import excel file to postgresql 9.5 but when run the query , got error :extra data after last expected column

Mac OS

1) downloaded excel file , open with LibreOffice, saved as CSV, UTF-8 (attachment). 

2) renamed and moved the file under  /Users/<mylogin>/Library/Application Support/Postgres/var-9.5

​I'm somewhat surprised the server was able to resolve "test.csv" to above path...​

3) created an empty table called mytable

​Pertinent also to your subsequent question - I'm not sure where you expect to place import the data to if the table you've created has no columns...​

 

4) ran the following command: 

query ran under PSequel:

​Its nice to use actual names, and not phonetic spellings, when writing.

i.e., psql​

COPY mytable

FROM  'test.csv'

Do I have to create columns inside mytable ? I also tried "Freeze header and footer" before converting to CSV , same error. 

What am I missing here? 

​Try:

COPY mytable FROm 'test.csv' WITH (FORMAT csv);

​Then read the following while paying special attention to what the defaults are:

http://www.postgresql.org/docs/9.5/interactive/sql-copy.html

PostgreSQL doesn't try to figure things out on its own - you have to tell it what format your data is in.

David J.