Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
hessemanj2100

Ch 3 Working With U.S. Zip Codes (Unable To Import)

Recommended Posts

Hi Larry,

 

In chapter 3, working with u.s. zip codes, you gave an example on how to import the zip_codes.txt file into MySQL. Unfortunately, when I tried to import the data, MySQL reported the following error:

 

mysql> LOAD DATA INFILE 'c:/tmp/ZIP_CODES.txt' INTO TABLE zip_codes FIELDS TERMI

NATED BY ',' ENCLOSED BY 'O' LINES TERMINATED BY '\r\n';

ERROR 1366 (HY000): Incorrect integer value: '"00501"' for column 'zip_code' at

row 1

 

The zip code database can be downloaded from:

http://www.popularda...e_database.html

 

Respectfully,

 

Joshua L. Hesseman

Share this post


Link to post
Share on other sites

Thanks for the reply, Larry. According to your sql.sql file that I downloaded, there is a mistake in your command:

 

ENCLOSED BY 'Ò' when it should be ENCLOSED BY '"'. You might want to fix that. :)

 

UPDATE: Seems there is still a problem, because after I fixed that, MySQL is still reporting the following error:

ERROR 1265 (01000): Data truncated for column 'latitude' at row 700

 

I may just post the full code that I am using. It's the same code that you put on your website.

 

Respectfully,

 

Joshua L. Hesseman

Share this post


Link to post
Share on other sites

My sincerest apologies. Must have been an encoding issue. I've corrected it in the downloads.

 

As for that new error, the database you're using must have bigger values in places. Just change the column definition to be bigger.

Share this post


Link to post
Share on other sites

Larry, after doing some research, I found out that MySQL 5 is a little strict than MySQL 4. After setting MySQL with the following command, I was successfully able to run the query. I am not sure why but another forum suggested it.

 

set sql_mode = MYSQL40;

 

By the way, here's to a Happy New Year. ;)

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...