Big Csv import problem

cirollo · Jan 31, 2012

HI! I've a csv files of approx 37000 rows
I try to import in my SQL Table with this:

   ' Creo la tabella Clienti
   Dim m As Map
   m.Initialize
   m.Put("IdCli", DBUtils.DB_TEXT)
   m.Put("Ragsoc", DBUtils.DB_TEXT)
   m.Put("Indirizzo", DBUtils.DB_TEXT)
   m.Put("Cap", DBUtils.DB_TEXT)
   m.Put("Localita", DBUtils.DB_TEXT)
   m.Put("Prov", DBUtils.DB_TEXT)
   m.Put("Telefono", DBUtils.DB_TEXT)
   m.Put("Mobile", DBUtils.DB_TEXT)
   m.Put("Iniziale", DBUtils.DB_TEXT)
   m.Put("Giorno", DBUtils.DB_TEXT)
   m.Put("Status", DBUtils.DB_TEXT)
   m.Put("Listino", DBUtils.DB_TEXT)
   m.Put("CatScm", DBUtils.DB_TEXT)
   m.Put("AliIva", DBUtils.DB_TEXT)
   DBUtils.CreateTable(SQL1, "Clienti", m, "IdCli")
   ' popolo la tabella clienti
   TxtLog.Text = "Inserimento Dati Tabella Clienti..." &Chr(10)&TxtLog.Text
   Dim su As StringUtils
   Dim Table As List
   Table = su.LoadCSV(sdRoot, "clienti.csv", ";")
   Dim Table2 As List
   Dim Items() As String
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      m.Put("IdCli", Items(0)) 
      m.Put("Ragsoc", Items(1))
      m.Put("Indirizzo", Items(2))
      m.Put("Cap", Items(3))
      m.Put("Localita", Items(4))
      m.Put("Prov", Items(5))
      m.Put("Telefono", Items(6))
      m.Put("Mobile", Items(7))
      m.Put("Iniziale", Items(1).SubString2(0,1))
      m.Put("Giorno", Items(8))
      m.Put("Status", Items(9))
      m.Put("Listino", Items(10))
      m.Put("CatScm", Items(11))
      m.Put("AliIva", Items(12))
      LblStatus.Text = Items(0)
      Table2.Add(m)
   Next
   DBUtils.InsertMaps(SQL1, "Clienti", Table2)
   Progressbar1.Progress = 50
   LblStatus.Text = Progressbar1.Progress &"%"

but after some seconds the tablet screen becomes all black and starting to vibrate.
After that forced closure is invoked

If I use a smaller file It doesn't happen. The db is on the sd card.
This is the complete code, You can notice that there are several tables that should be filled (each one with its own file csv)

Code: Copy

Sub PopolaTabelle
   Msgbox("popola tabelle","")
      ' Delete old tables and create new ones.
