SQLite to csv

[lawboy]

Hello,
Is there a quick simple way to write the entire sqlite table (including headers) to a txt file using b4a?

Thanks

 

[Erel]

It shouldn't be too difficult.
Steps required:
- Create a list
- Add all the rows as arrays of strings
- Save the list with StringUtils.SaveCSV2

    Dim su As StringUtils
    Dim Cursor1 As Cursor
    Cursor1 = SQL1.ExecQuery("SELECT col1, col2, col3 FROM table1")
    Dim list1 As List
    list1.Initialize
    Dim cols() As String
    For i = 0 To Cursor1.RowCount - 1
        Cursor1.Position = i
        cols = Array As String(Cursor1.GetString2(0), Cursor1.GetString2(1), Cursor1.GetString2(2))
        list1.Add(cols)
    Next
    Cursor1.Close
    su.SaveCSV2(File.DirRootExternal, "db.csv", ",", list1, Array As String("col1", "col2", "col3"))

This example requires the StringUtils library.

 

[lawboy]

Thank You Erel!

 

[cirollo]

not working for me

Hi! Tried this code:

Sub BtInvia_Click
   'recuperiamo i dati da inserire nel file csv da inviare
   Dim su As StringUtils
   Dim Cursor1 As Cursor
   Cursor1 = SQL1.ExecQuery("SELECT IdOrd,IdCli,IdArt,DesArt,Um,QtaOrd,QtaOma,ImpUni,Sconto1,Sconto3,Sconto3,Sconto4,AliIva,DataCons,NoteOrd from ORDINI where DataExp = ''")
   Dim list1 As List
   list1.Initialize
   Dim cols() As String
   For i = 0 To Cursor1.RowCount - 1
        Cursor1.Position = i
         cols = Array As String(Cursor1.GetString2(0), Cursor1.GetString2(1), Cursor1.GetString2(2), Cursor1.GetString2(3), Cursor1.GetString2(4), Cursor1.GetString2(5), _
      Cursor1.GetString2(6), Cursor1.GetString2(7), Cursor1.GetString2(8), Cursor1.GetString2(9), Cursor1.GetString2(10), Cursor1.GetString2(11), Cursor1.GetString2(12), _
      Cursor1.GetString2(13), Cursor1.GetString2(14))
         list1.Add(cols)
   Next
   Cursor1.Close
   su.SaveCSV2(File.DirRootExternal, "ordini.csv", ",", list1, Array As String("col1","col2","col3","col4","col5","col6","col7","col8","col9","col10","col11","col12","col13","col14"))
   'FTP.UploadFile(File.DirRootExternal, EditText1.Text, True, "1.txt")
End Sub

but I get java.lang.indexoutfoboundsexception:
invalid index 0, size is 0
continue?

what does it mean? the query should return some records!

 

[Erel]

Which line raises this error?

 

[cirollo]

SQl to csv problem

this is the code

   Dim su As StringUtils
   Dim Cursor1 As Cursor
   Cursor1 = SQL1.ExecQuery("SELECT Seriale,IdOrd,IdCli,IdArt,Um,QtaOrd,QtaOma,ImpUni,Sconto1,Sconto2,Sconto3,Sconto4,AliIva,DataCons,NoteOrd from ORDINI where DataExp = 'N'")
   Dim list1 As List
   list1.Initialize
   Dim cols() As String
   For i = 0 To Cursor1.RowCount - 1
        Cursor1.Position = i
         cols = Array As String(Cursor1.GetString2(0), Cursor1.GetString2(1), Cursor1.GetString2(2), Cursor1.GetString2(3), Cursor1.GetString2(4), Cursor1.GetString2(5), _
      Cursor1.GetString2(6), Cursor1.GetString2(7), Cursor1.GetString2(8), Cursor1.GetString2(9), Cursor1.GetString2(10), Cursor1.GetString2(11), Cursor1.GetString2(12), _
      Cursor1.GetString2(13), Cursor1.GetString2(14))
         list1.Add(cols)
   Next
   Cursor1.Close
   su.SaveCSV2(File.DirRootExternal, "ordini.csv", ",", list1, Array As String("Seriale","IdOrd","IdCli","IdArt","Um","QtaOrd","QtaOma","ImpUni","Sconto1","Sconto2","Sconto3","Sconto4","AliIva","DataCons","NoteOrd"))

the line is:

