[Gambas-user] Help me ....... with Gambas2 and SQlite3

[charlesg]

"Muñoz Palominos, César Augusto" wrote:
> 
> 	How I can insert a CSV (comma-separated values) file into Sqlite3
> database in Gambas2 program?
> 

Here is an example. It won't be directly applicable to you but will give
pointers. In constructing the CSV, I used "^" as a separator. If you inherit
a CSV file with a more traditional "," separator, then you must be mindful
that text entries enclosed within parentheses in your CSV may contain odd
characters even commas. If you don't get rid of these with a replace
statement than it will play havoc with your sqlite database.

Also sqlite does not have a defined data format. I choose to use yyyy-mm-dd
so most CSV dates need converting.


PUBLIC SUB btnGrnLine_Click()

  DIM spl AS String[]
  DIM j AS Integer
  DIM sql AS String
  DIM dte AS String
  DIM grnno AS Integer
  DIM rslt AS String
  DIM ext AS Float
  
  sql = "delete from grnLine"
  conn.Exec(sql)
      
  hFileIn = OPEN "/home/charles/Dropbox/sqlite3/grnLine.csv"
  WHILE NOT Eof(hFileIn)
    LINE INPUT #hFileIn, sLine
    sLine = Replace(sLine, "'", " ")
    sline = Replace(sLine, ",", " ")
    sline = Replace(sLine, "|", " ")
    
    spl = Split(sline, "^")
    sql = "insert into grnLine values ('"
    j = 0
    FOR EACH spl
      SELECT CASE j
        CASE 0 'capture grn no
          grnno = Val(spl[j])
        CASE 1 'add grn no to line no
          rslt = Str(grnno * 1000 + spl[j])
        CASE 4
          rslt = Format(spl[j], "###0.00")
        CASE 16
          dte = spl[j]
          IF dte <> "" THEN 
            dte = "20" & Mid(dte, 7, 2) & "-" & Mid(dte, 4, 2) & "-" &
Left(dte, 2)
          ELSE 
            dte = ""
          ENDIF 
          rslt = dte
        CASE ELSE 
          rslt = spl[j]
      END SELECT 
      IF j > 0 THEN 
        sql = sql & rslt & "','"
      ENDIF 
      j = j + 1
    NEXT 
    sql = Left(sql, Len(sql) - 2) & ")"
    conn.Exec(sql)
  WEND
END


-- 
View this message in context: http://old.nabble.com/Help-me-.......-with-Gambas2-and-SQlite3-tp32268253p32269833.html
Sent from the gambas-user mailing list archive at Nabble.com.