'   TxtLog.Text = "Cancellazione Tabelle Database..."&Chr(10)&TxtLog.Text
'   DBUtils.DropTable(SQL1, "Clienti")
'   DBUtils.DropTable(SQL1, "Parape")
'   DBUtils.DropTable(SQL1, "Articoli")
'   DBUtils.DropTable(SQL1, "Listini")
'   DBUtils.DropTable(SQL1, "Giacenze")
''   DBUtils.DropTable(SQL1, "Scomag")
'   DBUtils.DropTable(SQL1, "Storicoprz")
'   DBUtils.DropTable(SQL1, "Famiglie")
'   DBUtils.DropTable(SQL1, "Gruppi")
'   DBUtils.DropTable(SQL1, "CodiciIva")
   TxtLog.Text = "Creazione Tabelle Database ed Import Dati..."&Chr(10)&TxtLog.Text
   LblStatus.Text = Progressbar1.Progress &"%"
   '****************************************************************
   ' Creo la tabella Clienti
   Dim m As Map
   m.Initialize
   m.Put("IdCli", DBUtils.DB_TEXT)
   m.Put("Ragsoc", DBUtils.DB_TEXT)
   m.Put("Indirizzo", DBUtils.DB_TEXT)
   m.Put("Cap", DBUtils.DB_TEXT)
   m.Put("Localita", DBUtils.DB_TEXT)
   m.Put("Prov", DBUtils.DB_TEXT)
   m.Put("Telefono", DBUtils.DB_TEXT)
   m.Put("Mobile", DBUtils.DB_TEXT)
   m.Put("Iniziale", DBUtils.DB_TEXT)
   m.Put("Giorno", DBUtils.DB_TEXT)
   m.Put("Status", DBUtils.DB_TEXT)
   m.Put("Listino", DBUtils.DB_TEXT)
   m.Put("CatScm", DBUtils.DB_TEXT)
   m.Put("AliIva", DBUtils.DB_TEXT)
   DBUtils.CreateTable(SQL1, "Clienti", m, "IdCli")
   ' popolo la tabella clienti
   TxtLog.Text = "Inserimento Dati Tabella Clienti..." &Chr(10)&TxtLog.Text
   Dim su As StringUtils
   Dim Table As List
   Table = su.LoadCSV(sdRoot, "clienti.csv", ";")
   Dim Table2 As List
   Dim Items() As String
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      m.Put("IdCli", Items(0)) 
      m.Put("Ragsoc", Items(1))
      m.Put("Indirizzo", Items(2))
      m.Put("Cap", Items(3))
      m.Put("Localita", Items(4))
      m.Put("Prov", Items(5))
      m.Put("Telefono", Items(6))
      m.Put("Mobile", Items(7))
      m.Put("Iniziale", Items(1).SubString2(0,1))
      m.Put("Giorno", Items(8))
      m.Put("Status", Items(9))
      m.Put("Listino", Items(10))
      m.Put("CatScm", Items(11))
      m.Put("AliIva", Items(12))
      LblStatus.Text = Items(0)
      Table2.Add(m)
   Next
   DBUtils.InsertMaps(SQL1, "Clienti", Table2)
   Progressbar1.Progress = 50
   LblStatus.Text = Progressbar1.Progress &"%"

   '****************************************************************
   ' Creo la tabella Partite Aperte
   Dim m As Map
   m.Initialize
   m.Put("IdPar", DBUtils.DB_TEXT)
   m.Put("IdCli", DBUtils.DB_TEXT)
   m.Put("ImpSal", DBUtils.DB_TEXT)
   m.Put("ImpInc", DBUtils.DB_TEXT)
   m.Put("DataInc", DBUtils.DB_TEXT)
   m.Put("NumDoc", DBUtils.DB_TEXT)
   m.Put("DataDoc", DBUtils.DB_TEXT)
   m.Put("DataExp", DBUtils.DB_TEXT)
   DBUtils.CreateTable(SQL1, "Parape", m, "IdPar")
   ' popolo la tabella clienti
   TxtLog.Text = "Inserimento Dati Tabella Partite Aperte..." &Chr(10)&TxtLog.Text
   Dim su As StringUtils
   Dim Table As List
   Table = su.LoadCSV(sdRoot, "parape.csv", ";")
   Dim Table2 As List
   Dim Items() As String
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      m.Put("IdPar", Items(0)) 
      m.Put("IdCli", Items(1))
      m.Put("ImpSal", Items(2))
      m.Put("ImpInc", Items(3))
      m.Put("DataInc", Items(4))
      m.Put("NumDoc", Items(5))
      m.Put("DataDoc", Items(6))
      m.Put("DataExp", "N")
      Table2.Add(m)
   Next
   DBUtils.InsertMaps(SQL1, "Parape", Table2)
   Progressbar1.Progress = 55
   LblStatus.Text = Progressbar1.Progress &"%"

   '****************************************************************
   ' Creo la tabella Articoli
   Dim m As Map
   m.Initialize
   m.Put("CodiceArt", DBUtils.DB_TEXT)
   m.Put("Desart", DBUtils.DB_TEXT)
   m.Put("IdArt", DBUtils.DB_TEXT)
   m.Put("Um", DBUtils.DB_TEXT)
   m.Put("Famiglia", DBUtils.DB_TEXT)
   m.Put("GruMer", DBUtils.DB_TEXT)
   m.Put("CatScm", DBUtils.DB_TEXT)
   m.Put("AliIva", DBUtils.DB_TEXT)
   DBUtils.CreateTable(SQL1, "Articoli", m, "CodiceArt")
   ' popolo la tabella articoli
   TxtLog.Text = "Inserimento Dati Tabella Articoli..." &Chr(10)&TxtLog.Text
   Table = su.LoadCSV(sdRoot, "articoli.csv", ";")
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      m.Put("CodiceArt", Items(0)) 
      m.Put("Desart", Items(1))
      m.Put("Idart", Items(2))
      m.Put("Um", Items(3))
      m.Put("Famiglia", Items(4))
      m.Put("GruMer", Items(5))
      m.Put("CatScm", Items(6))
      m.Put("AliIva", Items(7))
      Table2.Add(m)
   Next
   DBUtils.InsertMaps(SQL1, "Articoli", Table2)
   Progressbar1.Progress = 60
   LblStatus.Text = Progressbar1.Progress &"%"
   
   
   '****************************************************************
   ' Creo la tabella Listini (Articoli)
   Dim m As Map
   m.Initialize
   m.Put("Seriale", DBUtils.DB_TEXT)
   m.Put("IdArt", DBUtils.DB_TEXT)
   m.Put("IdListino", DBUtils.DB_TEXT)
   m.Put("ImpUni", DBUtils.DB_TEXT)
   m.Put("Sconto1", DBUtils.DB_TEXT)
   m.Put("Sconto2", DBUtils.DB_TEXT)
   m.Put("Sconto3", DBUtils.DB_TEXT)
   m.Put("Sconto4", DBUtils.DB_TEXT)
   DBUtils.CreateTable(SQL1, "Listini", m, "Seriale")
   ' popolo la tabella 
   TxtLog.Text = "Inserimento Dati Tabella Listini Articoli..." &Chr(10)&TxtLog.Text
   Table = su.LoadCSV(sdRoot, "listini.csv", ";")
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      m.Put("Seriale", ""&(i+1))
      m.Put("IdArt", Items(0))
      m.Put("IdListino", Items(1))
      m.Put("ImpUni", Items(2))
      m.Put("Sconto1", Items(3))
      m.Put("Sconto2", Items(4))
      m.Put("Sconto3", Items(5))
      m.Put("Sconto4", Items(6))
      Table2.Add(m)
   Next
   DBUtils.InsertMaps(SQL1, "Listini", Table2)
    Progressbar1.Progress = 65
   
   '****************************************************************
   ' Creo la tabella Giacenze (Articoli)
   Dim m As Map
   m.Initialize
   m.Put("Seriale", DBUtils.DB_TEXT)
   m.Put("IdArt", DBUtils.DB_TEXT)
   m.Put("IdMag", DBUtils.DB_TEXT)
   m.Put("Esistenza", DBUtils.DB_TEXT)
   m.Put("DataAgg", DBUtils.DB_TEXT)
   DBUtils.CreateTable(SQL1, "Giacenze", m, "Seriale")
   ' popolo la tabella 
   TxtLog.Text = "Inserimento Dati Tabella Giacenze Articoli..." &Chr(10)&TxtLog.Text
   Table = su.LoadCSV(sdRoot, "giacenze.csv", ";")
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize 
      m.Put("Seriale", ""&(i+1))
      m.Put("IdArt", Items(0))
      m.Put("IdMag", Items(1))
      m.Put("Esistenza", Items(2))
      m.Put("DataAgg", "")
      Table2.Add(m)
   Next
   DBUtils.InsertMaps(SQL1, "Giacenze", Table2)
    Progressbar1.Progress = 70
   
   '****************************************************************
   ' Creo la tabella Scomag
   Dim m As Map
   m.Initialize
   m.Put("Seriale", DBUtils.DB_TEXT)
   m.Put("CatScmCli", DBUtils.DB_TEXT)
   m.Put("CatScmArt", DBUtils.DB_TEXT)
   m.Put("Sconto1", DBUtils.DB_TEXT)
   m.Put("Sconto2", DBUtils.DB_TEXT)
   m.Put("Sconto3", DBUtils.DB_TEXT)
   m.Put("Sconto4", DBUtils.DB_TEXT)
   DBUtils.CreateTable(SQL1, "Scomag", m, "Seriale")
