Improve company productivity with a Business Account.Sign Up

x
?
Solved

I am not sure if query ran correctly: inserting rows from http://www.federalgovernmentzipcodes.us/`

Posted on 2014-02-19
6
Medium Priority
?
808 Views
Last Modified: 2014-02-23
I am not sure if query ran correctly: inserting rows from http://www.federalgovernmentzipcodes.us/`
Was this query successful?

LOAD DATA INFILE 'C:/wamp/www/phpvqp3_scripts/free-zipcode-database.csv'
INTO TABLE zip_codes 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

Open in new window



81832 row(s) affected, 64 warning(s): 1366 Incorrect integer value: 'RecordNumber' for column 'zip_code' at row 1 1366 Incorrect decimal value: 'LocationType' for column 'latitude' at row 1 1366 Incorrect decimal value: 'Lat' for column 'longitude' at row 1 1366 Incorrect integer value: 'Yaxis' for column 'taxreturnsfiled' at row 1 1366 Incorrect integer value: 'Zaxis' for column 'population' at row 1 1366 Incorrect integer value: 'WorldRegion' for column 'wages' at row 1 1262 Row 1 was truncated; it contained more data than there were input columns 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 2 1366 Incorrect integer value: 'NA' for column 'wages' at row 2 1262 Row 2 was truncated; it contained more data than there were input columns 1265 Data truncated for column 'state' at row 3 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 3 1366 Incorrect integer value: 'NA' for column 'wages' at row 3 1262 Row 3 was truncated; it contained more data than there were input columns 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 4 1366 Incorrect integer value: 'NA' for column 'wages' at row 4 1262 Row 4 was truncated; it contained more data than there were input columns 1265 Data truncated for column 'state' at row 5 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 5 1366 Incorrect integer value: 'NA' for column 'wages' at row 5 1262 Row 5 was truncated; it contained more data than there were input columns 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 6 1366 Incorrect integer value: 'NA' for column 'wages' at row 6 1262 Row 6 was truncated; it contained more data than there were input columns 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 7 1366 Incorrect integer value: 'NA' for column 'wages' at row 7 1262 Row 7 was truncated; it contained more data than there were input columns 1265 Data truncated for column 'state' at row 8 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 8 1366 Incorrect integer value: 'NA' for column 'wages' at row 8 1262 Row 8 was truncated; it contained more data than there were input columns 1265 Data truncated for column 'state' at row 9 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 9 1366 Incorrect integer value: 'NA' for column 'wages' at row 9 1262 Row 9 was truncated; it contained more data than there were input columns 1366 Incorrect decimal value: 'PRIMARY' for column 'latitude' at row 10 1366 Incorrect integer value: 'NA' for column 'wages' at row 10 1262 Row 10 was truncated; it contained more data than there were input columns 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 11 1366 Incorrect integer value: 'NA' for column 'wages' at row 11 1262 Row 11 was truncated; it contained more data than there were input columns 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 12 1366 Incorrect integer value: 'NA' for column 'wages' at row 12 1262 Row 12 was truncated; it contained more data than there were input columns 1265 Data truncated for column 'state' at row 13 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 13 1366 Incorrect integer value: 'NA' for column 'wages' at row 13 1262 Row 13 was truncated; it contained more data than there were input columns 1265 Data truncated for column 'state' at row 14 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 14 1366 Incorrect integer value: 'NA' for column 'wages' at row 14 1262 Row 14 was truncated; it contained more data than there were input columns 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 15 1366 Incorrect integer value: 'NA' for column 'wages' at row 15 1262 Row 15 was truncated; it contained more data than there were input columns 1265 Data truncated for column 'state' at row 16 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 16 1366 Incorrect integer value: 'NA' for column 'wages' at row 16 1262 Row 16 was truncated; it contained more data than there were input columns 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 17 1366 Incorrect integer value: 'NA' for column 'wages' at row 17 1262 Row 17 was truncated; it contained more data than there were input columns 1265 Data truncated for column 'state' at row 18 1366 Incorrect decimal value: 'NOT ACCEPTABLE' for column 'latitude' at row 18 Records: 81832  Deleted: 0  Skipped: 0  Warnings: 253488
0
Comment
Question by:rgb192
  • 2
  • 2
  • 2
6 Comments
 
LVL 36

Assisted Solution

by:Loganathan Natarajan
Loganathan Natarajan earned 1000 total points
ID: 39872518
It looks like you're trying to insert irrelevant values to the columns. It clearly says,.  Check the columns and values mapping.
0
 
LVL 36

Assisted Solution

by:Loganathan Natarajan
Loganathan Natarajan earned 1000 total points
ID: 39872530
I think your file may contain empty column values and trying to insert into the integer type column... Thats why it shows error,

You can store NULL value where ever EMPTY column value and insert it.
0
 
LVL 24

Assisted Solution

by:mankowitz
mankowitz earned 1000 total points
ID: 39873720
You can coerce all those "NOT ACCEPTABLE" fields into 0 for numeric fields by loading the file as local. Also, I think you are trying to load the first row, which has table headers. Do this

LOAD DATA LOCAL INFILE 'C:/wamp/www/phpvqp3_scripts/free-zipcode-database.csv'
INTO TABLE zip_codes
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

But, to be honest, I think that what you did worked fine. try

SELECT COUNT(*) FROM zip_codes

You should have 18000 records.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:rgb192
ID: 39878646
SELECT COUNT(*) FROM zip_codes

You should have 18000 records.


Less than half of target number, Is this still correct?

select count(*) from zip_codes

81832
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 1000 total points
ID: 39880847
You have 81,000 records. That's all of them.

Here is a test I just did, only using the primary file.

create table zip_codes (
Zipcode      int primary key,
ZipCodeType      varchar(50),
City varchar(50),
State varchar(10),
LocationType varchar(20),
Lat      decimal(10,2),
`Long` decimal(10,2),
Location varchar(60),
Decommisioned varchar(10),
TaxReturnsFiled      int,
EstimatedPopulation      int,
TotalWages int);

LOAD DATA LOCAL INFILE 'C:/Users/sm/Documents/free-zipcode-database-Primary.csv'
INTO TABLE zip_codes
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

select * from zip_codes;
0
 
LVL 1

Author Closing Comment

by:rgb192
ID: 39880968
most complete answer verifies that I have the records.  thanks.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question