Chapter 4: COPY us_counties_2010...

Question

Chapter 4: COPY us_counties_2010...

CactusKurt opened this issue 6 years ago · 22 comments

COPY us_counties_2010
FROM 'C:\YourDirectory\us_counties_2010.csv'
WITH (FORMAT CSV, HEADER);

will give the error
ERROR: invalid byte sequence for encoding "UTF8": 0xf1 0x61 0x20 0x41
CONTEXT: COPY us_counties_2010, line 1804
SQL state: 22021

This is because line 1804 contains a tilde in the NAME column "Dona Ana County"

Answer 1 · 2018-06-23T18:44:36.000Z

Hi, @CactusKurt --

Would you let me know:

Which OS you're using
How you installed PostgreSQL
Whether you ran the query in pgAdmin or from another GUI or the command line.

Thanks!

Answer 2 · 2018-06-23T19:06:03.000Z

Running Windows 10 w/ latest updates
Installed PostgreSQL (64 bit) from the EnterpriseDB (EDB) site (Version 3.0) download
I ran the query in pgAdmin

Answer 3 · 2018-06-24T13:32:37.000Z

Using a fresh download of PostgreSQL 10.4 from EnterpriseDB on Windows 10, I'm not able to replicate this. I'm glad to investigate further, though. If you would please:

Launch pgAdmin and left click on the analysis database you are using for the book exercises (assuming you're following the book and named the DB that way).
Select "Properties"
Click the "Definition" tab
Drop a screen grab here of the contents of that tab.

Thanks.

Answer 4 · 2018-06-24T19:49:28.000Z

Deleting the tilde over the "n" character in "Doña Ana County" solves the problem. Could the problem arise due to the encoding? Here is the screen grab of the "Definition" tab...

Answer 5 · 2018-06-24T19:57:05.000Z

Oh, it's definitely an encoding issue. The only (frustrating) question is why it's working fine over here but causing trouble on your system. Let me investigate further, and thanks for the report.

Your settings in that screengrab match mine, btw.

Answer 6 · 2018-06-24T20:54:28.000Z

@CactusKurt Just to rule things out:

In pgAdmin, would you please run the following commands one at a time and let me know the value they each return:
SHOW client_encoding;
SHOW server_encoding;
Also, did you by any chance open the file us_counties_2010.csv in a text editor or Excel or other application and save it before the import?

Thanks ... I'm interested in getting to the bottom of this one :-)

Answer 7 · 2018-06-24T23:23:34.000Z

@CactusKurt If you'd email me at practicalsqlbook@gmail.com, we can continue diagnosing as I want to ask some questions that are more appropriate in email.

Answer 8 · 2018-06-27T13:51:10.000Z

SHOW client_encoding;
returned UNICODE

SHOW server_encoding:
returned UTF8

When I used a fresh copy of us_counties_2010, I copy/pasted/saved it in a text editor (Notepad) before using COPY. I'm guessing that is causing the problem.

Answer 9 · 2018-06-28T13:00:15.000Z

Hi,

The best way to work with the data files for the book is to either git clone this repository to your local machine, or download the contents of the repo using the download option at the repo main screen.

All the files in this repo are encoded UTF-8. (Confirmed that yesterday).) Opening the .sql files in a text editor to copy/paste the queries into pgAdmin is fine. However, if you open a CSV file in a text editor and re-save it, it's possible you will change the encoding and encounter import issues as the database (using the install in the book) is expecting UTF-8.

Encodings are a tricky business and take some time to wrap your head around. I still have much to learn, myself. My sense is that you've learned some good lessons here about the behavior of Excel and text files that will serve you well going forward.

Thanks for reading the book and please open more issues if you encounter anything unusual. Feel free to email me at practicalsqlbook@gmail.com as well.

Answer 10 · 2018-11-28T12:29:29.000Z

Using PlusTable as my GUI I am receiving the same error.

Error:
ERROR: extra data after last expected column CONTEXT: COPY us_counties_2010, line 2: "Autauga County,AL,050,3,6,01,001,1539582278,25775735,54571,22135,+32.5363818,-086.6444901,54571,5370..."
Other Info:

MacOS Mojave 10.14.1
Installed Postgres via the instructions in the book, specifically the Postgres app

Also, I cloned the repo locally so it shouldn't be an issue with the file. Any thoughts or recommendations? Thanks in advance.

Answer 11 · 2018-11-28T15:48:36.000Z

Hi, @tfaieta --

The error you posted is different than the original one in this issue, which turned out to be a character encoding problem. Usually, the error message you posted occurs when the CSV file has more columns than the table you're importing into.

Can you post your entire COPY statement here?

Answer 12 · 2018-12-12T22:03:56.000Z

@tfaieta Hi, were you able to resolve this? Please let me know. Thanks.

Answer 13 · 2018-12-13T01:53:55.000Z

Yes I did! Sorry about not letting you know, it was a complete mistake on my part.

…

On Wed, Dec 12, 2018 at 5:03 PM Anthony DeBarros ***@***.***> wrote: @tfaieta <https://github.com/tfaieta> Hi, were you able to resolve this? Please let me know. Thanks. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#5 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AOPcW_hWfHwwgzcIHlKFOMXxmK1FfUfJks5u4X1NgaJpZM4U04jw> .

Answer 14 · 2018-12-16T22:48:58.000Z

Great, glad you got that sorted. Going to close this.

Answer 15 · 2020-03-15T21:39:20.000Z

Using PlusTable as my GUI I am receiving the same error.

Error:
ERROR: extra data after last expected column CONTEXT: COPY us_counties_2010, line 2: "Autauga County,AL,050,3,6,01,001,1539582278,25775735,54571,22135,+32.5363818,-086.6444901,54571,5370..."
Other Info:

MacOS Mojave 10.14.1

Installed Postgres via the instructions in the book, specifically the Postgres app

Also, I cloned the repo locally so it shouldn't be an issue with the file. Any thoughts or recommendations? Thanks in advance.

Answer 16 · 2020-03-15T21:40:55.000Z

Hello! I am this issue as well. Any help is greatly appreciated. Thanks
ERROR: extra data after last expected column
CONTEXT: COPY us_counties_2010, line 2: "Autauga County,AL,50,3,6,1,1,1539582278,25775735,54571,22135,32.5363818,-86.6444901,54571,53702,4285..."

Answer 17 · 2020-03-15T22:21:55.000Z

@Lunasanz By any chance, did you open the CSV file with another program, such as Excel?

Answer 18 · 2020-12-23T21:45:05.000Z

yeah I have same problem but this my error msg
ERROR: ERREUR: la relation « us_counties_2010 » n'existe pas
LINE 1: SELECT * FROM us_counties_2010;

Answer 19 · 2020-12-24T14:04:20.000Z

@brahim024 That message indicates you did not create the table using the code in Listing 4-2. See:

https://github.com/anthonydb/practical-sql/blob/master/Chapter_04/Chapter_04.sql#L20

Answer 20 · 2020-12-24T20:24:27.000Z

Thank you @anthonydb I was lost

Answer 21 · 2023-04-10T10:30:04.000Z

Good morning .
Please I just got this
ERROR: invalid input syntax for type bigint: "1.05076E+11"
CONTEXT: COPY us_counties_2010, line 72, column area_land: "1.05076E+11".

Answer 22 · 2023-04-10T13:01:12.000Z

@naboatng Most likely, you opened the CSV file with Excel or another spreadsheet application, which reformatted the numbers to scientific notation. Download a fresh copy of the CSV file, do not open it with a spreadsheet, and try the import again.