'   SQL1.ExecNonQuery("CREATE INDEX index1 ON Giacenze (IdArt, IdMag)")
   ' popolo la tabella 
   TxtLog.Text = "Inserimento Dati Tabella Sconti e Maggiorazioni..." &Chr(10)&TxtLog.Text
'   Dim su As StringUtils
'   Dim Table As List
   Table = su.LoadCSV(sdRoot, "scomag.csv", ";")
'   Dim Table2 As List
'   Dim Items() As String
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize 
      m.Put("Seriale", ""&(i+1))
      m.Put("CatScmCli", Items(0))
      m.Put("CatScmArt", Items(1))
      m.Put("Sconto1", Items(2))
      m.Put("Sconto2", Items(3))
      m.Put("Sconto3", Items(4))
      m.Put("Sconto4", Items(5))
      Table2.Add(m)
   Next
   DBUtils.InsertMaps(SQL1, "Scomag", Table2)
    Progressbar1.Progress = 75
   
   '****************************************************************
   ' Creo la tabella Storicoprz
   Dim m As Map
   m.Initialize
   m.Put("Seriale", DBUtils.DB_TEXT)
   m.Put("IdArt", DBUtils.DB_TEXT)
   m.Put("IdCli", DBUtils.DB_TEXT)
   m.Put("DataDoc", DBUtils.DB_TEXT)
   m.Put("ImpDoc", DBUtils.DB_TEXT)
   m.Put("Sconto1", DBUtils.DB_TEXT)
   m.Put("Sconto2", DBUtils.DB_TEXT)
   m.Put("Sconto3", DBUtils.DB_TEXT)
   m.Put("Sconto4", DBUtils.DB_TEXT)
   DBUtils.CreateTable(SQL1, "Storicoprz", m, "Seriale")
'   SQL1.ExecNonQuery("CREATE INDEX index1 ON Giacenze (IdArt, IdMag)")
   ' popolo la tabella 
   TxtLog.Text = "Inserimento Dati Tabella Storico Prezzi..." &Chr(10)&TxtLog.Text
'   Dim su As StringUtils
'   Dim Table As List
   Table = su.LoadCSV(sdRoot, "storicoprz.csv", ";")
'   Dim Table2 As List
'   Dim Items() As String
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize 
      m.Put("Seriale", ""&(i+1))
      m.Put("IdArt", Items(0))
      m.Put("IdCli", Items(1))
      m.Put("DataDoc", Items(2))
      m.Put("ImpDoc", Items(3))
      m.Put("Sconto1", Items(4))
      m.Put("Sconto2", Items(5))
      m.Put("Sconto3", Items(6))
      m.Put("Sconto4", Items(7))
      Table2.Add(m)
   Next
   DBUtils.InsertMaps(SQL1, "Storicoprz", Table2)
    Progressbar1.Progress = 80
   
   '****************************************************************
   ' Creo la tabella Famiglie
   Dim m As Map
   m.Initialize
   m.Put("Famiglia", DBUtils.DB_TEXT)
   DBUtils.CreateTable(SQL1, "Famiglie", m, "Famiglia")
   ' popolo la tabella 
   TxtLog.Text = "Inserimento Dati Tabella Famiglie..." &Chr(10)&TxtLog.Text
'   Dim su As StringUtils
'   Dim Table As List
   Table = su.LoadCSV(sdRoot, "tabelle.csv", ";")
'   Dim Table2 As List
'   Dim Items() As String
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      If Items(0)="FA" Then 
       m.Put("Famiglia", Items(1)) 
       Table2.Add(m)
      End If 
   Next
   DBUtils.InsertMaps(SQL1, "Famiglie", Table2)
    Progressbar1.Progress = 85
   
   '****************************************************************
   ' Creo la tabella Gruppi Merceologici
   Dim m As Map
   m.Initialize
   m.Put("GruMer", DBUtils.DB_TEXT)
   DBUtils.CreateTable(SQL1, "Gruppi", m, "GruMer")
   ' popolo la tabella 
   TxtLog.Text = "Inserimento Dati Tabella Gruppi Merceologici..." &Chr(10)&TxtLog.Text
'   Dim su As StringUtils
'   Dim Table As List
   Table = su.LoadCSV(sdRoot, "tabelle.csv", ";")
'   Dim Table2 As List
'   Dim Items() As String
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      If Items(0)="GM" Then 
       m.Put("GruMer", Items(1)) 
       Table2.Add(m)
      End If 
   Next
   DBUtils.InsertMaps(SQL1, "Gruppi", Table2)
    Progressbar1.Progress = 90
   
   '****************************************************************
   ' Creo la tabella Aliquote Iva
   Dim m As Map
   m.Initialize
   m.Put("AliIva", DBUtils.DB_TEXT)
   DBUtils.CreateTable(SQL1, "CodiciIva", m, "AliIva")
   ' popolo la tabella 
   TxtLog.Text = "Inserimento Dati Tabella Aliquote Iva..." &Chr(10)&TxtLog.Text
'   Dim su As StringUtils
'   Dim Table As List
   Table = su.LoadCSV(sdRoot, "tabelle.csv", ";")
'   Dim Table2 As List
'   Dim Items() As String
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      If Items(0)="CI" Then 
       m.Put("AliIva", Items(1)) 
       Table2.Add(m)
      End If 
   Next
   DBUtils.InsertMaps(SQL1, "CodiciIva", Table2)
    Progressbar1.Progress = 95
   '****************************************************************
   ' Creo la tabella Ordini
   Dim m As Map
   m.Initialize
   m.Put("Seriale", DBUtils.DB_TEXT)
   m.Put("IdOrd", DBUtils.DB_TEXT)
   m.Put("DataOrd", DBUtils.DB_TEXT)
   m.Put("OraOrd", DBUtils.DB_TEXT)
   m.Put("IdCli", DBUtils.DB_TEXT)
   m.Put("Ragsoc", DBUtils.DB_TEXT)
   m.Put("IdArt", DBUtils.DB_TEXT)
   m.Put("DesArt", DBUtils.DB_TEXT)
   m.Put("Um", DBUtils.DB_TEXT)
   m.Put("QtaOrd", DBUtils.DB_TEXT)
   m.Put("QtaOma", DBUtils.DB_TEXT)
   m.Put("ImpUni", DBUtils.DB_TEXT)
   m.Put("Sconto1", DBUtils.DB_TEXT)
   m.Put("Sconto2", DBUtils.DB_TEXT)
   m.Put("Sconto3", DBUtils.DB_TEXT)
   m.Put("Sconto4", DBUtils.DB_TEXT)
   m.Put("AliIva", DBUtils.DB_TEXT)
   m.Put("DataCons", DBUtils.DB_TEXT)
   m.Put("NoteOrd", DBUtils.DB_TEXT)
   m.Put("DataExp", DBUtils.DB_TEXT)
   DBUtils.CreateTable(SQL1, "Ordini", m, "Seriale")
   Progressbar1.Progress = 95
'   Dim ListOfMaps As List
'      ListOfMaps.Initialize
'      m.Initialize
'      m.Put("Seriale", "311220111530AHE")
'      m.Put("IdOrd", "311220111530")
'      m.Put("DataOrd", "31/12/2011")
'      m.Put("OraOrd", "15:30")
'      m.Put("IdCli", "ANALOGICA")
'      m.Put("RagSoc", "Angeli Web Center")
'      m.Put("IdArt", "AHE")
'      m.Put("DesArt", "Ad Hoc Enterprise")
'      m.Put("Um", "n.")
'      m.Put("QtaOrd", "1")
'      m.Put("QtaOma", "0")
'      m.Put("ImpUni", "1500")
'      ListOfMaps.Add(m)
'      DBUtils.InsertMaps(SQL1, "Ordini", ListOfMaps)
      
