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

[hessemanj2100]

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

[Larry]

Well, your ENCLOSED BY 'O' seems to be wrong, unless values really are enclosed by O and not "

[hessemanj2100]

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

[Larry]

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.

[hessemanj2100]

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.

[Larry]

Great. Kudos for figuring that out and thanks for sharing. Happy New Year to you as well!