· 8 years ago · Nov 23, 2017, 10:26 AM
1sed -i "s%^0%0/%g" grhost
2cat grhost | cut -c1-3 > grhost2
3sed -i "s%.{4}%/%g" grhost
4pr -mts, grhost2 grhost > grhostfinal
5sed -i "s/,//g" grhostfinal
6cat grhostfinal | cut -c1-4 > grhostfinal1
7cat grhostfinal | cut -c5 > grhostfinal2
8awk -Wposix '{printf("%dn","0x" $1)}' grhostfinal2 > grhostfinal3
9pr -mts, grhostfinal1 grhostfinal3 > grhostfinal4
10sed -i "s/,//g" grhostfinal4
11
12#! /bin/bash
13
14database='test.db'
15schema='esquema'
16XML='Telegramas.xml'
17tempschema='tempschema'
18trap 'rm -f definitivo' EXIT
19
20function charge_files
21{
22 if [[ -f $XML ]]
23 then
24 echo '============================='
25 echo '| LOADED XML |'
26 echo '============================='
27 else
28 echo '============================='
29 echo '| XML NOT LOADED |'
30 echo '============================='
31 exit 1
32 fi
33}
34
35function extract
36{
37 grep -e 'RecordStart' -e 'Telegram' "$XML" |
38 awk -F '"' '{ if( NF > 10 ) { conname = $8; contype = $12 } else
39 printf("%s,%s,%s,%s,%s,%d,%dn",
40 conname, contype, $2, $4, $6, substr($8, 36, 4), substr($8, 50, 4)
41 ); }' >> definitivo
42
43}
44
45function create_schema
46{
47 if [[ -f $schema ]]
48 then
49 echo '============================='
50 echo '| LOADED SCHEMA |'
51 echo '============================='
52 else
53 cat >> "$schema" <<'EOF'
54CREATE TABLE test (
55KKID INTEGER PRIMARY KEY,
56conection VARCHAR(20) NOT NULL,
57ip VARCHAR(20) NOT NULL,
58time DATETIME NOT NULL,
59service VARCHAR(20) NOT NULL,
60frameformat VARCHAR(20) NOT NULL,
61id_dispositivo VARCHAR(20) NOT NULL,
62id_valor VARCHAR(20) NOT NULL
63);
64EOF
65 fi
66 if [[ -f $tempschema ]]
67 then
68 echo '============================='
69 echo '| LOADED TEMPSCHEMA |'
70 echo '============================='
71 else
72 cat >> "$tempschema" <<'EOF'
73CREATE TABLE temp (
74conection VARCHAR(20) NOT NULL,
75ip VARCHAR(20) NOT NULL,
76time DATETIME NOT NULL,
77service VARCHAR(20) NOT NULL,
78frameformat VARCHAR(20) NOT NULL,
79id_dispositivo VARCHAR(20) NOT NULL,
80id_valor VARCHAR(20) NOT NULL
81);
82.separator ","
83.import ./definitivo temp
84.exit
85EOF
86 fi
87}
88
89function upload
90{
91 # Upload the schema to sqlite3 database
92 sqlite3 "$database" < "$schema"
93
94 # Create a temp table with the script
95 sqlite3 "$database" < "$tempschema"
96
97 # Upload the csv to a temp table
98 sqlite3 "$database" < <(printf '.separator ","n.import definitivo tempn')
99
100 # Make an insert from the temp to the database
101 # to get the attribute autoincrement
102 sqlite3 "$database" "INSERT INTO test (conection, ip, time, service,
103 frameformat, id_dispositivo, id_valor) SELECT * FROM temp;"
104
105 # Delete the table temp
106 sqlite3 "$database" 'DROP TABLE IF EXISTS temp;'
107
108 # Remove duplicate fields
109 sqlite3 "$database" "DELETE FROM test WHERE oid NOT IN (
110 SELECT MIN(oid) FROM test GROUP BY
111 conection, ip, time, service,
112 frameformat, id_dispositivo, id_valor);"
113
114}
115
116charge_files
117extract
118create_schema
119upload
120
1210105 will be 0=0/ 1=1 0=/ 5=5 result=0/1/5
122 0100 will be 0=0/ 1=1 0=/ 0=0 result=0/1/0
123 1A1A 1=1 A=10 1=1 A=10 result=110110
124
125<CommunicationLog xmlns="http:telegrams">
126<RecordStart Timestamp="" Mode="" Host="" ConnectionName="" ConnectionOptions="" ConnectorType="" MediumType="" />
127<Telegram Timestamp="" Service="" FrameFormat="" RawData="" />
128<Telegram Timestamp="" Service="" FrameFormat="" RawData="" />
129
130<RecordStop Timestamp="" />
131<RecordStart Timestamp="" Mode="" Host="" ConnectionName="" ConnectionOptions="" ConnectorType="" MediumType="" />
132<Telegram Timestamp="" Service="" FrameFormat="" RawData="" />
133<Telegram Timestamp="" Service="" FrameFormat="" RawData="" />
134<RecordStop Timestamp="" />
135</CommunicationLog>