B4J Question Problem with Jpoi and large files

[Giacomo]

Help me
if i open excel file with 2.000 rows i have no problem

Sub Leggi_excel(Dir As String ,File_excel As String)
    Start=DateTime.Now
    Leggi.InitializeExistingAsync("Lettura",Dir,File_excel,"")
    
End Sub

Sub Lettura_ready(Success As Boolean)
    LogDebug("Lettura Finita")
    Dim Tempo_di_lettura As Double=DateTime.Now-Start
    Tempo_di_lettura=Tempo_di_lettura/1000
    LogDebug ("Tempo di lettura " & Tempo_di_lettura & " Secondi")
    'Legge il file selezionato e scrive Variabile Elenco (20)
    Elenca
    'Scrive il TreeView
    Costruisci_tree.Riempi_Tree(Main.TreeWiew_01,Caratteristiche_excell)
end sub

but if I open larger files about 100.000 lines nothing happens
What can I do ?

 

[Erel]

Can you upload the workbook file?

 

[Giacomo]

I try but too big 70Mb 420.000 rows and 32 column

WS_ROWS.xlsx
The uploaded file is too large for the server to process.

the question is ?I can read it in blocks ?

My final target is convert it to Sqlite Db

 

[udg]

If anything else fails, you could save it as several smaller files and import them one after the other in your DB.
Tedious but feasible..

 

[DonManfred]

I try but too big 70Mb 420.000 rows and 32 column

Upload it to dropbox/drive and post the link

 

[Giacomo]

 

[Giacomo]

If anything else fails, you could save it as several smaller files and import them one after the other in your DB.
Tedious but feasible..

it can be done
there are 150 Excel files every month
dividing them (e.g. x10) they are 1500 excel every month

too tiring

 

[udg]

Agreed. I understood it as a one-timer import.

 

[Erel]

Test it with InitializeExisting instead of InitializeExistingAsync.

Increase the VM heap size:

#VirtualMachineArgs: -Xms4096m -Xmx4096m

 

[Giacomo]

Test it with InitializeExisting instead of InitializeExistingAsync.

Increase the VM heap size:

#VirtualMachineArgs: -Xms4096m -Xmx4096m

My System is 32 bit therefore

#VirtualMachineArgs: -Xms1024m -Xmx1024m

Sub Leggi_excel(Dir As String ,File_excel As String)
    Start=DateTime.Now
    'Leggi.InitializeExistingAsync("Lettura",Dir,File_excel,"")
    Leggi.InitializeExisting(Dir,File_excel,"")
   
End Sub

The result is

java.lang.OutOfMemoryError: Java heap space ........

 

[DonManfred]

1. Try 2048

#VirtualMachineArgs: -Xms2048m -Xmx2048m

2. I suggest to Switch to 64bit and more RAM in PC

 

[Giacomo]

1. Try 2048

not work in 32 Bit operating system

2. I suggest to Switch to 64bit and more RAM in PC

if I develop my code with 64bit system (and JDK 8 or 11)
is it possible to run my software compiled on 32 bit system?

 

[DonManfred]

Java apps can work on 32 bit, sure. But you are limited in the RAM available. In 64bit it is no Limit.

I do have 16GB in my PC and savely can use 4096m in VirtualMachineArgs

 

[Giacomo]

All clear thanks

in the end the only solution seems to be that of udg

If anything else fails, you could save it as several smaller files and import them one after the other in your DB.
Tedious but feasible..

 

[roerGarcia]

May be this

Sub AbrirArchivos()

'Paso 1: Declarar las variables

Dim Archivos As String


'Paso 2: Especificar una carpeta y el tipo de archivo que buscamos
'en este caso la carpeta se llama "temporal" y el tipo de dato es "xlsx"


Archivos = Dir("C:\temporal\*.xlsx")
Do While Archivos <> “”


'Paso 3: Abrir los libros uno por uno

Workbooks.Open "C:\temporal\" & Archivos

' Paso 4: se coloca el código que se quiere correr
'en el caso de este ejemplo se corre el código que aparece a continuación
'el cual sirve para borrar el contenido de la celda A1

[B]Range("a1").ClearContents ' not  this of course[/B]

'Paso 5: Cuadro de mensaje, cerrar y guardar cambios

MsgBox ActiveWorkbook.Name

ActiveWorkbook.Close SaveChanges:=True

'Paso 6: buscar más archivos en la carpeta para volver seguir la secuencia

Archivos = Dir
Loop

End Sub

 

[RWK]

You could try it the other way around.....

export in Excel to csv.... then use this with b4x...

something like that:

Sub BuildDatevFile(ExFile As String)

Open ExFile For Output As #1

    'Kopfdaten
    Print #1, sDatevKopf
    'Buchungsdaten
    For x = 1 To tabDatev.UsedRange.EntireRow.Count
        For y = 1 To 116
            P tabDatev.Cells(x, y)
        Next y
        Print #1, ""
    Next x
Close #1
End Sub

Sub P(Datenfeld As String)
    If Datenfeld = "" Then
        Print #1, Chr(34) & Chr(34) & Chr(59);
    Else
        Print #1, Datenfeld & Chr(59);
    End If
End Sub

Grüße
Rainer