Big Csv import problem

[cirollo]

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)

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?

 

[Erel]

how does it works? it ends the insert of a file before going to the next one?

LoadCSV creates a new List and returns it. It doesn't matter that you use the same variable.

As I wrote before, it is very hard to help you this way. If you like you can upload your project and we can better help you.

 

[cirollo]

ok

this evening I'll send the project by mail to You Erel!

thanks a lot!

regards,
ciro

 

[peacemaker]

Any end of this quest ?

 

[b2mvga]

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?

 

[Roger Garstang]

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.

 

[Erel]

You will need to break the file into multiple files. If your file is made of "simple" records then you can manually read it and parse it line by line.

With "simple" I mean that no field contains the separator character, a quote or a new line characters.

 

[stefanobusetto]

solved ?
it looks like an intrsting problem