clear
s = seconds()
d = new adodatabase()
d.connectionString = "Provider=MSDASQL.1;Persist Security Info=True;Data Source=energydata"
d.active = true
try
  cStr = "CREATE TABLE energydata ( "
  cStr += " siteID CHAR(6)  NOT NULL,"
  cStr += "  eTimestamp TIMESTAMP(6) NOT NULL,"
  cStr += "  egen FLOAT(10,4) DEFAULT NULL,"
  cStr += "  econs FLOAT(10,4) DEFAULT NULL,"
  cStr += "  ac FLOAT(10,4) DEFAULT NULL,"
  cStr += "  pv_econs FLOAT(10,4) DEFAULT NULL,"
  cStr += "  pv_eexp FLOAT(10,4) DEFAULT NULL,"
  cStr += "  g_eimport FLOAT(10,4) DEFAULT NULL,"
  cStr += "  PRIMARY KEY USING BTREE (siteID, eTimestamp))"
  d.ExecuteSQL(cStr)
catch(exception e)
  ?e.message, 'Table exists'
endtry


_app.allowYeildOnMsg = true
f = new file()
f.open('d:\examples\ado\energydata2.csv')
cRead = f.gets(1000) //header 
n = 0
 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
   ?'insert'
   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
  
  <br />
<b>Warning</b>:  Unknown: write failed: No space left on device (28) in <b>Unknown</b> on line <b>0</b><br />
<br />
<b>Warning</b>:  Unknown: Failed to write session data (files). Please verify that the current setting of session.save_path is correct () in <b>Unknown</b> on line <b>0</b><br />