'      ListOfMaps.Initialize
'      m.Initialize
'      m.Put("Seriale", "311220111530AHR")
'      m.Put("IdOrd", "311220111530")
'      m.Put("DataOrd", "31/12/2011")
'      m.Put("OraOrd", "15:30")
'      m.Put("IdCli", "ANALOGICA")
'      m.Put("RagSoc", "Angeli Web Center")
'      m.Put("IdArt", "AHR")
'      m.Put("DesArt", "Ad Hoc Revolution")
'      m.Put("Um", "n.")
'      m.Put("QtaOrd", "2")
'      m.Put("QtaOma", "0")
'      m.Put("ImpUni", "500")
'      ListOfMaps.Add(m)
'      DBUtils.InsertMaps(SQL1, "Ordini", ListOfMaps)
   LblStatus.Text = ""
    Progressbar1.Progress = 100
   Msgbox2("Ricezione completata!","McOrdini","","Ok","",LoadBitmap (File.DirAssets, "warning_256.png"))
   Progressbar1.Visible = False
   TxtLog.Visible = False
   LblStatus.Visible = False
   LblProgress.Visible = False
End Sub

does it end to fill one table before going to the next one?

thedesolatesoul · Jan 31, 2012

What error message do you get in the Logs?
Only that can tell what the real problem is.

cirollo · Jan 31, 2012

how can I check the logs?

where I can find them?

thedesolatesoul · Jan 31, 2012

Are you running with B4a-Bridge?
On the IDE, on the right hand there is a Logs tab. Press Connect on there.
If you find nothing, its worth checking unfiltered logs too.

cirollo · Jan 31, 2012

This is my log

as I expected the problem is on table Articoli, too much records?

Code: Copy

Downloaded 165KB
Downloaded 165KB
Downloaded 166KB
giacenze.csv, Success=true
Downloaded 0KB
scomag.csv, Success=true
Downloaded 0KB
storicoprz.csv, Success=true
Downloaded 0KB
tabelle.csv, Success=true
InsertMaps (first query out of 2788): INSERT INTO [Clienti] ([IdCli], [Ragsoc], [Indirizzo], [Cap], [Localita], [Prov], [Telefono], [Mobile], [Iniziale], [Giorno], [Status], [Listino], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 3): INSERT INTO [Parape] ([IdPar], [IdCli], [ImpSal], [ImpInc], [DataInc], [NumDoc], [DataDoc], [DataExp]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 37631): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
** Activity (main) Resume **

this is unfiltered log....out of memory?

Code: Copy

GC_FOR_MALLOC freed 57207 objects / 3333648 bytes in 362ms
Clamp target GC heap from 48.835MB to 48.000MB
GC_FOR_MALLOC freed 55911 objects / 3259248 bytes in 351ms
Clamp target GC heap from 48.872MB to 48.000MB
GC_FOR_MALLOC freed 28280 objects / 1658856 bytes in 492ms
Forcing collection of SoftReferences for 1210-byte allocation
Clamp target GC heap from 48.872MB to 48.000MB
GC_FOR_MALLOC freed 1 objects / 32 bytes in 326ms
Out of memory on a 1210-byte allocation.
"main" prio=5 tid=1 RUNNABLE
  | group="main" sCount=0 dsCount=0 s=N obj=0x4001d8b0 self=0xcd28
  | sysTid=3224 nice=0 sched=0/0 cgrp=default handle=-1345017808
  at android.text.SpannableStringBuilder.toString(SpannableStringBuilder.java:~867)
  at anywheresoftware.b4a.objects.TextViewWrapper.getText(TextViewWrapper.java:27)
  at ordini.emmeci.sync._popolatabelle(sync.java:1745)
  at ordini.emmeci.sync._ftp_downloadcompleted(sync.java:731)
  at java.lang.reflect.Method.invokeNative(Native Method)
  at java.lang.reflect.Method.invoke(Method.java:521)
  at anywheresoftware.b4a.BA.raiseEvent2(BA.java:105)
  at anywheresoftware.b4a.BA$1.run(BA.java:210)
  at android.os.Handler.handleCallback(Handler.java:587)
  at android.os.Handler.dispatchMessage(Handler.java:92)
  at android.os.Looper.loop(Looper.java:123)
  at android.app.ActivityThread.main(ActivityThread.java:4627)
  at java.lang.reflect.Method.invokeNative(Native Method)
  at java.lang.reflect.Method.invoke(Method.java:521)
  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:871)
  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:629)
  at dalvik.system.NativeStart.main(Native Method)
Shutting down VM
threadid=1: thread exiting with uncaught exception (group=0x4001d7d0)
GC_FOR_MALLOC freed 865661 objects / 41958128 bytes in 183ms
FATAL EXCEPTION: main

thedesolatesoul · Jan 31, 2012

