Android Question CSV with 5 Columns into SQL-Lite Table and Show in List Box - Help Please!

[MurphmanL]

Hi All,

This is my first post here as , since purchasing B4a I have self taught myself a lot of the things I have needed through reading tutorials and experimentation.

I have built an almost fully functioning app, but I am having one problem which is driving me absoluetely insane.

I have read pretty much every tutorial on this forum regarding Importing a CSV into a SQLlite table and I cannot for the life of me get the hang of it or get it to work.

Can someone please donate to me a few lines of code to import a simple csv of 5 columns into a SQL lite table with the headers, including the code to create and drop the table if it exists or not?

The only other thing I would then need to do is read through the records in the table and if the data in the 'user id' field contained a user ID, extrct the information from a record in another field of the table (for example Employee ID 1001, Postcode - XXX XXX) in a label.

I can figure most things out but the SQLLite is driving me insane! I just want to use StringUtils to import my 5 column CSV into a SQL table(which has to be created too), and then read the data from that table into a listview if certain conditions are true (basically to allow the user to 'pick' a record).

Can anyone please help??

I can't attach the project due to its contents being confidential so hence I am asking for a small code sample to steer me in the right direction.

 

[eps]

So you've imported the CSV into your App?

Then it's just a case of looping round the records read and creating new records in the SQLite DB..

Have you seen this? http://www.b4x.com/android/forum/threads/best-way-to-import-csv-file-in-sqlite-db.34382/

 

[MurphmanL]

Yeah I believe I have seen it,

This is the code I've got so far.

 Try
    SQL1.BeginTransaction
    Dim lst1 As List
    lst1 = su.LoadCSV(File.DirInternal,"Allocation.csv",",")
    For i = 0 To lst1.Size - 1
    Dim sColumn() As String
    sColumn = lst1.Get(i)
    SQL1.ExecNonQuery2("INSERT INTO allocs (a,b,c,d,e) VALUES (?, ?, ?, ?, ?)", sColumn)
    Next
    SQL1.TransactionSuccessful
    Catch
    Log(LastException.Message)
    End Try
    SQL1.EndTransaction
    Msgbox("Allocations Added.","Insert into")

Would this code be enough to make a table or do I have to pre-create a DB for this table to go into?

I have literally been at this for weeks, trying multiple different methods posted on the forum but haven't been able to get the records in!

The process of maps, etc all goes over my head

 

[LucaMs]

You could create the db and the table using a pc tool like "SQLite Expert Personal 3" (free on web), import the db in the Files Tab of IDE and then copy it from there (it corresponds to File.DirAssets) to a writable directory (File.DirDefaultExternal, for example). You can found many posts about this.

Otherwise you could create the table by code, executing a SQL1.ExecNonQuery or SQL1.ExecNonQuery2.
This is the contextual help for SQL1.ExecNonQuery:

Also, you could use DBUtils (to copy the db or create the table) and Flexible Table class.