Chapter 4: Performing the census import with COPY.

[rollieM]

Having trouble with data, code, or exercises in Practical SQL? I'm glad to help. Please answer these questions, and I'll typically reply within one to two business days.

Please include the chapter number in your issue title. For example: "Chapter 13: Regular Expression Question"

General

Please answer all of these as they're essential for troubleshooting.

What's your operating system (e.g. Windows 10, macOS Catalina, Ubuntu, etc.)?

Did you install PostgreSQL and pgAdmin according to the steps on page xxviii of the book's Introduction? If not, please describe your installation.

Which versions of PostgreSQL and pgAdmin 4 are you using? If you aren't sure, for PostgreSQL run the SQL command SELECT version(); and for pgAdmin 4, navigate to "About pgAdmin 4" under the "Help" menu.

Did you download the book's code examples and data from GitHub using the directions on page xxvii of the Introduction? If not, please describe how you loaded the material on your computer.

Issue or Question

Chapter, page and code listing number:

Describe the issue or your question:

Please paste the code and error message here. It's OK to alter identifying info such as a folder name

[rollieM]

Hello, I'm Having an issue importing the census data in chapter 4. I got this error message. Any help would be truly appreciated. (Installed PostgreSQL as instructed by the book), (version 8.1.0), (yes, I download the books code examples and data from GitHub, as instructed by the book)

COPY us_counties_2010 FROM '/Users/rolliemartinez/Desktop/us_counties_2010.csv' WITH (FORMAT CSV, HEADER);

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..." SQL state: 22P04

[anthonydb]

Most of the time, people run into a problem at that point if they have not downloaded the code and data files per the instructions on page xxvii of the Introduction. On that page, I wrote:

"Each chapter includes code examples, and most use data sets I’ve already compiled. All the code and sample data in the book is available to download at https://www.nostarch.com/practicalSQL/. Click the Download the Code from GitHub link to go to the GitHub repository that holds this material.

At GitHub, you should see a “Clone or Download” button that gives you the option to download a ZIP file with all the materials. Save the file to your computer in a location where you can easily find it, such as your desktop. Inside the ZIP file is a folder for each chapter."

Finally, if you're on Windows, also follow the instructions in the Note at the bottom of that same page in the book.

---

Make sure you're downloading your code and data according to those instructions. Copying and pasting from GitHub won't work.

Also very important: Do not open the CSV files with Excel or another program before you import them.

Please do a fresh download per those instructions and see if that works. If not, I'm glad to troubleshoot further. Please let me know how it works out!

[rollieM]

Anthonydb, I got it figured out! Thank you again

[anthonydb]

Great! Any other questions don't hesitate to ask.

[seamusmc123]

Windows Installed as per steps on page xxvii. PostgreSQL 12 & pgAdmin 4.23 From Github, yes.

Running listing 4-3 for chapter 4, us_counties_2010.csv, keeps returning error:

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

ERROR: could not open file "C:\Users\postgres\Documents\us_counties_2010.csv" for reading: No such file or directory HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy. SQL state: 58P01

What am I missing here?

Thanks, Seamus

[anthonydb]

@seamusmc123

You'll need to follow the instructions in the Note at the bottom of page xxvii in the Introduction to grant permission to the folder containing data.

[wszczygielski]

Hello! I also having an issue with coping us_counties_2010.csv here's an 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..." SQL state: 22P04

I've followed @anthonydb instructions from github and instructions from introduction in book (changed permission for the database to read files). Unfortunately error has'nt changed...

[anthonydb]

Hi, @wszczygielski,

This usually happens when a person copies code from the PDF of the book instead of from the GitHub files, or if they opened the CSV file with Excel and saved it. I suggest you delete the table (use DROP TABLE us_counties_2010;) and recreate it using the code here in GitHub. Then download a fresh copy of the CSV.

[wszczygielski]

Great advice! It's working :) Thank You!