Yes, you are out of memory.
Do you need to load everything into memory?
What are you trying to do?

Sent from my GT-I9000 using Tapatalk

cirollo · Jan 31, 2012

insert record into a table

I need to insert records in a SQL table reading from a csv file

do you know another method to do that???

let me know....

thanks for the help

cirollo

thedesolatesoul · Jan 31, 2012

How big is the CSV file?

The easier way will be to break it into parts and import each part separately.

It does look like its more to do with a library rather than your code. Are you downloading by FTP?

Erel · Feb 1, 2012

It does look like its more to do with a library rather than your code.

In most cases libraries are not faster or more efficient than B4A code.

It is a bit hard to say, but the error seems to be with txtLog. How large is its text?

Why don't you use Log command instead.

cirollo · Feb 1, 2012

the file is...

Approx 4mb
i download it with ftp
but seems downloaded correctly
according to you what could be the limit of each part the file should be splitted?
after this there are other smaller files do i have the same problem?
Is possible to free some memory after each file import?

@erel what do you mean?

Erel · Feb 1, 2012

I think that the error is related to txtLog which gets longer all the time.

cirollo · Feb 1, 2012

no

Cause if i try with smaller file it works

Erel · Feb 1, 2012

If you like you can upload your project and I will check it.

cirollo · Feb 1, 2012

ok, but

the project is too big for upload on the forum, if you can give me an email I'll attach the zip file to it.

and also the files to be imported, even If there are on my ftp

Erel · Feb 1, 2012

You can send it to: [email protected]

cirollo · Feb 3, 2012

ok....

Erel but before sending to You I would like to make another test:

if I split the csv in more files, each one approx 1/10 of the original, can I solve the out of memory problerm or the memory will fill itself again processing the files....

in other words...does the memory free itself after one file processing before passing to the next one???

Erel · Feb 5, 2012

Memory is freed from objects that are no longer referenced.

cirollo · Feb 5, 2012

I need help!

because I'm going crazy about this problem:

I've splitted the big 37000 records csv file in more smaller files of 3000 records each.
The download phase has no problem, but when I try to insert the records inside the sql tables this is my log

Code: Copy

InsertMaps (first query out of 2788): INSERT INTO [Clienti] ([IdCli], [Ragsoc], [Indirizzo], [Cap], [Localita], [Prov], [Telefono], [Mobile], [Iniziale], [Giorno], [Status], [Listino], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
InsertMaps (first query out of 3): INSERT INTO [Parape] ([IdPar], [IdCli], [ImpSal], [ImpInc], [DataInc], [NumDoc], [DataDoc], [DataExp]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Parape] () VALUES ()
InsertMaps (first query out of 2999): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Articoli] () VALUES ()
InsertMaps (first query out of 3000): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Articoli] () VALUES ()
InsertMaps (first query out of 3000): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Articoli] () VALUES ()
InsertMaps (first query out of 3000): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Articoli] () VALUES ()
InsertMaps (first query out of 3000): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Articoli] () VALUES ()
InsertMaps (first query out of 3000): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Articoli] () VALUES ()
InsertMaps (first query out of 3000): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Articoli] () VALUES ()
InsertMaps (first query out of 3000): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Articoli] () VALUES ()
InsertMaps (first query out of 3000): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Articoli] () VALUES ()
InsertMaps (first query out of 3000): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Articoli] () VALUES ()
InsertMaps (first query out of 3000): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Articoli] () VALUES ()
InsertMaps (first query out of 3000): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Articoli] () VALUES ()
InsertMaps (first query out of 1632): INSERT INTO [Articoli] ([CodiceArt], [Desart], [Idart], [Um], [Famiglia], [GruMer], [CatScm], [AliIva]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Articoli] () VALUES ()
InsertMaps (first query out of 35691): INSERT INTO [Listini] ([Seriale], [IdArt], [IdListino], [ImpUni], [Sconto1], [Sconto2], [Sconto3], [Sconto4]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Listini] () VALUES ()
InsertMaps (first query out of 7741): INSERT INTO [Giacenze] ([Seriale], [IdArt], [IdMag], [Esistenza], [DataAgg]) VALUES (?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Giacenze] () VALUES ()
InsertMaps (first query out of 5): INSERT INTO [Scomag] ([Seriale], [CatScmCli], [CatScmArt], [Sconto1], [Sconto2], [Sconto3], [Sconto4]) VALUES (?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Scomag] () VALUES ()
InsertMaps (first query out of 9): INSERT INTO [Storicoprz] ([Seriale], [IdArt], [IdCli], [DataDoc], [ImpDoc], [Sconto1], [Sconto2], [Sconto3], [Sconto4]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Storicoprz] () VALUES ()
InsertMaps (first query out of 3): INSERT INTO [Famiglie] ([Famiglia]) VALUES (?)
InsertMaps (first query out of 9): INSERT INTO [Gruppi] ([GruMer]) VALUES (?)
InsertMaps (first query out of 4): INSERT INTO [CodiciIva] ([AliIva]) VALUES

