-
Notifications
You must be signed in to change notification settings - Fork 0
/
csv-to-sqlite
executable file
·76 lines (61 loc) · 1.54 KB
/
csv-to-sqlite
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
#!/usr/bin/env bash
# usage: ./csv-to-sqlite -d database.db -t table_name -f file.csv
case "$1" in
"in")
touch "$2"/import.db
for item in "$2"/*.csv; do
filename=$(basename -- "$item")
tablename="${filename%.csv}"
$0 -d "$2"/import.db -t "$tablename" -f "$item"
done
exit 0
;;
-h|--help)
echo "Single CSV: ./csv-to-sqlite -d <database.db> -t <table_name> -f <file.csv>"
echo "Bulk CSV: ./csv-to-sqlite in <directory>"
exit 0
;;
esac
# getops for command line arguments
while getopts d:t:f: option; do
case "${option}" in
d) DATABASE=${OPTARG};;
t) TABLE=${OPTARG};;
f) FILE=${OPTARG};;
*) echo "usage: ./csv-to-sqlite -d database.db -t table_name -f file.csv"; exit 1;;
esac
done
# check if database file exists
if [ ! -f "$DATABASE" ]; then
echo "Database file does not exist"
exit 1
fi
# check if csv file exists
if [ ! -f "$FILE" ]; then
echo "CSV file does not exist"
exit 1
fi
# check if table exists
if [ "$(sqlite3 "$DATABASE" ".schema $TABLE")" != "" ]; then
echo "Table already exists"
exit 1
fi
# get headers from csv file
HEADERS=$(head -n 1 "$FILE")
# create table
HEADERS_WITH_COMMA="${HEADERS//,/,}"
CREATE_TABLE="CREATE TABLE $TABLE (${HEADERS_WITH_COMMA})"
sqlite3 "$DATABASE" "$CREATE_TABLE"
# make a tmp csv file without the first line
TMP_FILE=$(mktemp)
tail -n +2 "$FILE" > "$TMP_FILE"
# insert data
sqlite3 "$DATABASE" <<EOF
.mode csv
.import $TMP_FILE $TABLE
EOF
# remove tmp file
rm "$TMP_FILE"
sqlite3 "$DATABASE" "$INSERT_DATA"
echo "Table $TABLE created in $DATABASE with data from $FILE"
exit 0