Big Csv import problem

cirollo

cirollo

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

Code: Copy 
   ' 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?
 
Sort by date Sort by votes
Same Problem...

Today I have the same problem...

I have one file with more than 50.000 regs (and 8mb size)

when I use su.LOADCSV command in this file the application crash....

When I cut file and remove a lot of regs (resting only 10.000 regs) the Su.LOADCSV works perfectly...

Have another hay to use su.loadcsv in big txt files or only solution is broken the big TXT in smallers TXTs anda import one by one?
 
Upvote 0
It may exist...although I couldn't find it, but it sounds like you guys need a Parse function/sub. This would work like the LoadCSV, but you pass it a string. You could then load each line of the file one at a time and handle each.

Might even be useful if there was a function that returned a Map-

LoadCSVRow(Dir as String, Filename as String, Separator as Char, Header() as String, Row as Int) as Map

If Header is Null it Reads the First line of the file, otherwise you can pass column names. Then you have a Map of Column=Value for that Row returned. Should be much easier to use in SQL too. This wouldn't be hard to make in class or module too. Only difficult thing is handling when the data contains the Separator. Naming the functions with CSV (Comma Separated Value) and allowing a Separator Char to be specified is confusing too. I often use Tab, Bar, or Semicolon delimited over comma.
 
Upvote 0
You must log in or register to reply here.

Similar Threads

virpalacios
  • Question Question
Android Question DbUtils for Android SQLite (Create Table with varchar field)
Replies
2
Views
1K
virpalacios
virpalacios
Guenter Becker
  • Article Article
B4A Library extDBUtils
Replies
6
Views
5K
Guenter Becker
Guenter Becker
Alexander Stolte
  • Question Question
iOS Question [B4X] DBUtils2 - CreateTable not standardized
Replies
5
Views
4K
TILogistic
TILogistic
Erel
  • Article Article
B4J Library [B4X] DBUtils 2
2 3 4
Replies
77
Views
83K
ganezha
G
Erel
  • Locked
  • Article Article
Android Tutorial Remote Database Connector (RDC) - Connect to any remote DB
20 21 22
Replies
423
Views
409K
Erel
Erel
Back
Top