I cannot understand why some insert are ok while others report:

(SQLiteException) android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: INSERT INTO [Parape] () VALUES ()

this is the code, I cannot see any difference between, for example, the insert of "clienti" and that of "parape" or "articoli"

Code: Copy

Sub PopolaTabelle
   Msgbox("popola tabelle","")
   Dim su As StringUtils
   Dim Table,Table2 As List
   Dim Items() As String
   TxtLog.Text = "Creazione Tabelle Database ed Import Dati..."&Chr(10)&TxtLog.Text
   LblStatus.Text = Progressbar1.Progress &"%"

   '****************************************************************
   ' popolo la tabella clienti
   TxtLog.Text = "Inserimento Dati Tabella Clienti..." &Chr(10)&TxtLog.Text
   Table = su.LoadCSV(sdRoot, "clienti.csv", ";")
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      m.Put("IdCli", Items(0)) 
      m.Put("Ragsoc", Items(1))
      m.Put("Indirizzo", Items(2))
      m.Put("Cap", Items(3))
      m.Put("Localita", Items(4))
      m.Put("Prov", Items(5))
      m.Put("Telefono", Items(6))
      m.Put("Mobile", Items(7))
      m.Put("Iniziale", Items(1).SubString2(0,1))
      m.Put("Giorno", Items(8))
      m.Put("Status", Items(9))
      m.Put("Listino", Items(10))
      m.Put("CatScm", Items(11))
      m.Put("AliIva", Items(12))
      Table2.Add(m)
   Next
   DBUtils.InsertMaps(SQL1, "Clienti", Table2)
   Progressbar1.Progress = 50
   LblStatus.Text = Progressbar1.Progress &"%"

   '****************************************************************
   ' popolo la tabella parape
   TxtLog.Text = "Inserimento Dati Tabella Partite Aperte..." &Chr(10)&TxtLog.Text
   Table = su.LoadCSV(sdRoot, "parape.csv", ";")
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      m.Put("IdPar", Items(0)) 
      m.Put("IdCli", Items(1))
      m.Put("ImpSal", Items(2))
      m.Put("ImpInc", Items(3))
      m.Put("DataInc", Items(4))
      m.Put("NumDoc", Items(5))
      m.Put("DataDoc", Items(6))
      m.Put("DataExp", "N")
      Table2.Add(m)
   Next
   m.Clear
   DBUtils.InsertMaps(SQL1, "Parape", Table2)
   Progressbar1.Progress = 55
   LblStatus.Text = Progressbar1.Progress &"%"

   '****************************************************************
   Dim i,j As Int
   For j = 0 To 12
      ' popolo la tabella articoli
      TxtLog.Text = "Inserimento Dati Tabella Articoli..." &Chr(10)&TxtLog.Text
      Table = su.LoadCSV(sdRoot, "articoli" &j &".csv", ";")
      Table2.Initialize
      For i = 0 To Table.Size - 1
         Items = Table.Get(i)
         Dim m As Map
         m.Initialize
         m.Put("CodiceArt", Items(0)) 
         m.Put("Desart", Items(1))
         m.Put("Idart", Items(2))
         m.Put("Um", Items(3))
         m.Put("Famiglia", Items(4))
         m.Put("GruMer", Items(5))
         m.Put("CatScm", Items(6))
         m.Put("AliIva", Items(7))
         Table2.Add(m)
      Next
      m.Clear
      DBUtils.InsertMaps(SQL1, "Articoli", Table2)
   Next
   Progressbar1.Progress = 60
   LblStatus.Text = Progressbar1.Progress &"%"
   
   
   '****************************************************************
   
   ' popolo la tabella Listini Articoli
   TxtLog.Text = "Inserimento Dati Tabella Listini Articoli..." &Chr(10)&TxtLog.Text
   Table = su.LoadCSV(sdRoot, "listini.csv", ";")
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      m.Put("Seriale", ""&(i+1))
      m.Put("IdArt", Items(0))
      m.Put("IdListino", Items(1))
      m.Put("ImpUni", Items(2))
      m.Put("Sconto1", Items(3))
      m.Put("Sconto2", Items(4))
      m.Put("Sconto3", Items(5))
      m.Put("Sconto4", Items(6))
      Table2.Add(m)
   Next
   m.Clear
   DBUtils.InsertMaps(SQL1, "Listini", Table2)
    Progressbar1.Progress = 65
   
   '****************************************************************
   ' popolo la tabella Giacenze Articoli
   TxtLog.Text = "Inserimento Dati Tabella Giacenze Articoli..." &Chr(10)&TxtLog.Text
   Table = su.LoadCSV(sdRoot, "giacenze.csv", ";")
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize 
      m.Put("Seriale", ""&(i+1))
      m.Put("IdArt", Items(0))
      m.Put("IdMag", Items(1))
      m.Put("Esistenza", Items(2))
      m.Put("DataAgg", "")
      Table2.Add(m)
   Next
   m.Clear
   DBUtils.InsertMaps(SQL1, "Giacenze", Table2)
    Progressbar1.Progress = 70
   
   '****************************************************************
   
   ' popolo la tabella Sconti e Maggiorazioni
   TxtLog.Text = "Inserimento Dati Tabella Sconti e Maggiorazioni..." &Chr(10)&TxtLog.Text
   Table = su.LoadCSV(sdRoot, "scomag.csv", ";")
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize 
      m.Put("Seriale", ""&(i+1))
      m.Put("CatScmCli", Items(0))
      m.Put("CatScmArt", Items(1))
      m.Put("Sconto1", Items(2))
      m.Put("Sconto2", Items(3))
      m.Put("Sconto3", Items(4))
      m.Put("Sconto4", Items(5))
      Table2.Add(m)
   Next
   m.Clear
   DBUtils.InsertMaps(SQL1, "Scomag", Table2)
    Progressbar1.Progress = 75
   
   '****************************************************************
   ' popolo la tabella Storico Prezzi
   TxtLog.Text = "Inserimento Dati Tabella Storico Prezzi..." &Chr(10)&TxtLog.Text
   Table = su.LoadCSV(sdRoot, "storicoprz.csv", ";")
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize 
      m.Put("Seriale", ""&(i+1))
      m.Put("IdArt", Items(0))
      m.Put("IdCli", Items(1))
      m.Put("DataDoc", Items(2))
      m.Put("ImpDoc", Items(3))
      m.Put("Sconto1", Items(4))
      m.Put("Sconto2", Items(5))
      m.Put("Sconto3", Items(6))
      m.Put("Sconto4", Items(7))
      Table2.Add(m)
   Next
   m.Clear
   DBUtils.InsertMaps(SQL1, "Storicoprz", Table2)
    Progressbar1.Progress = 80
   
   '****************************************************************
   ' popolo la tabella Famiglie
   TxtLog.Text = "Inserimento Dati Tabella Famiglie..." &Chr(10)&TxtLog.Text
   Table = su.LoadCSV(sdRoot, "tabelle.csv", ";")
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      If Items(0)="FA" Then 
       m.Put("Famiglia", Items(1)) 
       Table2.Add(m)
      End If 
   Next
   DBUtils.InsertMaps(SQL1, "Famiglie", Table2)
    Progressbar1.Progress = 85
   
   '****************************************************************
   ' popolo la tabella Gruppi Merceologici
   TxtLog.Text = "Inserimento Dati Tabella Gruppi Merceologici..." &Chr(10)&TxtLog.Text
   Table = su.LoadCSV(sdRoot, "tabelle.csv", ";")
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      If Items(0)="GM" Then 
       m.Put("GruMer", Items(1)) 
       Table2.Add(m)
      End If 
   Next
   m.Clear
   DBUtils.InsertMaps(SQL1, "Gruppi", Table2)
    Progressbar1.Progress = 90
   
   '****************************************************************
   ' popolo la tabella Aliquote Iva
   TxtLog.Text = "Inserimento Dati Tabella Aliquote Iva..." &Chr(10)&TxtLog.Text
   Table = su.LoadCSV(sdRoot, "tabelle.csv", ";")
   Table2.Initialize
   For i = 0 To Table.Size - 1
      Items = Table.Get(i)
      Dim m As Map
      m.Initialize
      If Items(0)="CI" Then 
       m.Put("AliIva", Items(1)) 
       Table2.Add(m)
      End If 
   Next
   m.Clear
   DBUtils.InsertMaps(SQL1, "CodiciIva", Table2)
    Progressbar1.Progress = 95
