B4J Question Problem with Jpoi and large files

Giacomo · Feb 12, 2020

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

B4X:

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

B4X:

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 · Feb 12, 2020

Can you upload the workbook file?

Giacomo · Feb 12, 2020

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 · Feb 12, 2020

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 · Feb 12, 2020

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

Upload it to dropbox/drive and post the link

Giacomo · Feb 12, 2020

Giacomo · Feb 12, 2020

udg said:
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 · Feb 12, 2020

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

Erel · Feb 13, 2020

Test it with InitializeExisting instead of InitializeExistingAsync.

Increase the VM heap size:

B4X:

#VirtualMachineArgs: -Xms4096m -Xmx4096m

Giacomo · Feb 13, 2020

Erel said:
Test it with InitializeExisting instead of InitializeExistingAsync.

Increase the VM heap size:

B4X:

#VirtualMachineArgs: -Xms4096m -Xmx4096m

My System is 32 bit therefore

#VirtualMachineArgs: -Xms1024m -Xmx1024m

B4X:

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 · Feb 13, 2020

1. Try 2048

#VirtualMachineArgs: -Xms2048m -Xmx2048m

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

Giacomo · Feb 13, 2020

DonManfred said:
1. Try 2048

not work in 32 Bit operating system

Click to expand...

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 · Feb 13, 2020

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 · Feb 13, 2020

All clear thanks

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

udg said:
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 · Feb 14, 2020

May be this

Excel macro to navigate all the files:

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 · Feb 16, 2020

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

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

something like that:

Code:

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

B4J Question Problem with Jpoi and large files

Giacomo

Active Member

Erel

B4X founder

Giacomo

Active Member

udg

Expert

DonManfred

Expert

Giacomo

Active Member

Giacomo

Active Member

udg

Expert

Erel

B4X founder

Giacomo

Active Member

DonManfred

Expert

Giacomo

Active Member

DonManfred

Expert

Giacomo

Active Member

roerGarcia

Active Member

RWK

Member

Similar Threads

B4J Question Problem with Jpoi and large files

Active Member

B4X founder

Active Member

Expert

Expert

Active Member

Active Member

Expert

B4X founder

Active Member

Expert

Active Member

Expert

Active Member

Active Member

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency