Solved

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

Posted on 2014-02-19
6
712 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
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 250 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 250 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 250 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
 

Author Closing Comment

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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

756 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