LblStatus.Text = ""
    Progressbar1.Progress = 100
   Msgbox2("Ricezione completata!","McOrdini","","Ok","",LoadBitmap (File.DirAssets, "warning_256.png"))
   Progressbar1.Visible = False
   TxtLog.Visible = False
   LblStatus.Visible = False
   LblProgress.Visible = False
End Sub

If I check the insert with errors, no record is inserted in the db

Could someone help me?

Erel · Feb 6, 2012

INSERT INTO [Parape] () VALUES ()

Aren't the columns missing?

cirollo · Feb 6, 2012

are you talking about the csv file?

The file has 3 records (3 rows) each field separated by ";"

all the csv files that I'm using don't have first row with field names.

Could it be because I'm using the same variables for all the imports:

Code: Copy

 Dim su As StringUtils
    Dim Table,Table2 As List
    Dim Items() As String
.......
  Table = su.LoadCSV(sdRoot, "clienti.csv", ";")
    Table2.Initialize
    For i = 0 To Table.Size - 1
        Items = Table.Get(i)
        Dim m As Map
        m.Initialize

how does it works? it ends the insert of a file before going to the next one?
if parape fail to insert records, why also the others fail? it seems that only smaller files will work!

Big Csv import problem

Active Member

Expert

Active Member

Expert

Active Member

Expert

Active Member

Expert

B4X founder

Active Member

B4X founder

Active Member

B4X founder

Active Member

B4X founder

Active Member

B4X founder

Active Member

B4X founder

Active Member

Similar Threads