High all,
I am loading a CSV file into a SQLLite table, the code below works well for appending data to the existing data but i want to give the user the option to replace the existing data. It may require data to be cleared before loading new data or possibly there is an overwrite option.
I see 3 options:
1. Drop the table and recreate it. [Brutal]
2. Delete each record by iteration. [Tedious]
3. Overwrite existing data?
Is there some way [DBUtils??] to clear data from the table in the db?
Regards Roger
I am loading a CSV file into a SQLLite table, the code below works well for appending data to the existing data but i want to give the user the option to replace the existing data. It may require data to be cleared before loading new data or possibly there is an overwrite option.
I see 3 options:
1. Drop the table and recreate it. [Brutal]
2. Delete each record by iteration. [Tedious]
3. Overwrite existing data?
Is there some way [DBUtils??] to clear data from the table in the db?
Regards Roger
B4X:
Sub BtnImpSiteList_click
' This routine:
' Checks PERMISSION_WRITE_EXTERNAL_STORAGE. Creates folder ABT if required
' Checks for the file "sitelist.csv" in the directory File.DirRootExternal&"/ABT/", "sitelist.csv".
VibBip
'Close Menu
PnlMenu.Left = -PnlMenu.Width
rp.CheckAndRequest(rp.PERMISSION_WRITE_EXTERNAL_STORAGE)
Wait For Activity_PermissionResult (Permission As String, Result As Boolean)
If Permission = "android.permission.WRITE_EXTERNAL_STORAGE" And Result = True Then
If File.Exists(File.DirRootExternal&"/ABT/", "") = False Then File.MakeDir(File.DirRootExternal&"/ABT/", "")
End If
' If the file is not found the user briefly sees a short "File not found" message.
' If the file is found the user is given the choice to append this file to the existing sites db, replace the existing data or cancel the operation. WIP
' If the user chooses to proceed the routine will check the file for valid data and no futher action is required by the user. WIP
If File.Exists(File.DirRootExternal&"/ABT/", "sitelist.csv") = False Then
CustomToastMsg.Show("File 'sitelist.csv' not found",1000,Gravity.TOP,0,25%y)
Else
' CHECK VALIDITY OF DATA.
' First record may be heading.
' Each record check first colum for text. If no text assume end of list and exit.
' If text is found check the next colum for a valid number -180 to +180. If true check 3rd colum for valid number -90 to +90.
' Write 3 colums
Private su As StringUtils
Private Table As List
Table = su.LoadCSV(File.DirRootExternal&"/ABT/", "sitelist.csv",",")
Private Table2 As List
Private Items() As String
Private TestTrue As Boolean
Table2.Initialize
For i = 0 To Table.Size - 1
TestTrue = True
Items = Table.Get(i)
'checks for blank in first colum? if so loading is complete
If Items(0) = "" Then Exit
'Next check if second colum is valid Longitude -180 to 180.
If IsNumber (Items(1)) Then
If Items(1) < -180 Or Items(1) > 180 Then TestTrue = False
Else
TestTrue = False
End If
'Next check if second colum is valid Latitude -90 to 90.
If IsNumber (Items(2)) Then
If Items(2) < -90 Or Items(2)> 90 Then TestTrue = False
Else
TestTrue = False
End If
If TestTrue Then
Private m As Map
m.Initialize
m.Put("SiteName", Items(0))
m.Put("Longitude", Items(1))
m.Put("Latitude", Items(2))
Table2.Add(m)
End If
Next
DBUtils.InsertMaps(SQL1, "sites", Table2)
ShowTable
'Display site list
wbvTable.Left = 0
wbvTable.BringToFront
lblSelectedItem.Left = 0
lblSelectedItem.BringToFront
BtnSitesBack.BringToFront
BtnSitesBack.Left = 0
pnlToolBox.Left = 0
pnlToolBox.BringToFront
BtnStore.BringToFront
BtnLandMark.BringToFront
BtnRecall.BringToFront
BtnDel.BringToFront
BtnHelp.BringToFront
BtnSunDial.Visible = False
LblBtnHide.BringToFront
SitesFlag = 1
CustomToastMsg.Show("Upload complete",1000,Gravity.TOP,0,25%y)
End If
End Sub
Last edited: