Solved

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

Posted on 2014-02-19
6
644 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 250 total points
Comment Utility
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 250 total points
Comment Utility
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 250 total points
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:rgb192
Comment Utility
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 250 total points
Comment Utility
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
 

Author Closing Comment

by:rgb192
Comment Utility
most complete answer verifies that I have the records.  thanks.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now