Iowa Liquor Sales Dataset to MySQL

iowa_mysql

## Download
https://mydata.iowa.gov/Sales-Distribution/Iowa-Liquor-Sales-by-Year-and-County/ahiv-u4uz

## mysql
CREATE TABLE `iowaalcohol` (
  `DATE` date DEFAULT NULL,
  `CONVENIENCE STORE` varchar(255) DEFAULT NULL,
  `STORE` varchar(12) DEFAULT NULL,
  `NAME` varchar(255) DEFAULT NULL,
  `ADDRESS` varchar(255) DEFAULT NULL,
  `CITY` varchar(255) DEFAULT NULL,
  `ZIPCODE` varchar(20) DEFAULT NULL,
  `STORE LOCATION` varchar(255) DEFAULT NULL,
  `COUNTY NUMBER` varchar(4) DEFAULT NULL,
  `COUNTY` varchar(255) DEFAULT NULL,
  `CATEGORY` varchar(20) DEFAULT NULL,
  `CATEGORY NAME` varchar(100) DEFAULT NULL,
  `VENDOR NO` varchar(20) DEFAULT NULL,
  `VENDOR` varchar(255) DEFAULT NULL,
  `ITEM` varchar(20) DEFAULT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  `PACK` int(11) DEFAULT NULL,
  `LITER SIZE` int(11) DEFAULT NULL,
  `STATE BTL COST` float(7,2) DEFAULT NULL,
  `BTL PRICE` float(7,2) DEFAULT NULL,
  `BOTTLE QTY` int(11) DEFAULT NULL,
  `TOTAL` float(12,2) DEFAULT NULL,
  KEY `STORE` (`STORE`),
  KEY `DATE` (`DATE`),
  KEY `CATEGORY NAME` (`CATEGORY NAME`),
  KEY `CATEGORY` (`CATEGORY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

## Translate the dates, clean up numbers, pre-import via bash

sed -E "s#([0-9]{2})/([0-9]{2})/([0-9]{4})#\3-\1-\2#" < iowa-liquor.csv | 
  tr -d '$' > iowa-liquor-datefixed.csv


## Load to MySQL

LOAD DATA LOCAL INFILE '/home/users/iowa.csv' INTO TABLE sales FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 ROWS;