encoding system utf-8
set datafile _data__202010261208.csv
#"moment","sensorId","value"
#2020-10-15 00:00:00,1,0.741044
set sensorfile sensor_202010261227.csv
#"sensorId","sensor","description","type","technology","mnemo","status","sql_state",
#"data_type","measure","last_value","last_value_moment",
#... "OPC_import","min_valid","max_valid","MS_status","location"
#1,ABS_DENS,Абсолютная плотность,Плотность,Основная технология,КУУГ,В работе,"1",
#Первичный,кг/м3,0.74625,2020-10-26 13:27:00,1,0.0,3.0,1,""

console show
update
####
package require sqlite3

sqlite3 db ./sensors.sqlite

db eval {BEGIN TRANSACTION}

### sensor table
set cols "id int, sensor text, description text,type text,technology text,mnemo text,status text,sql_state int,data_type \
text,measure text,last_value real,last_value_moment int,OPC_import int,min_valid \
 real,max_valid real,MS_status int,location text"
db eval "CREATE TABLE sensors($cols)"

set infile [open $sensorfile]

gets $infile line ;# Skip the first line - we know the meaning of the columns


while { [gets $infile line] >= 0 } {
		if {[regexp {\,$} $line]} {append line \"\"}
		set matches [regexp -inline -all {([^\,]*|"[^"]*")(\,|$)} $line]
  	set list1 {}
    set date [lindex $matches [expr 37]]
    if {[catch {set seconds [clock scan $date]} ret]!=0} {
    	error $line
    }
    set matches [lreplace $matches 37 37 $seconds]
    foreach {a b c} $matches  {
    	if {[regexp {\"} $b]} {
    	lappend list1 $b
    	} else {
    		lappend list1 \"$b\"
    	}
    	
    }
    set str [join $list1 ,]
    
    if {[catch {db eval "INSERT INTO sensors VALUES($str)"} ret]!=0} {
    	error $line
    }
}
close $infile

### data table
db eval {CREATE TABLE data( sensor int, date int, value real )}

set infile [open $datafile]

gets $infile line ;# Skip the first line - we know what the columns mean


set c 0
while { [gets $infile line] >= 0 } {
	incr c
	if {$c%1000==0} {
		puts stdout $c
		update
	}
    lassign [split $line ,] date sens value
    set seconds [clock scan $date]
    db eval {INSERT INTO data VALUES($sens,$seconds,$value)}
}
close $infile


db eval {COMMIT TRANSACTION}
db eval {CREATE INDEX idx_sensor ON data (sensor)}
db eval {CREATE INDEX idx_date ON data (date)}
db close

exit