su.SaveCSV2(File.DirRootExternal, "ordini.csv", ",", list1, Array As String("Seriale","IdOrd","IdCli","IdArt","Um","QtaOrd","QtaOma","ImpUni","Sconto1","Sconto2","Sconto3","Sconto4","AliIva","DataCons","NoteOrd"))

maybe because some of the fields are null? how do I treat null values in SQL lite?

 

[Erel]

Are you sure that there is at least one row in list1? You can use the debugger to check it.

 

[cirollo]

SQL to csv

how can I check it?

can I check also in the code.....if....then....

 

[Erel]

Yes:

If list1.Size > 0 Then ...

 

[luciano deri]

Before of all Tanks. To do the opposite operation to importo data from csv to sqlite, how can i do?

 

[Erel]

Read the CSV file with StringUtils.LoadCSV. Iterate over the rows and add them to the table.

Something like:

Dim ListOfMaps As List
ListOfMaps.Initialize
Dim rows As List = StringUtils.LoadCSV(...)
For Each row() As String In rows
 ListOfMaps.Add(CreateMap("Column1": row(0), "Column2": row(1) ,...)) ' make sure that the column names are correct
Next
DBUtils.InsertMaps(SQL1, "Table1", ListOfMaps)

 

[delozoya]

Hello Erel. I used you example for the a sqlite to csv. But mu database include many data (13 columns and many rows+2000). when I execute the sub rutine dont copy all rows of the my database. Is there a solution? Thanks

 

[Erel]

Is there any error message?

Can you post the relevant code?

 

[delozoya]

no error. the code is

fecha=DateTime.Date(DateTime.Now)
hora=DateTime.GetHour(DateTime.Now)&":"&DateTime.GetMinute(DateTime.Now)
mes=DateTime.GetMonth(DateTime.Now)
año=DateTime.GetYear(DateTime.Now)

Dim su As StringUtils
    Dim Cursor1 As Cursor
    Cursor1 = informes.ExecQuery("SELECT FECHA, HORA, FASE, FRECUENCIA, FACTOR_DE_POTENCIA, VOLTAJE, CORRIENTE, POTENCIA, POTENCIA_REACTIVA, POTENCIA_APARENTE, ENERGIA, hola, TENSION_COMPUESTA FROM informe")
    Dim list1 As List
    list1.Initialize
    Dim cols() As String
    For i = 0 To Cursor1.RowCount - 1
        Cursor1.Position = i
        cols = Array As String(Cursor1.GetString2(0), Cursor1.GetString2(1), Cursor1.GetString2(2), Cursor1.GetString2(3), Cursor1.GetString2(4), Cursor1.GetString2(5),Cursor1.GetString2(6), Cursor1.GetString2(7), Cursor1.GetString2(8), Cursor1.GetString2(9), Cursor1.GetString2(10), Cursor1.GetString2(11),Cursor1.GetString2(12))
        list1.Add(cols)
      
    Next
    Cursor1.Close
    If list1.Size > 0 Then
       su.SaveCSV2(File.DirRootExternal, "Informes_"&mes&"-"&año&".csv", ",", list1, Array As String("FECHA", "HORA", "FASE","FRECUENCIA", "FACTOR DE POTENCIA", "VOLTAJE","CORRIENTE", "POTENCIA", "POTENCIA REACTIVA","POTENCIA APARENTE", "ENERGIA", " ", "TENSION COMPUESTA"))
    Else
  
    ToastMessageShow("No hay datos suficientes",True)
    End If

 

[udg]

Hi delozoya,

I don't know if it could be related to your problem, but look at your next to last field in the Select statement.
Do you really have a field named hola?
And did you choose to name that column like " " (a space)?

udg

 

[delozoya]

Yes. This name his hola because i have a problem for a null name. Then when I create the name " " is for the colums is " ". When create de csv, is good, but only create the first line, exactly aprox 60 lines for database (in the database has more lines)

 

[udg]

If I understand it correctly, your CSV sports now an header row followed by about 60 data rows when you expect a greater number.
Did you inspect the data in the DB?
A second question: did you try to log(list1.size) to at least verify that the csv file has as many rows as there in list1? If this holds true, the problem is not in the csv creation.

udg

 

[delozoya]

Yes. in db are all data. I thought it could be because they did not have time to create it, because I believe it and then copy it. now I've trying until tomorrow (which will have a lot of data) and see what happens. Thanks

 

[Erel]

There is no such thing as "did not have time to create it". The operation will either succeed or fail.

 

[delozoya]

Today I remove the old csv. In DB there are 760 row. when i create csv, In csv only 4 first row. Any Solution?