Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 789
  • Last Modified:

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

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
rgb192
Asked:
rgb192
  • 2
  • 2
  • 2
4 Solutions
 
Loganathan NatarajanLAMP DeveloperCommented:
It looks like you're trying to insert irrelevant values to the columns. It clearly says,.  Check the columns and values mapping.
0
 
Loganathan NatarajanLAMP DeveloperCommented:
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
 
mankowitzCommented:
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rgb192Author Commented:
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
 
mankowitzCommented:
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
 
rgb192Author Commented:
most complete answer verifies that I have the records.  thanks.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now