-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathdata_processing.txt
64 lines (50 loc) · 2.29 KB
/
data_processing.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
Importing data into MYSQL
Step 1 - Create Table
delimiter $$
CREATE TABLE `tbl_maine_domestic_db` (
`dms_orig` varchar(45) DEFAULT NULL,
`dms_orig_fips` varchar(45) DEFAULT NULL,
`dms_dest` varchar(45) DEFAULT NULL,
`dms_dest_fips` varchar(45) DEFAULT NULL,
`dms_mode` varchar(45) DEFAULT NULL,
`sctg2` varchar(45) DEFAULT NULL,
`value_2010` float DEFAULT NULL,
`curval_2010` float DEFAULT NULL,
`tons_2010` float DEFAULT NULL,
KEY `orig` (`dms_orig_fips`),
KEY `dest` (`dms_dest_fips`),
KEY `orig-dest` (`dms_dest_fips`,`dms_orig_fips`),
KEY `mode` (`dms_mode`),
KEY `scgt` (`sctg2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
mysqlimport --ignore-lines=1 --fields-terminated-by=, --fields-enclosed-by=\" --columns='dms_orig,dms_orig_fips,dms_dest,dms_dest_fips,dms_mode,sctg2,value_2010,curval_2010,tons_2010' --local -u root -p tredis tbl_maine_domestic_db.csv
CREATE TABLE `tbl_maine_domestic_db` (
`dms_orig_fips` varchar(45) DEFAULT NULL,
`dms_dest_fips` varchar(45) DEFAULT NULL,
`dms_mode` varchar(45) DEFAULT NULL,
`sctg2` varchar(45) DEFAULT NULL,
`curval_2010` float DEFAULT NULL,
`tons_2010` float DEFAULT NULL,
KEY `orig` (`dms_orig_fips`),
KEY `dest` (`dms_dest_fips`),
KEY `orig-dest` (`dms_dest_fips`,`dms_orig_fips`),
KEY `mode` (`dms_mode`),
KEY `scgt` (`sctg2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
mysqlimport --ignore-lines=1 --fields-terminated-by=, --fields-enclosed-by=\" --columns='dms_orig_fips,dms_dest_fips,dms_mode,sctg2,curval_2010,tons_2010' --local -u root -p tredis tbl_corrected_maine_domestic.csv
CREATE TABLE `tbl_maine_project_exports` (
`dms_orig_fips` varchar(45) DEFAULT NULL,
`dms_mode` varchar(45) DEFAULT NULL,
`dms_dest_fips` varchar(45) DEFAULT NULL,
`fr_inmode` varchar(45) DEFAULT NULL,
`fr_orig` varchar(45) DEFAULT NULL,
`sctg2` varchar(45) DEFAULT NULL,
`curval_2010` float DEFAULT NULL,
`tons_2010` float DEFAULT NULL,
KEY `orig` (`dms_orig_fips`),
KEY `dest` (`dms_dest_fips`),
KEY `orig-dest` (`dms_dest_fips`,`dms_orig_fips`),
KEY `mode` (`dms_mode`),
KEY `scgt` (`sctg2`)
)
mysqlimport --ignore-lines=1 --fields-terminated-by=, --fields-enclosed-by=\" --columns='dms_orig_fips,dms_mode,dms_dest_fips,fr_inmode,fr_orig,sctg2,curval_2010,tons_2010' --local -u root -p tredis tbl_maine_project_exports.csv