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

[Fabien Bodard]

2011/8/16 charlesg <charles at ...1784...>:
>
>
> "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.

split(stext,",","\"'")   ???



 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.

and if you need do that just one time there is an import tool in gb3
and ... i don't remember for gb2

just configure the db flieds type before ... in other case all will be text

>
>
> 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.
>
>
> ------------------------------------------------------------------------------
> uberSVN's rich system and user administration capabilities and model
> configuration take the hassle out of deploying and managing Subversion and
> the tools developers use with it. Learn more about uberSVN and get a free
> download at:  http://p.sf.net/sfu/wandisco-dev2dev
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>



-- 
Fabien Bodard