Recently I have finished a customized zip radius search and used the one from Zipcodes to go. Unfortunately they only have csv, xls, and mdb formats for the database. To make use of their data you need to import it into mysql, but first you must create the table which phpmyadmin won't do automagically (please tell me im wrong by the way).
First you must create the table.
I tried to be as lazy as possible and tried to export the mdb to mssql then to mysql but it didn't really work and took waayy too long so i ended up having to write out the create table anyway.
SQL to create the gold table
Note: Remove the ID field until after you import all the data from the csv, otherwise the field lengths wont match!
CREATE TABLE IF NOT EXISTS `zipcodes_full` ( `ID` int(12) unsigned NOT NULL AUTO_INCREMENT, `ZIP_CODE` varchar(255) NOT NULL DEFAULT '' , `CITY_NAME` varchar(255) NOT NULL DEFAULT '' , `STATE_NAME` varchar(255) NOT NULL DEFAULT '' , `STATE_ABBREV` varchar(255) NOT NULL DEFAULT '' , `STATE_FIPS` varchar(255) NOT NULL DEFAULT '' , `COUNTY_FIPS` varchar(255) NOT NULL DEFAULT '' , `COUNTY_NAME` varchar(255) NOT NULL DEFAULT '' , `TOTAL_COUNTY_POPULATION` varchar(255) NOT NULL DEFAULT '' , `TOTAL_COUNTY_HOUSING_UNITS` varchar(255) NOT NULL DEFAULT '' , `LATITUDE` varchar(255) NOT NULL DEFAULT '' , `LONGITUDE` varchar(255) NOT NULL DEFAULT '' , `TIMEZONE` varchar(255) NOT NULL DEFAULT '' , `DAYLIGHT_SAVINGS` varchar(255) NOT NULL DEFAULT '' , `Returns` varchar(255) NOT NULL DEFAULT '' , `$ AVG AGI` varchar(255) NOT NULL DEFAULT '' , `Refunds` varchar(255) NOT NULL DEFAULT '' , `$ AVG Refund` varchar(255) NOT NULL DEFAULT '' , `Single` varchar(255) NOT NULL DEFAULT '' , `Married Joint` varchar(255) NOT NULL DEFAULT '' , `Head of Household` varchar(255) NOT NULL DEFAULT '' , `<30` varchar(255) NOT NULL DEFAULT '' , `30_44` varchar(255) NOT NULL DEFAULT '' , `45_60` varchar(255) NOT NULL DEFAULT '' , `>60` varchar(255) NOT NULL DEFAULT '' , `TV_Designated Market Areas` varchar(255) NOT NULL DEFAULT '' , PRIMARY KEY (`id`) )
Second:
Importing the data is a freaking pain through phpmyadmin. I ended up using the commandline on my local machine and now i am trying to use BigDump php to migrate it to the server.
Use the below script in the mysql commandline to import the csv into the db table. It worked wonderfully 4.6 some seconds. Great Now how do i get it to the server....
Note i dont have mysql mapped in my environment variables in windows so i needed to copy the csv file into the mysql bin directory.
load data local infile '5-DIGIT-ZIP-GOLD.csv' into table zipcodes_full fields terminated by ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'




1 comment:
To show the movement and operation of sophisticated machinery metal aesthetic rolex replica uk full hollow self-jazz movement gives full hollow gentleman temperament and rolex replica sale confident demeanor, elegant ladies wrist and a different kind of charm. Has more than three days power reserve of replica watches sale movement, so the appearance of the classic atmosphere of freedom of spirit automatic mechanical hublot replica uk is more durable and reliable, so watch at idle weekend is still running. Tour in 2017 rolex replica sale also starting a new series of khaki field double calendar, which is mounted on the same series of uk replica watches independent movement, also has a rolex replica uk reserve of more than three days, is a double calendar display the sweep of the watch.
Post a Comment