Jump to content
Larry Ullman's Book Forums

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

Link to comment
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

Link to comment
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.

Link to comment
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. ;)

Link to comment
Share on other sites

 Share

×
×
  • Create New...