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
' ---------------------------------------------
' 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$," |","</td><td><B>")
        hdr$    = "<TR align=center bgcolor=wheat><TD><B>RecNum<TD><B>";hdr$;"</td></tr>"
        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 "<TABLE border=1 cellsapcing=0>";hdr$                        '| make a table and show header
mem$ = "SELECT rowid,* ";_                                        '| Select page from DB starting at record recNum for lpp records
" FROM csv limit ";recNum;",";lpp
#mem execute(mem$)
WHILE  #mem hasanswer()                                           '| While there is data available for that page then,
    row$ = #mem nextRow$("</TD><TD>")                             '| get it,
    html "<TR><td>"                                               '|   and display
    rowid$ = word$(row$,1,"</TD><TD>")
    button #acd, "", [acd]                                        '|    maintenance button
           #acd setkey(rowid$)                                    '|
    html row$;"</td></TR>"                                        '|    and the row$ data
WEND
html "</table>"
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 style='width:800px; height: 500px; overflow: auto'>"   '| div to make it scrolled
html "<TABLE border=1 cellsapcing=0>";hdr$                        '| make table with header
#mem execute("SELECT rowid,* FROM csv")                           '| select everything from the DB
WHILE  #mem hasanswer()                                           '| While there is data in the db,
    row$   = #mem nextRow$("</TD><TD>")                           '|  get it into row$,
    rowid$ = word$(row$,1,"</TD><TD>")
    html "<TR><td>"
    button #acd, "", [acd]
           #acd setkey(rowid$)
    html row$;"</td></TR>"                                        '|  and display the row$
WEND
html "</table>"                                                   '| done - close the table,
html "</div>"                                                     '|  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$
#mem execute(mem$)
row$     = #mem nextRow$(" |")                                   '|  get it into row$,
i        = 1
html "<table border=1 cellsapcing=0>"
hdr2$    =  word$(hdr1$,i,"|")
WHILE hdr2$ <> ""
    dta$  = word$(row$,i,"|")
    ln    = len(dta$)
    html "<tr><td align=right><B>";trim$(hdr2$);"</td><td><B>"
    val.i$ = "#val";i
    if ln > 40 then                                             '| if too big for text make it a area
        textarea #val.i$,dta$,40,3
       else
        textbox #val.i$,dta$,ln
    end if
    html "</td></tr>"
    i        = i + 1
    hdr2$    =  word$(hdr1$,i,"|")
WEND
html "<tr align=center bgcolor=wheat><td colspan=2 >"           '| Add Change Delete options
button #acd,"Add",[doAcd]
       #acd setkey("a")
button #acd,"Chg",[doAcd]
       #acd setkey("c")
button #acd,"Del",[doAcd]
       #acd setkey("d")
button #acd,"Exit",[doAcd]
       #acd setkey("e")
html "</td></tr></table>"
wait
[doAcd]
if EventKey$ = "e" then goto [exitAcd]
 
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
#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