sqlLite limit ?

[gapi]

Some known whether there is a limit to the size of a table SqlLite? I have a problem importing data from a csv file in the database (1378 Kb)

tnx

 

[Erel]

The limit is huge. Which error do you encounter?

 

[Yafuhenk]

I recently imported a csv file of more than 60 Mb. It takes a few minutes but it is imported without any problem.

Here is a part of the code i use for importing the csv

Sub CSVImport
   Dim I, j As Int
   Dim FileDialog1 As FileDialog
   Dim cd As CustomDialog
   Dim Answer As Int
   FileDialog1.FilePath = File.DirRootExternal
   If FileDialog1.Show("Select csv file", "Ok", "Cancel", "", Null) = DialogResponse.POSITIVE Then
         FileName = FileDialog1.ChosenName
         FilePath = FileDialog1.FilePath
      Else
         ProgressDialogHide
         Return False
      End If
   End If
   'set db
   SQL1.Initialize(File.DirRootExternal", "database.sqlite", False, Password, File.DirRootExternal)
   Dim Answer As Int   
   Dim pnl As Panel
   pnl.Initialize("pnl")   
   cd.AddView(pnl, 9%x, 7%y, 9%x, 4%y) 
   'If there is a DB already aks if the new data should append to the current DB or not
   If NoDB = False Then
      Answer = cd.Show("Append data to existing table?", "Yes", "No", "Cancel", LoadBitmap(File.DirAssets, "import.png"))
   Else
      'If No that drop table and create new DB
      Answer = -3
   End If
   Select Case Answer
   Case -1 'Yes
      SQL1.BeginTransaction
   Case -2 'Cancel
      ProgressDialogHide
      Return False
   Case -3 'No
      SQL1.ExecNonQuery("DROP TABLE IF EXISTS Figures")
      SQL1.ExecNonQuery("CREATE TABLE Figures (SalesOrgNumber TEXT, SalesOrgName TEXT, ArtGrpNumber TEXT, ArtGrpName TEXT, CustomerNumber INT, CustomerName TEXT, CountryCode TEXT, CountryName TEXT, Period TEXT, ArticleNumber TEXT, ArticleName TEXT, SalesTurnover REAL, SalesQuantity REAL, InOrderTurnover REAL, InOrderQuantity REAL, Forecast1 REAL, Forecast2 REAL, Forecast3 REAL)")
      SQL1.BeginTransaction
   End Select
   Dim Reader As TextReader
   Reader.Initialize(File.OpenInput(FilePath, FileName))
   Try
      Dim line As String
      'Don´t do anything with this first two line since it contains the title
      Reader.ReadLine
      line = Reader.ReadLine
      line = Reader.ReadLine
      Do While line <> Null 
         j = j + 1
         line = Reader.ReadLine
         'Check if line contains a Result or Non-Defined ->(#). If so, do nothing
         If line.Contains("Result") OR line.Contains("#") OR line.Contains("Ergebnis") Then
         Else
            Dim ColumnContent() As String
            ColumnContent = Regex.Split(";", line)
            For I = 0 To 14
               ColumnContent(I) = ColumnContent(I).Replace(QUOTE, "")
               'SalesQuantity, InOrderTurnover and InOrderQuantity can be NULL. This is to catch that issue
               If ColumnContent(I).Length = 0 Then
                  Select Case I
                  Case 13
                     ColumnContent(I) = "0,00 EUR"
                  Case 12, 14
                     ColumnContent(I) = "0,00 PC"
                  End Select
               End If   
               'Convert layout -> 12.345,67 € to useable layout -> 12345.67 
               Select Case I
               Case 11, 13
                  Try
                     ColumnContent(I) = ColumnContent(I).SubString2(0, ColumnContent(I).Length - 4) '-4 for EUR & Space
                     ColumnContent(I) = ColumnContent(I).Replace(".", "")
                     ColumnContent(I) = ColumnContent(I).Replace(",", ".")
                  Catch
                     'Log(i & " " & j & " " & LastException.Message)
                  End Try
                  'Convert layout -> 1.234,5 KG to useable layout -> 1234.5 
               Case 12, 14
                  Try
                     ColumnContent(I) = ColumnContent(I).SubString2(0, ColumnContent(I).Length - 3) '-3 for KG or PC and Space
                     ColumnContent(I) = ColumnContent(I).Replace(".", "")
                     ColumnContent(I) = ColumnContent(I).Replace(",", ".")
                  Catch
                     Log(I & " " & j & " " & LastException.Message)
                  End Try
               End Select
            Next
            SQL1.ExecNonQuery2("INSERT INTO Figures VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", Array As Object (ColumnContent(0), ColumnContent(1), ColumnContent(2), ColumnContent(3), ColumnContent(4), ColumnContent(5), ColumnContent(6), ColumnContent(7), ColumnContent(8), ColumnContent(9), ColumnContent(10), ColumnContent(11), ColumnContent(12), ColumnContent(13),ColumnContent(14),0,0,0))
            'DoEvents is needed for ProgressDialog
            DoEvents
         End If
      Loop
   Catch
      Log("CSV read loop " & LastException.Message)
   End Try
   Reader.Close
   SQL1.TransactionSuccessful
   SQL1.EndTransaction
   'Delete double records in the database
   If Answer = -1 Then 'When append is selected delete duplicate records
      SQL1.ExecNonQuery("DELETE FROM Figures WHERE rowid Not IN (SELECT MAX(rowid) FROM Figures GROUP BY CustomerNumber, Period, ArticleNumber)")
   End If
   'Create Index
   SQL1.ExecNonQuery("DROP INDEX IF EXISTS CustomerIndex")
   SQL1.ExecNonQuery("CREATE INDEX CustomerIndex ON Figures (CustomerNumber)")
   ProgressDialogHide
   Msgbox("CSV file imported. Ready for use!", "")
   NoDB = False
   Activity.CloseMenu
End Sub

Succes

Henk

 

[gapi]

Thanks all