Solved

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

Posted on 2014-02-19
6
720 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…

739 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