Android Question SQLite Table to CSV file

[Pilar-JLSineriz]

Hello

Is there the possibilty to export a SQlite table to CSV file by means of code? I read something in stringutils functions

Thanks!!!

​

 

[Mahares]

You can use the DButils class and export the table to a csv exactly as shown in this thread:
https://www.b4x.com/android/forum/threads/sqllite-to-csv-and-dbutils.55487/#content

Or you can use code similar to the below without resorting to DButils::

Dim DBFilePath As String    = File.DirRootExternal
Dim MyTextWriter As TextWriter
Dim MyList As List
Dim MyCode As String 
Dim txt As String

MyList.Initialize     
MyTextWriter.Initialize(File.openoutput(DBFilePath,"Chems.csv",False))
txt = "SELECT * FROM tblChemicals"
Cursor1 = SQL1.ExecQuery(txt)         
For i=0 To Cursor1.RowCount-1
    Cursor1.Position=i                 
    Mycode =Cursor1.GetString("CODE") & "," & Cursor1.GetString("CHEMICAL")    'comma delimnited text File
    MyList.Add(Mycode)     
Next
MyTextWriter.WriteList(Mylist) 
MyTextWriter.close

 

[Pilar-JLSineriz]

Thanks, Mahares, I'll try it!!!!!!!!!!!

 

[Erel]

You should use StringUtils.SaveCSV to create a csv file. The above code will create a corrupted CSV file is there is a comma or quote in one of the fields.

 

[Pilar-JLSineriz]

Thanks Erel.. I have not time to test the code.. I suppose that it will be complicated for me..

 

[Pilar-JLSineriz]

Hi,
Are there other methods to export data from sqlite table from B4A code? Thanks

 

[Erel]

Use DBUtils.ExecuteMemoryTable.

 

[Pilar-JLSineriz]

Hi,
I want to make a JOIN between two tables of a SQLITE BD, and export the result to excel format, or csv format, how could I make it? thanks

(First, is it possible to realize a JOIN SQL instruction in B4A code???)

 

[Informatix]

Hi,
I want to make a JOIN between two tables of a SQLITE BD, and export the result to excel format, or csv format, how could I make it? thanks

(First, is it possible to realize a JOIN SQL instruction in B4A code???)

JOIN (INNER, LEFT, etc) are clauses of SQL SELECT, and SQL commands are sent by B4A, not run (this is the work of the DBMS).
If you're used to LINQ for example, just forget. You have to compose the SQL query as a string ("SELECT * FROM Table1 INNER JOIN Table2 ON...").

 

[eurojam]

you can also consider to store your query as a view within the database:

CREATE VIEW "view1" AS SELECT Name, Day FROM Customers AS C JOIN Reservations AS R ON C.CustomerId=R.CustomerId;

and later query something like select * from view1 from b4a