Load+CSV+file+to DB

Load CSV file to Database and Maintain it. At end of load, print the Data from SQLite You can print the Data Scrolled or Paged You can Add Change or Delete records code format="lb" ' - ' Load CSV file to SQLite database ' CSV file has fields surrounded with quotes(") ' and seperated with comma(.) '  The first line has the field names ' You can Add Change or Delete records ' -

' - ' Create DB using first line as field name ' then load it with CSV data ' -- sqliteconnect #mem, ":memory:"                                   '| create in-memory SQLite DB open DefaultDir$ + "\public\YourCsvFile.csv" for input as #f      '| open csv file i   = 0                                                          '| while not(eof(#f))                                               '| line input #f, a$                                            '| i = i + 1                                                    '| if i = 1 then                                                '| first line has field names hdr1$  = strRep$(a$,""","""," |") hdr1$  = strip$(hdr1$) hdr$   = strRep$(hdr1$," |","  ") hdr$   = "RecNum";hdr$;"  " a$     = strRep$(a$,""","""," text,") a$     = "CREATE TABLE csv(";strip$(a$);")" else a$     = strRep$(a$,"'","''")                            '| eclipse single quote to quote+quote a$     = strRep$(a$,"""","'")                            '| replace dbl quote (") with single quote (')        a$      = "INSERT INTO csv VALUES(";a$;")"                '| SQL command to insert data into DB    end if                                                        '|    #mem execute(a$)                                              '| execute the SQL command wend                                                              '| close #f                                                          '| load complete - close CSV file records = i - 1                                                   '| print "Records loaded:";records                                   '| Show how many records loaded button #ps,"Paged",    [goPage]                                   '| button if user wants to Print the Data a page at a time button #ps,"Scrolled", [goScroll]                                 '| button if user wants to Print all Data in a scroll area wait ' - ' display database paged ' - [goPage]                                                         '| ps$   = "p"                                                      '| Set page or scroll request to [p]aged [doPage]                                                         '| lpp   = 20                                                       '| Lines per page - change to your preference recNum = 0                                                       '| set begin record number to zero [nxtPage] cls html " " button #page,"Prev Page", [page]                                 '| button for previous page #page setkey(recNum - lpp)                                '|  let button know what record button #page,"Next Page", [page]                                 '| button for next page #page setkey(recNum + lpp)                                '|  let button know what record button #p,"Scrolled", [goScroll]                                 '| button if user wants to show data scrolled wait [page] recNum = val(EventKey$)                                          '| get record passed by button recNum = max(0,recNum)                                           '| make sure it is not less than zero recNum = min(records,recNum)                                     '| make sure it does not exceed the number of records recNum = int(recNum / lpp) * lpp                                 '| keep it on the suggested lines per page group goto [nxtPage]                                                   '| now go back and list that page wait ' - ' display database scrolled ' - [goScroll] ps$   = "s"                                                      '| Set page or scroll request to [s]croll [doScroll] cls html " "  '| div to make it scrolled html " "                                                  '| done - close the table, html " "                                                    '|  and close the div button #p,"Paged", [goPage]                                      '| button if user wants to see data paged wait

' ' Add Change Delete ' [acd] cls rowid$  = EventKey$ mem$    = "SELECT * FROM csv WHERE rowid = ";rowid$ row$    = #mem nextRow$(" |")                                   '|  get it into row$, i       = 1 html " " wait [doAcd] if EventKey$ = "e" then goto [exitAcd]
 * 1) mem execute(mem$)

if EventKey$ = "d" then                                       '| Delete mem$ = "DELETE FROM csv WHERE rowid = ";rowid$ records = records - 1 #mem execute(mem$) goto [exitAcd] end if i          = 1 x$         = "" q$         = "" cma$       = "" set$       = "" hdr2$      = word$(hdr1$,i,"|") while hdr2$ <> ""                                             '| Make maintenance form val.i$ = "#val";i a$     = strRep$(trim$(#val.i$    contents$),"'","''") x$     = x$;q$;a$ set$   = set$ ; cma$;hdr2$;" = '";a$;"'" q$     = "','" cma$   = ", " i      = i + 1 hdr2$  = word$(hdr1$,i,"|") wend

if EventKey$ = "a" then                                      '| Add a record mem$   = "INSERT into csv VALUES ('";x$;"')" records = records + 1 else mem$ = "UPDATE csv SET ";set$;" WHERE rowid = ";rowid$   '| Change existing record end if
 * 1) mem execute(mem$)

[exitAcd]                                                    '| Go back to Scroll or Page if ps$ = "s" then goto [doScroll] else goto [doPage] end if

wait

' ' string replace ' FUNCTION strRep$(str$,rep$,with$) ln = len(rep$) ln1 = ln - 1 i = 1 while i <= len(str$) if mid$(str$,i,ln) = rep$ then strRep$ = strRep$ + with$ i = i + ln1 else strRep$ = strRep$ + mid$(str$,i,1) end if   i = i + 1 WEND END FUNCTION

' - ' strip junk ' - FUNCTION strip$(str$) strip$ = "" for i = 1 to len(str$) a$ = MID$(str$,i,1) a = ASC(a$) if a > 31 then if a < 127 then if a$ <> "'" then if a$ <> """" then strip$ = strip$ + a$               end if            end if        end if    end if next i END FUNCTION code