clear s = seconds() d = new adodatabase() d.connectionString = "DRIVER=Firebird/InterBase(r) driver;UID=SYSDBA;PWD=masterkey;DBNAME=d:\firebird_databases\energydata.fdb;DIALECT=3" d.active = true _app.allowYeildOnMsg = true f = new file() f.open('d:\examples\ado\energydata2.csv') cRead = f.gets(1000) //header n = 1 do while not f.eof() n1 = 1 cStr = "insert into energydata (siteId,eTimestamp,egen,econs,ac_pv_econs," cStr += "pv_eexp,g_eimport) values " do while n1 < 1000 and not f.eof() cRead = f.gets(10000) cStr += "("+parse_cRead(cRead)+")," _app.executeMessages() if n%500 = 0 ?n endif n++ n1++ enddo cStr = substr(cStr,1,len(cStr)-1) //get rid of last comma d.ExecuteSQL(cStr) enddo d.ExecuteSQL('commit') f.close() d.active = false ? n,' Records added ' ?'seconds',(seconds()-s) function parse_cRead(cRead) //Excludes site field from the INSERT. Wraps siteID and eTimestamp in single quotes //Adds NULL in empty fields in the .csv file. local cRet cRet = "'"+substr(cRead,1,at(',',cRead,1)-1)+"','" cTs = substr(cRead,at(',',cRead,2)+1,(at(',',cRead,3)-1)-at(',',cRead,2)) +"'," cRet += convert_ts(cTs) cRet += substr(cRead,at(',',cRead,3)+1) do while ",,"$cRet cRet = stuff(cRet,at(',,',cRet),2,",NULL,") if substr(cRet,len(cRet),1) = ',' cRet += 'NULL' endif enddo return cRet function convert_ts(cTs) //Reformats the timestamp as YYYY-MM-DD HH:MM:SS local cRet,cY,cH,cM,cS if not 'A'$cTs and not 'P'$cTs cRet = cTs else cY = substr(cTs,1,9) cY = stuff(cy,7,0,'-') cY = stuff(cy,5,0,'-') if 'P'$cTs and val(substr(cTs,10,2)) = 12 cH = '00' elseif 'P'$cTs and val(substr(cTs,10,2)) < 12 cH = val(substr(cTs,10,2))+12 else cH = substr(cTs,10,2) endif cM = substr(cTs,13,2) cS = ":00'," cRet = cY+cH+':'+cM+cS endif return cRet