forked from pavanchhatpar/csv-to-sql-converter
/
convert.sh
135 lines (92 loc) · 3.76 KB
/
convert.sh
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
while getopts f:t:d: flag
do
case "${flag}" in
f) fname=${OPTARG};;
t) intable=${OPTARG};;
d) indatabase=${OPTARG};;
esac
done
#echo "Table: $intable";
#echo "Database: $indatabase";
# https://stackoverflow.com/questions/10154633/load-csv-data-into-mysql-in-python/10154650#10154650
# https://stackoverflow.com/questions/31235642/query-csv-file-in-python-and-create-new-table
#https://pythonhosted.org/querycsv/
#http://code.activestate.com/recipes/498130-create-sql-tables-from-csv-files/
if [ -z "${fname+set}" ]; then
echo "error file not found"
echo "use -f /path/to/the/file.csv"
exit
fi
#https://www.unix.com/unix-for-dummies-questions-and-answers/9883-removing-commas-text-file.html
#cat $fname|tr -d '"' > $fname
#sed -e 's/"//g' $fname > TMP_00
#cp TMP_00 $fname
#rm TMP_00
cat $fname|tr -d '"' > TMP_00
cp TMP_00 $fname
rm TMP_00
# https://www.baeldung.com/linux/use-command-line-arguments-in-bash-script
# https://github.com/pavanchhatpar/csv-to-sql-converter
sed 's/\s*,*\s*$//g' "$fname" > tmp.csv
op=$(echo "$fname" | cut -d"." -f 1)
#echo ${foo##*/}
TABLE=${op##*/}"xtable"
DATABASE=${op##*/}
#TABLE=${op##*\/}"xtable"
#DATABASE=${op##*\/}
opfile="$op.sql"
op="\`$op\`"
columns=$(head --lines=1 tmp.csv | sed 's/,/`,`/g' | tr -d "\r\n")
columns="\`$columns\`"
tail --lines=+2 tmp.csv | while read l ; do
values=$(echo $l | sed 's/,/\",\"/g' | tr -d "\r\n")
values="\"$values\""
echo "INSERT INTO $TABLE($columns) VALUES ($values);"
done > "$opfile"
#CREATE TABLE Persons(PersonID int,LastName varchar(255));
#$tablecolumnstypes="\`$tablecolumnstypes\`"
#echo $tablecolumnstypes
#https://stackoverflow.com/questions/9998596/create-mysql-table-directly-from-csv-file-using-the-csv-storage-engine
#http://positon.org/import-csv-file-to-mysql
DELIM=","
#CSV="$1"
CSV="tmp.csv"
FIELDS=$(head -1 "$CSV" | sed -e 's/'$DELIM'/` varchar(255),`/g' -e 's/\r//g')
#echo $FIELDS
FIELDS='`'"$FIELDS"'` varchar(255)'
#echo $FIELDS
#sed "1iCREATE DATABASE '$DATABASE';\nUSE '$DATABASE';" $opfile
# from https://github.com/lukaneco/minecraft-server
#file2=~/mineserver/spigot.jar
# https://www.baeldung.com/linux/use-command-line-arguments-in-bash-script
# https://stackoverflow.com/questions/3601515/how-to-check-if-a-variable-is-set-in-bash
#if [ -z "$intable" ]; then echo "var is blank"; else echo "Table: $intable"; fi
#if [ -z "$indatabase" ]; then echo "var is blank"; else echo "Database: $indatabase"; fi
#if [ -z ${intable+x} ]; then $gg=1; else echo "var is set to '$intable'"; fi
if [ -n "${indatabase+set}" ]; then
echo '$indatabase was set'
myDatabase="USE $indatabase;\n"
else
echo '$indatabase isnt set'
myDatabase="DROP DATABASE IF EXISTS $DATABASE;\nCREATE DATABASE $DATABASE;\nUSE $DATABASE;\n"
fi
if [ -n "${intable+set}" ]; then
echo '$intable was set'
else
echo '$intable isnt set'
fi
sed -i "1i$myDatabase DROP TABLE IF EXISTS $TABLE;\nCREATE TABLE $TABLE ($FIELDS);" "$opfile"
#Work
#sed -i "1iDROP DATABASE IF EXISTS $DATABASE;\nCREATE DATABASE $DATABASE;\nUSE $DATABASE;\nDROP TABLE IF EXISTS $TABLE;\nCREATE TABLE $TABLE ($FIELDS);" "$opfile"
# Work
#sed -i "1iDROP DATABASE IF EXISTS $DATABASE;\nCREATE DATABASE $DATABASE;\nUSE $DATABASE;\nDROP TABLE IF EXISTS $TABLE;\nCREATE TABLE $TABLE ($FIELDS);" "$opfile"
#sed -i '1i\CREATE DATABASE '$DATABASE';\nUSE '$DATABASE';\nDROP TABLE IF EXISTS '$TABLE';\nCREATE TABLE '$TABLE' ('$FIELDS');' $opfile
#sed "1iCREATE DATABASE '$DATABASE';\nUSE '$DATABASE';" $opfile
#sed "3iDROP TABLE IF EXISTS '$TABLE';\nCREATE TABLE '$TABLE' ($FIELDS)" $opfile
#\nCREATE TABLE '$TABLE' ('$FIELDS');
#WORK
#echo "DROP TABLE IF EXISTS '$TABLE';\nCREATE TABLE '$TABLE' ($FIELDS);"
#echo "DROP TABLE IF EXISTS '$TABLE';"
#echo $FIELDS
#echo "CREATE TABLE '$TABLE' ($FIELDS);"
rm tmp.csv