QzrFDrxE

1#!/bin/bash
2
3guessColType() {
4	guesses=()
5	colnum=$1
6	sample=$(sed -n '2,50p' < $2)
7	lines=`echo -e "$sample" | csvtool col $colnum -`
8	for var in $lines;
9	do
10		if [[ $var =~ ^\-\+[0-9]{1,9}$ ]]; then
11			guesses+=('INT')
12			#echo "INT --> "$var
13		elif [[ $var =~ ^[\-\+0-9\.]{1,}$ ]]; then
14			guesses+=('DOUBLE')
15			#echo "DOUBLE --> "$var
16		elif [[ $var =~ ^.{1,200}$ ]]; then
17			guesses+=('VARCHAR(200)')
18			#echo "VARCHAR(200) --> "$var
19		else
20			guesses+=('TEXT')
21			#echo "TEXT --> "$var
22		fi
23	done
24
25	IFS=$'\n' sorted=($(sort <<<"${guesses[*]}"))
26	unset IFS
27	echo $sorted
28}
29
30csv2mysql() {
31	filetitle=$(basename $1 | sed -r 's/\..*//')
32	sqltablecols=$(head -1 $1 | sed -e 's/\"/`/g' | sed -e 's/,/ text NOT NULL,\n\t/g')
33
34	columns=$(head -1 $1 | sed -e 's/\"//g')
35	IFS=', ' read -r -a filecols <<< $columns
36	cols=""
37	intcols=(pop rent elevation medIncome medIncomestate medHousevalue medHousevalueState workersLocalNum popPerSqMi popMale popFemale raceHispanic raceWhite raceBlack raceTwo raceIndian)
38	filecolcount=${#filecols[@]}
39	i=0
40
41	for col in "${filecols[@]}"
42	do
43		((i++))
44		if [[ $i -lt $filecolcount ]]; then
45			maybecomma=","
46		else
47			maybecomma=""
48		fi
49
50		coltype=$(guessColType $i $1)
51		cols+="$col $coltype$maybecomma "
52
53		#if [[ " ${intcols[@]} " =~ " ${col} " ]]; then
54	   # 	cols+="$col INT$maybecomma "
55		#else
56	    #	cols+="$col TEXT$maybecomma "
57		#fi
58	done
59
60sql=" CREATE TABLE IF NOT EXISTS $filetitle ( id bigint(20) NOT NULL, $cols ) ENGINE=InnoDB DEFAULT CHARSET=latin1;"
61sql+=" ALTER TABLE $filetitle ADD PRIMARY KEY (id);"
62sql+=" ALTER TABLE $filetitle MODIFY id int(11) NOT NULL AUTO_INCREMENT;"
63
64#echo -e $sql; exit
65
66mysql -u root -pmyseecret citydata << EOF
67$sql
68EOF
69
70	mysqlimport \
71	    --user=root \
72	    --password=myseecret \
73	    --columns=$columns \
74	    --compress \
75		--ignore-lines=1 \
76	    --fields-optionally-enclosed-by='"' \
77	    --fields-terminated-by=',' \
78	    --fields-escaped-by='\\' \
79	    --lines-terminated-by='\n' \
80	    --local \
81	    --lock-tables \
82	    --verbose \
83	    citydata $1	2>&1 | grep -v "Warning: Using a password"
84}
85
86if [ "$1" ]; then
87	csv2mysql $1
88else
89	for file in csvout/*
90	do
91		csv2mysql $file
92	done
93	#csv2mysql $1
94fi